Oracle index optimization rules

 

Index optimization rules:

1. Do not start with a WILDCARD (WILDCARD) in the like file. Otherwise, the index will not be used.

Example: SELECT LODGING FROM LODGING

Where manager like '% HANMAN ';

2. Avoid using calculation on the index column or changing the index column type or using '! = 'And <>

Example: SELECT... From dept where sal * 12> 25000;

SELECT... From emp where EMP_TYPE = to_char (123 );

Select .... Where ACCOUNT_NAME | ACCOUNT_TYPE = 'amexa ';

Select... Where empno! = 8888;

3. Avoid using NOT in the index column.

4. Replace with> =>.

Efficient: SELECT * from emp where deptno> = 4

Inefficient: SELECT * from emp where deptno> 3

The difference between the two lies in that the former DBMS will jump directly to the first record whose DEPT is equal to 4, while the latter will first locate the record whose DEPTNO is = 3 and scan forward to the record whose first DEPT is greater than 3.

• 5. replace OR with UNION (applicable to index columns)

• In general, replacing OR in the WHERE clause with UNION will produce better results. using OR for index columns will scan the entire table. note that the preceding statements are only valid for multiple index columns. if a column is not indexed, the query efficiency may be reduced because you did not select the O rule R.

 

In the following example, both LOC_ID and REGION have indexes.

 

Efficient:

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

WHERE LOC_ID = 10

UNION

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

Where region = "MELBOURNE"

• Inefficiency:

SELECT LOC_ID, LOC_DESC, REGION

FROM LOCATION

WHERE LOC_ID = 10 or region = "MELBOURNE"

If you insist on using OR, you need to write at the beginning of the index column with the least record returned.

• Note: WHERE KEY1 = 10 (minimum records are returned)

OR KEY2 = 20 (the maximum number of records returned)

ORACLE converts the above

WHERE KEY1 = 10 AND (NOT KEY1 = 10) AND KEY2 = 20)

6. Avoid using is null and is not null in the index column.

To avoid using any columns that can be empty in the index, ORACLE will not be able to use this index. this record does not exist in the index if the column contains a null value. for a composite index, if each column is empty, this record does not exist in the index. if at least one column is not empty, the record is stored in the index.

(Suggestion: you can set a default value for a null field ))

7. If the index is created on multiple columns, the index period must be placed in the first condition of the where condition (before Oracle8i), and the jump index is allowed after Oracle8i.

8. (if possible) replace UNION with UNION-ALL.

UNION-ALL is a simple merge without sorting. UNION is a simple merge first, followed by sorting, and deduplication of records.

9. Avoid resource-consuming operations

SQL statements with DISTINCT, UNION, MINUS, INTERSECT, and order by will start the SQL engine.

 

Execute the resource-consuming sorting (SORT) function. DISTINCT requires a sorting operation, and the other requires at least two sorting operations.

 

For example, for a UNION query, where each query has a group by clause, group by triggers NESTED sorting. In this way, each query needs to be sorted once, then, when UNION is executed, another sort unique operation is executed and can only be executed after the preceding embedded sorting is completed. the depth of embedded sorting will greatly affect the query efficiency.

 

Generally, SQL statements with UNION, MINUS, and INTERSECT can be rewritten in other ways.