Oracle SQL optimization)

Oracle SQL optimization rules:

• Use the in operator as few as possible. Basically, all in operators can be replaced by exists.

The SQL statements written in are easier to write and understand, but the SQL statements written in always have low performance, the steps executed by Oracle are used to analyze the differences between SQL statements using in and SQL statements without using 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.

When Oracle executes the in subquery, it first executes the subquery, puts the query result into a temporary table, and then executes the primary query. Exist checks the primary query first, and then runs the subquery until the first matching item is found. Not exists is more efficient than not in. However, when selecting an in or exist operation, you must consider the data size of the primary table and sub-table.

Recommended Solution: avoid using the in operator in business-intensive SQL statements.

The not in operator can be replaced by not exists or external join +.

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.

• No "<>" or "! = "Operator. Processing Non-equals operators will cause full table scanning, which can be replaced by "<" or ">"

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:
1) change a <> 0 to a> 0 or a <0
2) A <> ''To A>''

• If the WHERE clause is null or is not null, Oracle stops using the index and performs a full table scan. When designing a table, you can set the index column to not null. In this way, other operations can be used to replace null operations.

Is null or is not null (whether the field is null)
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:
1) change a is not null to a> 0 or a>.
2) fields that are not allowed to be empty are replaced by a default value. For example, status fields in the application for expansion cannot be empty. The default value is apply.
3) create a bitmap index (a partitioned table cannot be created, and the bitmap index is difficult to control. If there are too many fields, the index will degrade the performance, and the data block lock will be added during multi-person update operations)

• When the wildcard "%" or "_" is the first character of the query string, the index is not used.
• For the connected column "|", the index of the last connected column is invalid. Avoid connections whenever possible. Separate connections or use functions that do not work on columns.
• If the index is not function-based, the index will no longer function when a function is used for the index column in The WHERE clause.
• Avoid using calculation in the index column in The WHERE clause. Otherwise, the entire table is scanned because the index fails.
• When comparing columns of different data types, the index will become invalid.
• > 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, but in some cases it can be optimized. For example, a table has 1 million records, for a numeric field a, 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.

Recommended Solution: Replace "> =" with "> ".

• 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

• 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 actual example: the user ID following the Business ID in the yw_yhjbqk table can be used to query the Business ID yy_bh like '% 100'. This condition will generate 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.

• Influence of SQL writing (sharing SQL statements can improve operation efficiency)

Impact of SQL statement writing with the same function and performance

For example, 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 programmers write
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 once and occupy the shared memory. If the SQL string and format are completely the same, Oracle will analyze it only once, the shared memory also leaves only one analysis result, which not only reduces the time for SQL analysis, but also reduces duplicate information in the shared memory. Oracle can also accurately count the execution frequency of SQL.

Recommended Solution: Make sure that the query characters are identical for the same SQL statements in different regions, so as to use the SGA shared pool to prevent the same SQL statements from being analyzed multiple times.

• Effect of conditional order after where

Oracle processes multiple query conditions in the WHERE clause from bottom to top. Therefore, the table join statement should be written before other where conditions. The conditions for filtering the maximum number of records must be written at the end of the WHERE clause.

The conditional order after the WHERE clause directly affects the query of the big data table, as shown in figure

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.

• Query table order

Oracle processes the table names in the from clause from right to left. Therefore, when the from clause contains multiple tables, the table with the least records is placed at the end. (Effective only when RBO optimization is adopted)

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 a table, Oracle automatically links small tables before large tables ).

• The non-index column in the order by statement reduces performance and can be processed by adding an index. Strictly control the use of expressions in order by statements
• When connecting multiple tables in an SQL statement, use the table alias and use it as the prefix of each column. This reduces the resolution time.
• Use more internal functions to improve SQL Efficiency
• SQL statement index Utilization
  • Optimization of operators (see the previous section)
  • Optimization of condition fields
    • 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)

    • Fields with 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.

    • When multiple fields in the table are included in the condition, indexes cannot be performed.

    Ys_df> cx_df, cannot be optimized

    Qc_bh | kh_bh = '000000', optimization: qc_bh = '000000' and kh_bh = '000000'

Operations that may cause full table Scan

• Use not or "<>" on the index Column"
• Use function or calculation for index Columns
• Not in Operation
• The wildcard character is the first character of the query string.
• Is null or is not null
• Multi-column index, but its first column is not referenced by the WHERE clause

Oracle has been mature in SQL Execution analysis. If the analysis execution path is incorrect, it should first be in the database structure (mainly index), current server performance (shared memory, disk file fragments) and whether the statistical information of database objects (tables and indexes) is correct.