Optimization Summary of oracle Database sql

1: Use where and use having less;

2: When checking more than two tables, put the few records on the right;

3. Reduce the number of visits to tables;

4: When there is an where subquery, the subquery is placed first;

5: Try to avoid using * in select statements (* will be converted into column names in turn when executed);

6: Use commit as much as possible;

7: Decode can avoid repeatedly scanning the same records or repeatedly joining the same tables;

8: The efficiency of sql can also be improved through internal functions;

9: When joining multiple tables, use aliases and prefix them on each field;

10: Replace in with exists

101: not exists replaces not in (not in sentence will perform an internal sort and merge, in any case, not in is the least efficient, and the whole table is scanned in subquery. To avoid using not in, it can be rewritten as outer joins or not exists);

102: Table join is more efficient than exists;

103: Replace distinct with exists

Example:

Low: High:

select distinct dept_no, dept_name select dept_no, dept_name

from dept d, emp e from dept d

where d.dept_no = e.dept_no; where exists (select 1 from emp e where e.dept_no = d.dept_no);

104: Use the TKPROF tool to query the sql performance status;

105: Efficiency with indexing (at the cost of space and the need to reconstruct the index regularly: ALTER INDEX < INDEXNAME > REBUILD < TABLESPACENAME);

First, introduce the principle of index, so as to facilitate the understanding of index optimization:

Find rowid through the index, and then access the table through rowid. However, if the columns of the query are included in index, the Part 2 operation will not be performed, because the retrieval data is stored in the index, and accessing the index alone can fully meet the query requirements.

Premise summary: In 106 cases, LODGING has only one index; There is a non-unique index on the MANAGER column.

106: Index Range Query (INDEX RANGE SACEN):

Applicable to two situations:

1) Query based on 1 range:

SELECT LODGING FROM LODGING WHERE LODGING LIKE 'M%'

(where sentence condition includes 1 series of values, oracle will query LODGING_PK through index range query)

2) Retrieval based on non-uniqueness index:

SELECT LODGING FROM LODGING WHERE MANAGER = 'LI';

(This query is in two steps: the index range query for LODGING $MANAGER gets the rowid for all eligible records, and then gets the values of the LODGING column through the rowid access table. This index is a non-unique index, and the database cannot perform an index-only scan on it.)

In the where sentence, if the first character of the value corresponding to the index column starts with a wildcard character, the index will not be adopted, but will be scanned all over the table, such as SELECT...... WHERE MANAGER LIKE '% LI'

107: Selection of basic tables:

Base table: The first table accessed (usually accessed by full table scanning).

Depending on the optimizer, the selection of the underlying table in the SQL statement is different:

If CBO is used, the optimizer checks the physical size of each table in the SQL statement, the status of the index, and then selects the path with the lowest call cost.

If RBO is used and all join conditions have index correspondence, the underlying table is the last table listed in the FROM sentence

Example:

SELECT A.NAME, B.MANAGER FROM WOKER A, LODGING B WHERE A.LODGING = B.LODGING;

Since there is one index on the LODGING column and there is no comparison index in the WORKER table, the WORKER table will be used as the query base table.

108: Multiple equal indexes:

When the execution path of an SQL statement can use multiple indexes spread across multiple tables, oracle uses multiple indexes and merges their records at run time, retrieving records that are valid only for all indexes.

The oracle selected execution path is that the only 1 index is higher than the non-only 1 index, and only when the index column and constant comparison in the where sentence are valid. If the index column is compared to the index column of other tables, this sentence is very low in the optimizer;

If two indexes of the same rank in different tables are to be referenced, which is to be used first is based on the order of the tables in the FROM sentence. The last table index in the FROM sentence takes precedence. If two indexes of the same rank in the same table are to be referenced, the index referenced first in the where sentence will have the highest priority.

Example: There is a non-unique index on DEPTNO, and there is also a non-unique index on EMP_CAT

SELECT ENAME FROM EMP WHERE DEPT_NO = 20 AND EMP_CAT = 'A';

The DEPTNO index will be retrieved first and then merged with the results retrieved by the EMP_CAT index as follows:

TABLE ACCESS BY ROWID ON EMP

AND _EQUAL

INDEX RANGE SCAN ON DEPT_IDX

INDEX RANGE SCAN ON CAT_IDX

109: Equality comparison and range comparison:

Let's start with an example:

SELECT ENAME FROM EMP WHERE DEPT_NO > 20 AND EMP_CAT = 'A';

(Under the premise of two non-uniqueness indexes) at this time, the range index is not used, and the records are queried through the EMP_CAT index and compared with the DEPT_NO condition

Note: Uniqueness, so when comparing the scope, the grade is lower than the equation of non-uniqueness index;

210: Force index invalidation:

If two or more indexes have the same level, you can force the oracle optimizer to use one of them. When to use this strategy? If one index is close to the only one, and the other index has many duplicate values, sorting and merging will become a burden instead, and the latter can be masked to invalidate its index.

(Failure mode: Add calculation '+0' or '""' to index column);