select * from ( SELECT ROW_NUMBER() OVER(ORDER BY A.ColumnName DESC) NUM, * from T_A A left join T_B B on A.X=B.Y and ...) t where B.ColumnName is not null
Optimization of the key 3:
1、Change natural link to left join and filter out the null value of the right table in the outer layer.
2、Oracle’s ROW_NUMBER () is used to place the order by condition in the join select item, ROW_NUMBER() OVER (ORDER BY A. ColumnName DESC) NUM.Solve the slow sequencing problem;
3、Index create index IND_C_DESC on T_A (ColumnName DESC) is created according to ascending and descending order.
Common query design and optimization methods:
1.To optimize queries, avoid full table scanning as much as possible, and consider indexing the columns involved in where and order by first.
2.You should try to avoid null value judgments on fields in the where clause, otherwise you will cause the engine to abandon using the index for full table scanning, such as: select id from t where num is null can set default values on num0, make sure that the num column in the table does not have a null value, and then query like this: select id from t where num=0
3.Try to avoid using the! = or & lt; & gt; operator in the where clause, otherwise the engine will abandon using the index for a full table scan.
4.You should try to avoid using or to join conditions in the where clause, otherwise it will cause the engine to abandon using the index for a full table scan, such as: select id from t where num = 10 or num = 20 to query: seleCT ID from t where num=10 union all select id from ID
5.in And not in should also be used with caution, otherwise I T will cause full table scan, such as: select id from t where num in (1, 2, 3) For continuous values, use between instead of in: select ID from t where num between 1 and 3
6.The following query will also cause a full table scan: select id from t where name like’Lee%’to be more efficient, consider full-text retrieval.
7. If you use parameters in the where clause, you will also cause full table scanning. Because SQL parses local variables only at run time, the optimizer cannot defer the choice of access plan to run time; it must make the choice at compile time. However, if the access plan is set up at compile time, the variables areThe value is unknown and therefore cannot be used as an index selection entry. The following statement will perform a full table scan: select id from t where num =@num can be changed to force the query to use the index: select id from t witH (index (index name)) where num=@num
8.You should try to avoid expression operations on fields in the where clause, which will cause the engine to abandon the use of indexes for full table scanning. For example, select id from t where num/2=100 should be changed to: select id fromT where num=100*2
9.Functional manipulation of fields in the where clause should be avoided as much as possible, which will cause the engine to abandon the use of indexes for full table scanning. Such as: select id from t where substring (name, 1,3) =’abc’, nameID starting with ABC
It should be changed to:
select id from t where name like ‘abc%’
10.Do not perform function, arithmetic, or other expression operations on the left of the “=” in the where clause, or the system may not be able to use the index correctly.
11.If an index field is used as a condition, the first field in the index must be used as a condition to ensure that the system will use the index, otherwise the index will not be used, and the field order should be consistent with the index order as far as possible.
12.Don’t write meaningless queries, such as the need to generate an empty table structure: select col1, col2 into t from where 1 = 0
Such code does not return any result set, but it consumes system resources.
create table #t(…)
13.Many times it’s a good choice to replace in with exists: select num from a where num in (select num from b)
Replace with the following statement:
select num from a where exists(select 1 from b where num=a.num)
14.Not all indexes are valid for queries. SQL is optimized for queries based on data in tables. SQL queries may not take advantage of indexes when there is a large number of data duplicates in index columns. For example, a table with fields sex, male, and female are almost half each, even if it is built on sexIndex also has no effect on query efficiency.
15. Indexes are not as many as possible. Indexes can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and update, because insert or update may rebuild the index, so how to build the index is necessary.Consider carefully, depending on the circumstances. The number of indexes in a table should not exceed 6, if too many, consider whether it is necessary to build indexes on columns that are not often used.
16. Updating clustered index data columns should be avoided as much as possible, because the order of clustered index data columns is the physical storage order of table records. Once the column value changes, the order of the entire table records will be adjusted, which will consume considerable resources. If the application system needs frequencyTo update a clustered index column, you need to consider whether the index should be built as a clustered index.
17.Use numeric fields as much as possible. If only numeric fields are not designed to be character-based, this will reduce query and connection performance and increase storage overhead. This is because the engine compares each character in a string one by one when processing queries and connections, and only one comparison is needed for numeric typesThat’s right.
18.Use varchar / nvarchar instead of char / nchar as much as possible, because first of all, variable-length field storage space is small, you can save storage space, and secondly, for queries, search efficiency in a relatively small field is obviously higher.
19.Do not use select * from t anywhere, replace “*” with a specific list of fields, and do not return any fields that are not used.
20.Try to use table variables instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only the primary key index).
21.Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
22.Temporary tables are not unusable, and proper use of them can make some routines more efficient, for example, when you need to refer repeatedly to a large table or a data set in a common table. However, for one-time events, it is better to use the export table.
23.When creating a temporary table, you can use select into instead of creating a table if you insert a large amount of data at one time, to avoid causing a large number of logs to increase speed; if the amount of data is small, to ease the resources of the system table, create it firstE table, then insert.
24.If temporary tables are used, it is important to explicitly delete all temporary tables at the end of the stored procedure, truncate tables first, and then drop tables, so that long locking of the system tables can be avoided.
25.Avoid using cursors as much as possible, because cursors are inefficient, and if the cursor operates on more than 10,000 rows of data, you should consider rewriting.
26.Set-based solutions should be sought before using cursor-based or temporary table methods, which are usually more effective.
27. Like temporary tables, cursors are not not available. Using the FAST_FORWARD cursor for small datasets is usually superior to other row-by-row processing methods, especially when several tables must be referenced to get the required data. Routines in the result set including “aggregate” are usually more than cursors.The speed of execution is fast. If development time permits, both cursor-based and set-based approaches can be tried to see which one works better.
28.Set SET NOCOUNT ON at the beginning of all stored procedures and triggers, and SET NOCOUNT OFF at the end. There is no need to send DONE_IN_PROC messages to the client after executing each statement of the stored procedure and trigger.
29.Try to avoid big business operation and improve system concurrency capability.
30.Try to avoid returning large amounts of data to the client, if the amount of data is too large, should consider whether the corresponding requirements are reasonable.