Oracle SQL optimization notes
Notes for writing Basic SQL statements
- Use the in operator as few as possible. Basically, all in operators can be replaced by exists.
- You can use not exists or external join + instead of the not in operator.
- 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.
- No "<>" or "! = "Operator. Processing Non-equals operators will cause full table scanning, which can be replaced by "<" or ">.
- 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.
- If the wildcard "%" or "_" is the first character of the query string, the index will not be 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.
- Replace "> =" with "> ".
- The Union operator filters the results to eliminate duplicates. If the data volume is large, disk sorting may occur. If you do not need to delete duplicate records, use Union all.
- 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.
- 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. (This is only effective when RBO optimization is adopted. It is described below)
- 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.
- 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.
- Use more internal functions to improve SQL efficiency.
- When connecting multiple tables in an SQL statement, use the table alias and use it as the prefix of each column. This reduces the parsing time.
It should be noted that with the upgrade of Oracle, the query optimizer will automatically optimize SQL statements, and some restrictions may no longer be a problem in the new version of oracle. Especially when CBO (cost-based optimization, cost-based optimization) is used.
We can summarize the operations that may cause full table scan:
- Use not or "<>" on the index column ";
- Use functions or calculations for index columns;
- Not in operation;
- The wildcard character is the first character of the query string;
- Is null or is not null;
- Multiple column indexes, but its first column is not referenced by the WHERE clause;
Oracle optimizer
Oracle optimizer is a tool for Oracle to analyze statements before executing SQL statements.
Oracle optimizer has two optimization methods: Rule-based (RBO) and cost-based (CBO ).
- RBO:The optimizer follows Oracle's predefined rules.
- CBO:The statement execution cost mainly refers to the CPU and memory usage. When determining whether to use CBO, the optimizer should refer to the statistical information of tables and indexes. The statistical information will not be available until analyze is performed on the table. For oracle8 and later versions, CBO is recommended.
The Oracle optimizer has four major optimization modes:
- Rule: Based on rules;
- Choose: default mode. Based on the statistical information of the table or index, if there is statistical information, the CBO method is used. If there is no statistical information and the corresponding column has an index, the RBO method is used.
- First rows: similar to choose. The difference is that if the table has statistical information, it will return the first few rows of the query in the fastest way to get the best response time.
- All rows: fully cost-based mode. When a table has statistical information, it returns all rows of the table as quickly as possible to obtain the maximum throughput. If no statistical information is available, RBO is used.
Set the optimization mode
- Instance level: In init <Sid>. ora Set optimizer_mode in the file;
- Session level: set by SQL> alter session set optimizer_mode =.
- Statement level: Select/* + all + _ rows */......; . Available hint types include/* + all_rows */,/* + first_rows */,/* + choose */, And/* + rule.
Note that if the table has statistical information, the statement may not return the index result. You can use SQL> analyze table table_name Delete statistics; to delete an index.
SQL statement for updating statistics of columns and indexes:
SQL> analyze table table_name compute statistics;
SQL> analyze index index_name estimate statistics;
