Basic SQL Authoring Considerations
- Use the In operator sparingly, and basically all in operators can be replaced with exists.
- Instead of the not-in operator, you can use NOT EXISTS or outer join + substitution.
- When Oracle executes an in subquery, it first executes the subquery, puts the query results into the staging table, and executes the main query. Instead, exist first checks the main query and then runs the subquery until the first match is found. The not exists is slightly more efficient than not. However, in selecting the in or exist operation, it is necessary to consider the size of the master table data.
- Do not use the "<>" or "! =" operators. Handling of non-equal operators results in a full table scan, which can be replaced with "<" or ">".
- Oracle stops using the index to perform a full table scan when the is null or is not NULL appears in the WHERE clause. You can consider setting the index column to not NULL when designing the table. This allows you to replace the null operation with another operation.
- When the wildcard character "%" or "_" as the first of the query string
- Characters, the index is not used.
- For a connected column ' | | ', the last Connection column index will be invalid. Avoid connections as much as possible, either by separating them or by using functions that do not function on the columns.
- If the index is not function-based, the index no longer works when the function is used on the indexed column in the WHERE clause.
- Avoid using calculations on indexed columns in the WHERE clause, which will cause the index to fail with a full table scan.
- Indexes are invalidated when comparisons are made to columns of different data types.
- Replace ">" with ">=".
- The union operator filters the results, eliminates duplicates, and may cause the disk to be sorted if the amount of data is large. If you do not need to delete duplicate records, you should use UNION ALL.
- Oracle processes multiple query conditions in the WHERE clause from bottom to top, so that the table join statement should be written before the other where condition, the condition that can filter out the maximum number of records must be written at the end of the WHERE clause.
- Oracle handles the table names in the FROM clause from right to left, so if you include more than one table in the FROM clause, the table with the fewest records is placed last. (valid only when using RBO optimization, detailed below)
- Non-indexed columns in the Order by statement degrade performance and can be processed by adding an index. Strictly controls the use of an expression in an order by statement.
- The same SQL statements appear in different regions, to ensure that the query characters are exactly the same, to take advantage of the SGA shared pool, to prevent the same SQL statement being analyzed multiple times.
- Multi-use intrinsic functions to improve SQL efficiency.
- When you concatenate multiple tables in an SQL statement, use the alias of the table and prefix each column with it. This can reduce parsing time.
It is important to note that as Oracle upgrades, the query optimizer automatically optimizes SQL statements, and some limitations may no longer be a problem under the new version of Oracle. Especially when using CBO (cost-based optimization, cost-based optimization).
We can summarize the operations that might cause a full table scan:
- Use NOT OR "<>" on the index column;
- Use functions or calculations on indexed columns;
- Not in operation;
- Wildcards are located in the first character of a query string;
- Is null or is not NULL;
- A multi-column index, but its first column is not referenced by the WHERE clause;
Oracle Optimizer
The Oracle Optimizer (Optimizer) is a tool for Oracle to parse statements before executing SQL.
There are two optimizations for Oracle's optimizer: rule-based (RBO) and cost-based (CBO).
- RBO: the optimizer follows Oracle's internal scheduled rules.
- CBO: Depending on the cost of statement execution, it mainly refers to CPU and memory consumption. The optimizer refers to the statistics of tables and indexes when deciding whether to use the CBO. Statistics should be analyze after the table is done. Oracle8 and later versions, it is recommended to use the CBO method.
There are four main optimization modes for Oracle Optimizer:
- Rule: rules-based;
- Choose: Default mode. Depending on the statistics of the table or index, if there is statistics, the CBO method is used, and if there are no statistics, the corresponding column is indexed, then the Rbo method is used.
- First rows: similar to choose. The difference is that if the table has statistics, it will return the first few rows of the query in the quickest way to get the best response time.
- All rows: Fully cost-based mode. When a table has statistics, all rows of the table are returned in the quickest way for maximum throughput. No statistical information is used in RBO mode.
How to set the optimization mode
- Instance level: Set the Optimizer_mode in the Init<sid>.ora file;
- Session level: Set by sql> ALTER session set optimizer_mode=;
- Statement level: Through sql> SELECT/*+all+_rows*/...; To set. The available hint include/*+all_rows*/,/*+first_rows*/,/*+choose*/,/*+rule*/, and so on.
Note that if the table has statistics, it may cause the statement not to walk the result of the index. You can use Sql>analyze TABLE table_name DELETE STATISTICS; Deletes an index.
SQL to update statistics on columns and indexes:
sql> ANALYZE TABLE table_name COMPUTE STATISTICS;
sql> ANALYZE INDEX index_name ESTIMATE STATISTICS;
Basic SQL Authoring Considerations
