Technical Analysis of oracle SQL statement Optimization

I. Use of SQL
1. IN Operator
SQL statements written IN are easy to write and understand, which is suitable for modern software development.
However, SQL statements using IN always have low performance. The following differences exist between SQL statements using IN and SQL statements without IN:
ORACLE tries to convert it to the join of multiple tables. If the conversion fails, it first executes the subquery IN and then queries the outer table records, if the conversion is successful, multiple tables are directly connected for query.
It can be seen that at least one conversion process is added to SQL statements using IN. General SQL statements can be converted successfully, but SQL statements that contain grouping statistics cannot be converted.
 
Recommended Solution: avoid using the IN operator IN business-intensive SQL statements
2. not in Operator
This operation is not recommended for strong columns because it cannot apply table indexes.
 
Recommended Solution: use not exists or (the outer join + is null) instead.
3. <> operator (not equal)
The non-equals operator will never use the index, so the processing of it will only generate a full table scan.
 
Recommended Solution: use operations with the same functions, such
A <> 0 to a> 0 or a <0
A <> ''To a>''
4. is null or is not null operation (judge whether the field IS empty)
Generally, indexes are not used to determine whether a field is null, because the B-tree index does not have a null index.
 
Recommended solution:
Use other operations with the same function, such
Change a is not null to a> 0 or a>.
Fields are not allowed to be empty, but a default value is used to replace null values. For example, status fields in the application for expansion cannot be empty. The default value is application.
Create a bitmap index (partition-based tables cannot be created, and bitmap indexes are difficult to control. If too many fields are indexed, performance will be degraded, and data block locks will be added when multiple users perform update operations)
5.> and <operator (greater than or less than operator)
If the value is greater than or less than the operator, you do not need to adjust it. Because it has an index, index search is used. However, in some cases, you can optimize it,
For example, if A table has 1 million records, A numeric field A, and 0.3 million records A = 3.
Therefore, the effect of executing A> 2 and A> = 3 is very different, because ORACLE will first find the record index of 2 and then compare it, when A> = 3, ORACLE directly finds the record Index = 3.
6. LIKE Operator
The LIKE operator can be used for wildcard queries. The wildcard combinations in the LIKE operator can be used for almost any queries. However, poor use may result in performance problems,
For example, LIKE '% 100' does not reference the index, while LIKE 'x5400%' references the range index.
An example:
You can use the user ID after the Business ID in the YW_YHJBQK table to query the Business ID YY_BH LIKE '% 100'. This condition generates a full table scan,
If you change to YY_BH LIKE 'x5400% 'OR YY_BH LIKE 'b5400%', the index of YY_BH will be used to query the two ranges, and the performance will be greatly improved.
7. UNION operator
UNION filters out duplicate records after table link. Therefore, after table link, it sorts the generated result sets and deletes duplicate records before returning results.
In most applications, duplicate records are not generated. The most common is the UNION of Process Tables and historical tables. For example:
Select * from gc_dfys
Union
Select * from ls_jg_dfys
This SQL statement extracts the results of two tables at run time, sorts and deletes duplicate records using the sorting space, and finally returns the result set. If the table has a large amount of data, it may cause disk sorting.
Recommended Solution: Use the union all operator to replace UNION, because the union all operation simply merges the two results and returns them.
Select * from gc_dfys
Union all
Select * from ls_jg_dfys
8. Do not use upper () or lower for a large amount of data
 
Ii. Influence of SQL writing
 
1. Impact of different SQL statements on the same function and performance (using ORACLE's shared SQL Program)
If an SQL statement is written by A programmer: Select * from zl_yhjbqk
Programmer B writes: Select * from dlyx. zl_yhjbqk (with the table owner prefix)
C programmers write: Select * from DLYX. ZLYHJBQK (Capital table name)
D programmer writes: Select * from DLYX. ZLYHJBQK (spaces are added in the middle)
The results and execution time of the preceding four sqls are the same after ORACLE analysis, but the principle of shared memory SGA from ORACLE is as follows,
It can be concluded that ORACLE will analyze each SQL statement and occupy the shared memory,
If the SQL string and format are completely the same, ORACLE only analyzes the data once, and the shared memory only leaves the analysis results once,
This not only reduces the time required to analyze SQL statements, but also reduces duplicate information in the shared memory. ORACLE can also accurately calculate the execution frequency of SQL statements.
 
2. Effect of conditional order after WHERE
A. the conditional order after the WHERE clause directly affects the query of the big data table, as shown in
Select * from zl_yhjbqk where dy_dj = '1k' and xh_bz = 1
Select * from zl_yhjbqk where xh_bz = 1 and dy_dj = '1kv below'
In the preceding two SQL statements, the dy_dj and xh_bz fields are not indexed. Therefore, full table scan is performed,
In the first SQL statement, the dy_dj = '1kv below 'condition is 99% in the record set, while the xh_bz = 1 condition is only 0.5%,
When the first SQL statement is executed, 99% records are compared with dy_dj and xh_bz. When the second SQL statement is executed, 0.5% records are compared with dy_dj and xh_bz,
The CPU usage of the second SQL statement is obviously lower than that of the first SQL statement.
B. query the influence of table order
The list order in the table after FROM will affect SQL Execution performance. ORACLE will link the table in the order it appears if there is no index or ORACLE does not perform statistical analysis on the table,
As a result, data that consumes a lot of server resources is generated when the table order is incorrect. (Note: If statistical analysis is performed on the table, ORACLE will automatically link the small table to the large table)
 
Iii. SQL statement index Utilization
 
1. Optimization of operators (see the previous section)
2. Optimize the condition fields:
A. indexes cannot be used for fields processed by functions, for example:
Substr (hbs_bh, 5400) = '20160301', optimization: hbs_bh like '20160301'
Trunc (sk_rq) = trunc (sysdate), optimization: sk_rq> = trunc (sysdate) and sk_rq <trunc (sysdate + 1)
B. fields that have undergone explicit or implicit operations cannot be indexed, for example:
Ss_df + 20> 50, optimization: ss_df> 30
'X' | hbs_bh> 'x5400021452 '. Optimization: hbs_bh> '123'
Sk_rq + 5 = sysdate, optimized: sk_rq = sysdate-5
Hbs_bh = 5401002554, optimization processing: hbs_bh = '000000'. Note: This condition implicitly converts hbs_bh to to_number, because the hbs_bh field is in bytes type.
C. indexes cannot be performed when multiple fields in the table are included in the condition, for example:
Ys_df> cx_df, cannot be optimized
Qc_bh | kh_bh = '000000', optimization: qc_bh = '000000' and kh_bh = '000000'
4. Apply the HINT (prompt) Processing of ORACLE: the prompt processing is used when the SQL analysis execution path generated by ORACLE is not satisfied. It can prompt the following for SQL:
1. Goals:
COST (COST-based optimization)
RULE (RULE-Based Optimization)
CHOOSE (default) (ORACLE automatically selects cost or rules for optimization)
ALL_ROWS (all rows are returned as soon as possible)
FIRST_ROWS (the first row of data is returned as soon as possible)
2. Execution method prompt:
USE_NL (use nested loops to join)
USE_MERGE (Union using merge join)
USE_HASH (Union using hash join)
3. Index prompt:
INDEX (table index) (use the prompted table index for query)
4. Other advanced prompts (such as parallel processing)
The prompt function of ORACLE is a strong function and a complicated application, and the prompt is just a suggestion for ORACLE execution, sometimes ORACLE may not follow the prompts for cost considerations.
According to practical applications, it is generally not recommended for developers to Apply ORACLE prompts. Because the performance of each database and server is different, the performance may be improved in one place, but the performance may be decreased in another place,
ORACLE is mature in SQL Execution analysis,
If the path of the analysis execution is incorrect, the database structure (mainly indexes), current server performance (shared memory, disk file fragments), and database objects (tables and indexes) should be used first) analysis on whether the statistical information is correct

Author: ERDP Technical Architecture"