1. To optimize the query, you should try to avoid full table scan, first consider the where and the order by the columns involved in the establishment of the index.
2. The null value of the field in the WHERE clause should be avoided as far as possible, or it will cause the engine to discard the use of the index for a full table scan, such as:
Select ID from t where num is null
You can set the default value of 0 on NUM to ensure that the NUM column in the table does not have a null value and then query this way:
Select ID from t where num=0
3. The use of!= or <> operators in the WHERE clause should be avoided as far as possible, otherwise the engine discards the use of the index for a full table scan.
4. You should try to avoid using or to join conditions in the WHERE clause, or it will cause the engine to discard the use of the index for a full table scan, such as:
Select ID from t where num=10 or num=20
You can query this way:
Select ID from t where num=10
UNION ALL
Select ID from t where num=20
5.in and not in should also be used with caution, otherwise it will result in a full table scan, such as:
Select ID from t where num in (1,2,3)
For consecutive values, you can use between instead of in:
Select ID from t where num between 1 and 3
6. The following query will also result in a full table scan:
Select ID from t where name like '%abc% '
To improve efficiency, you can consider Full-text search.
7. The use of parameters in the WHERE clause also results in a full table scan. Because SQL resolves local variables only at run time, it optimizes
The program cannot postpone the choice of the access plan to the runtime; it must be selected at compile time. However, if an access plan is established at compile time,
The value of the variable is still unknown and cannot be selected as an entry for the index. The following statement will perform a full table scan:
Select ID from t where num= @num
You can use the index instead of forcing the query:
Select ID from T with (index name) where num= @num
8. You should try to avoid the expression of fields in the WHERE clause, which will cause the engine to discard the use of indexes for full table scans. Such as:
Select ID from t where num/2=100
should read:
Select ID from t where num=100*2
9. Avoid functional operations of fields in the WHERE clause, which causes the engine to discard the use of indexes for full table scans. Such as:
The select ID from t where substring (name,1,3) = ' abc '//Oracle is always a substr function.
Select ID from t where DATEDIFF (day,createdate, ' 2005-11-30 ') =0//Check it out. There is indeed no DateDiff function.
should read:
Select ID from t where name like ' abc% '
Select ID from t where createdate>= ' 2005-11-30 ' and createdate< ' 2005-12-1 '//
Oracle time should convert char to date such as: CreateDate >= to_date (' 2005-11-30 ', ' yyyy-mm-dd ')
10. Do not perform functions, arithmetic operations, or other expression operations on the left side of "=" in the WHERE clause.
Otherwise the system may not be able to use the index correctly. (Fields with functions cannot take advantage of indexes)
11. When using an indexed field as a condition, if the index is a composite index, you must use the first field in the index to
The index is guaranteed to be used by the system, otherwise the index will not be used, and the order of the fields should be consistent with the index order as much as possible.
12. Do not write some meaningless queries, such as the need to generate an empty table structure:
Select Col1,col2 into #t the from T where 1=0
This type of code does not return any result sets, but consumes system resources and should be changed to this way:
CREATE TABLE #t (...)
13. It is a good choice to use exists instead of in.
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, and SQL is optimized for queries based on the data in the table, and when the index column has a large amount of data to repeat,
SQL queries may not take advantage of an index, such as a table with fields Sex,male, female almost half, and even if an index is built on sex
Also can not play a role in query efficiency.
15. Index is not the more the better, while the index can improve the efficiency of the corresponding select, but also reduce the efficiency of insert and update,
Because the index is likely to be rebuilt when inserts or updates are made, how to build the index requires careful consideration, depending on the circumstances. The number of indexes for a table
It is best not to exceed 6, if too many you should consider whether the indexes built on columns that are not used frequently are necessary.
16. Avoid updating clustered indexed data columns as much as possible, because the order of the clustered indexed data columns is the physical storage order of the table records.
Once the value of the column changes will result in an adjustment of the order of the entire table record, a considerable amount of resources is consumed. If the application system needs frequent update of clustered cable
Cited data columns, you need to consider whether the index should be built as a clustered index
17. Use numeric fields as much as possible, if the fields containing only numerical information are not designed to be character type, this will reduce the performance of query and connection, and will increase the storage
Storage overhead. This is because the engine compares each character of the string one at a time while processing queries and connections, and it is enough for a numeric type to be compared once.
18. Use Varchar/nvarchar as much as possible instead of Char/nchar, because first variable long field storage space is small, can save storage space,
Second, for queries, search efficiency is clearly higher in a relatively small field.
19. Do not use SELECT * from t anywhere, use a specific field list instead of "*", and do not return any fields that are not used.
20. Use table variables as far as possible instead of temporary tables. If the table variable contains a large amount of data, note that the index is very limited (only primary key indexes).
21. Avoid frequent creation and deletion of temporary tables to reduce the consumption of system table resources.
22. Temporary tables are not unusable and they can be used appropriately to make some routines more efficient, for example, when you need to repeatedly refer to large tables or frequently used tables
is a data set in the However, for one-off events, it is best to use an export table.
23. When creating a new temporary table, if the amount of data inserted at a time is large, you can use SELECT INTO instead of the CREATE table.
Avoid causing a lot of log to improve speed; If the amount of data is small, in order to ease the resources of the system tables, create a table and insert it first.
24. If you use a temporary table, be sure to explicitly delete all temporary tables at the end of the stored procedure, TRUNCATE TABLE first,
The drop table is then used to avoid a longer time lock on the system table.
25. Avoid the use of cursors, because the efficiency of the cursor is poor, if the cursor operation of more than 10,000 rows of data, then you should consider rewriting.
26. Before using a method based on a cursor or a temporary table method, you should first look for a set based solution to solve the problem, and a set based approach is usually more efficient.
27. As with temporary tables, cursors are not unusable. Using Fast_forward cursors for small datasets is usually better than other row-by-line processing.
Especially if you have to refer to several tables to get the data you need. Routines that include "totals" in the result set are typically faster than those used with cursors.
If development time allows, both a cursor based approach and a set based approach can be tried to see which method works better.
28. Set the set NOCOUNT on at the beginning of all stored procedures and triggers, and set NOCOUNT off at the end.
You do not need to send a DONE_IN_PROC message to the client after executing each statement of the stored procedure and trigger.
29. Try to avoid large business operations, improve system concurrency capability.
30. Try to avoid the return of large data to the client, if the amount of data is too large, we should consider whether the corresponding demand is reasonable.
31.union operator
The Union filters out duplicate records after a table link, so the resulting set of results is sorted after the table is connected.
Delete duplicate records and return the results. Most applications do not produce duplicate records, the most common is the process table and history
Table Union.
This SQL takes out the results of two tables first, then sorts the duplicate records with the sort space, and finally returns the result set.
If the amount of table data is large, it may result in sorting by disk.
Recommendation: Use the union ALL operator instead of union because the union all operation simply merges two results and returns.
32. As far as possible to avoid hermit type conversion, easy to cause the failure of the Oracle index many reasons:
1) and use functions on the indexed columns. such as SUBSTR,DECODE,INSTR, and so on, the index column is operational. You need to set up a function index to solve it.
2 The new table has not been able to generate statistical information, analysis of the good
3, cost based costing analysis, access to the table is too small, using full table scan consumption is less than the use of indexes.
4), using <>, not in, not exist, for these three scenarios, most cases think the result set is very large, generally greater than 5%-15% do not go index and walk fts (full table scan).
5), Separate >, <.
6, like "%_" hundred semicolon in front.
7), a separate reference to the index column not in the first position in the composite index. That is, the query verb does not use the first column of the combined index, here is a index SKIP scan concept
8), when the character field is numeric, no quotes are added in the Where condition.
9, when the variable is using the Times variable, and the table's field is the date variable. or the opposite.
10, index invalidation, you can consider rebuilding the index, rebuild online.
11), B-tree index is null does not go, is not NULL will go, bitmap index is null,is not NULL will go,
The federated index is not null as long as it is in the indexed column that is established (in all order)
12), indexed on a table column that contains a null value, the index is not used when the select COUNT (*) from table is used.
13, plus hint does not go to the index, it may be because you want to go index of this column is nullable, although this column has no null value.
(Change the field to not null and go)
1: When multiple table associations are used, the multiple where statement minimizes the result set of a single table, uses aggregate functions to summarize the result set, and then associates with other tables to minimize the amount of result set data
2: You should consider the possibility of using a right connection when you are associating two tables. To improve query speed
3: Use where instead of having, where is used to filter rows, and having is used to filter groups, because the rows are grouped, having to filter the group, so try to filter the user where
4: Use exists instead of in because exists only checks the existence of rows, while in examines the actual values.
5:in operator
The advantage of using in-write SQL is that it is easier to write and understand, which is more suited to the style of modern software development.
However, in SQL performance is always low, because:
For SQL statements in, ORACLE always attempts to convert the connection to multiple tables, and if the conversion is unsuccessful, the first execution in
Inside of the subquery, and then query the outer table record
A connection that is converted to multiple tables if the conversion succeeds. So don't manage how, in SQL statements always have one more conversion
Process. Normal SQL can be converted successfully.
But for SQL that contains packet statistics, it cannot be converted. Therefore, in the business-intensive SQL, try not to use the in operator.
6:not in operator
This operation is strongly recommended for use because it cannot apply the index of the table.
In this case, it should be replaced with EXISTS, not EXISTS or (outer join + NULL) scheme.
7:<> operator
Not equal to the operator is never used in the index, so processing it will only produce a full table scan.
In this case, other ways can be substituted, such as:
A<>0-> a>0 OR a<0
A<> '-> a> '
8:like operator
If you encounter SQL statements that require like filtering, you can use InStr instead. Processing speed will be significantly improved.
9:union operator
The Union filters out duplicate records after a table link, so the resulting set of results is sorted after the table is connected.
Delete duplicate records and return the results. Most applications do not produce duplicate records, the most common is the process table and history
Table Union. Such as:
The code is as follows:
SELECT * FROM Gc_dfys
Union
SELECT * FROM Ls_jg_dfys
This SQL takes out the results of two tables first, then sorts the duplicate records with the sort space, and finally returns the result set.
If the amount of table data is large, it may result in sorting by disk.
Recommendation: Use the union ALL operator instead of union because the union all operation simply merges two results and returns.
The code is as follows:
SELECT * FROM Gc_dfys
UNION ALL
SELECT * FROM Ls_jg_dfys
The impact of SQL writing
The same function, same performance, different syntax, SQL effect
such as a SQL in a programmer written for
SELECT * FROM Zl_yhjbqk
b The programmer writes for
SELECT * from dlyx.zl_yhjbqk (prefix with table owner)
C the programmer writes for
SELECT * from Dlyx. ZLYHJBQK (uppercase table name)
D Programmer writes for
SELECT * from Dlyx. ZLYHJBQK (more space in the middle)
The above four SQL produces the same results and execution time after Oracle parsing, but the shared memory of the SGA from the Oracle
Principle
It can be concluded that Oracle will parse it once for each SQL and consume shared memory, if the SQL string and format are written
Exactly the same, Oracle will only analyze it once,
Shared memory also leaves only one analysis, which reduces the time it will take to parse SQL, and reduces the amount of shared memory duplicated
Information, Oracle can also accurately count the execution frequency of SQL.
11:where the conditional order after the
The conditional order after the WHERE clause has a direct effect on queries to large data scales, such as
The code is as follows:
SELECT * from zl_yhjbqk where dy_dj = ' 1KV below ' and xh_bz=1
SELECT * from Zl_yhjbqk where xh_bz=1 and dy_dj = ' below ' 1KV
The above two SQL DY_DJ (voltage level) and XH_BZ (PIN user flag) Two fields are not indexed, so the execution is full table scan,
The first SQL DY_DJ = ' 1KV below ' condition has a ratio of 99% in the Recordset, while the xh_bz=1 ratio is only 0.5%,
In the first SQL, 99% records were compared Dy_dj and xh_bz, while in the second SQL, 0.5% records
are compared with DY_DJ and XH_BZ,
As a result, the second SQL CPU occupancy rate is significantly lower than the first one.
12: Impact of order of inquiry
The order of the lists in the table following from will have an impact on SQL performance, where there are no indexes and the Oracle does not have statistical analysis of the table
In the case Oracle will link in the order in which the table appears,
This is because the order of the tables does not result in data crossings that are very consuming server resources. (Note: If you make a statistical analysis of the table,
Oracle automatically links to advanced small tables, then links to large tables
13: Fields that are processed by functions cannot take advantage of indexes, such as:
The code is as follows:
substr (hbs_bh,1,4) = ' 5400 ', optimizing processing: Hbs_bh like ' 5,400% '
Trunc (SK_RQ) =trunc (sysdate), optimized processing:
Sk_rq>=trunc (Sysdate) and Sk_rq<trunc (sysdate+1)
Fields that have an explicit or implicit operation cannot be indexed, such as:
The code is as follows:
SS_DF+20>50, optimizing Processing: ss_df>30
' X ' | | Hbs_bh> ' X5400021452 ', optimized for processing:hbs_bh> ' 5400021542 '
Sk_rq+5=sysdate, optimizing Processing: sk_rq=sysdate-5
hbs_bh=5401002554, Optimizing Processing: hbs_bh= ' 5401002554 ', note: This condition is implicit to the HBS_BH
To_number conversion because the Hbs_bh field is a character type.
A condition that includes multiple field operations for this table cannot be indexed, such as:
YS_DF>CX_DF, unable to optimize
qc_bh| | Kh_bh= ' 5400250000 ', Optimizing Processing: qc_bh= ' 5400 ' and kh_bh= ' 250000 '
14: Apply Oracle's HINT (hint) processing
Prompt processing is used when the SQL profiling execution path generated by Oracle is not satisfactory. It can be done to the SQL below
The hint of the face
Goal-related tips:
Cost (optimized by costs)
Rule (optimized by rules)
CHOOSE (default) (Oracle automatically selects costs or rules for optimization)
SELECT emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';
All_rows (all lines are returned as soon as possible)
SELECT emp_no,emp_nam,dat_in from Bsempms WHERE emp_no= ' SCOTT ';
First_rows (first line of data return as soon as possible)
SELECT *
from xxx
where xxx;
SELECT *
from xxx
where xxx
Optimizer Tip: The goal is to improve the response time of SQL statements, and quickly return n rows first.
Tips for accessing paths
Full: Perform whole-table scans
ROID: Scan According to ROWID
Index: Scanning based on an index
SELECT * from emp where deptno=200 and sal>300;
If more than one is written, Oracle automatically chooses which of the best
SELECT * from emp where deptno=200 and sal>300;
Index_join: If the selected field is an indexed field (several indexes), you can access the data through an indexed connection without having to access
Table's data.
Select Deptno,sal from emp
where deptno=20;
Index_ffs: Perform fast full index scans
Select COUNT (*) from EMP;
No_index: Specify which indexes are not used
SELECT * FROM EMP where deptno=200
and sal>300;
And_equal: Specifies the result (intersection) of merging two or more index searches, up to 5
Tips for executing methods:
USE_NL (combined with nested loops method)
Use_merge (union with MERGE join)
Use_hash (Federated with HASH join)
Depending on the order in which the table appears in from, ordered enables Oracle to connect to it in this order.
For example:
SELECT a.col1,b.col2,c.col3 from TABLE1 a,table2 b,table3 C
WHERE A.col1=b.col1 and B.col1=c.col1;
Connects the specified table to the row source of the nested connection and takes the specified table as an internal table.
For example:
SELECT Bsdptms. Dpt_no,bsempms. Emp_no,bsempms. Emp_nam
From Bsempms,bsdptms WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;
Joins the specified table with other row sources by merging the sort connections.
For example:
SELECT * from Bsempms,bsdptms
WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;
Joins the specified table with other row sources through a hash connection.
For example:
SELECT * from Bsempms,bsdptms
WHERE Bsempms. Dpt_no=bsdptms. Dpt_no;
Other advanced hints (such as parallel processing, etc.)
Oracle's hints are more powerful and more complex applications, and hints are just a recommendation for Oracle to implement.
Sometimes Oracle may not follow the prompts for cost considerations. Based on practical applications, developers are not generally advised to apply Oracle hints,
Because the performance of each database and server is not the same, it is likely that a local performance upgrade, but the other place is down,
Oracle has matured in SQL execution analysis, if the path to execution is not the first in the database structure (primarily the index),
Analysis of the server's current performance (shared memory, disk file fragmentation), database object (table, index) statistics are correct.
