ORACLE SQL Performance Optimization series (13)

oracle| Performance | optimization
43. Where to replace the order by

The ORDER BY clause uses the index only under two strict conditions.



All columns in the order by must be contained in the same index and remain in the index.

All columns in the order by must be defined as non-null.



The index used in the WHERE clause and the index used in the ORDER BY clause cannot be tied.



For example:

The table dept contains the following:



Dept_code PK not NULL

Dept_desc not NULL

Dept_type NULL



Non-uniqueness Index (DEPT_TYPE)



Inefficient: (index not used)

SELECT Dept_code

From DEPT

ORDER BY Dept_type



EXPLAIN Plan:

SORT ORDER BY

TABLE ACCESS Full



Efficient: (using index)



SELECT Dept_code

From DEPT

WHERE dept_type > 0



EXPLAIN Plan:

TABLE ACCESS by ROWID on EMP

INDEX RANGE SCAN on Dept_idx

Translator by:

An order by can also use an index! This is indeed an easily overlooked point of knowledge. Let's verify:

Sql> SELECT * from emp order by empno;

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=choose

1 0 TABLE ACCESS (by INDEX ROWID) of ' EMP '

2 1 INDEX (full SCAN) of ' EMPNO ' (UNIQUE)



44. Avoid changing the type of indexed columns.

Oracle automatically makes simple type conversions to columns when comparing data of different data types.



Suppose Empno is an indexed column of a numeric type.



SELECT ...

From EMP

WHERE EMPNO = ' 123 '



In fact, after Oracle type conversion, the statement translates to:

SELECT ...

From EMP

WHERE EMPNO = to_number (' 123 ')



Fortunately, the type conversion did not occur on the index column, and the purpose of the index was not changed.



Now, suppose Emp_type is an indexed column of character type.

SELECT ...

From EMP

WHERE Emp_type = 123



This statement is converted by Oracle to:

SELECT ...

From EMP

WHERE to_number (Emp_type) =123



This index will not be used because of a type conversion occurring internally!

Translator by:

To avoid an implicit type conversion of Oracle to your SQL, it is a good idea to explicitly display the type conversion. Note that when characters and numeric comparisons are compared, Oracle converts numeric types to character types preferentially.



45. The WHERE clause needs to be careful

The WHERE clause in some SELECT statements does not use an index. Here are some examples.

In the following example, '!= ' will not use the index. Remember, an index can only tell you what exists in the table, not what doesn't exist in the table.

Do not use indexes:

SELECT account_name

From TRANSACTION

WHERE AMOUNT!=0;

Use index:

SELECT account_name

From TRANSACTION

WHERE AMOUNT >0;



In the following example, ' | | ' is a character join function. As with other functions, the index is deactivated.

Do not use indexes:

SELECT Account_name,amount

From TRANSACTION

WHERE account_name| | Account_type= ' Amexa ';

Use index:

SELECT Account_name,amount

From TRANSACTION

WHERE account_name = ' AMEX '

and account_type= ' A ';



In the following example, ' + ' is a mathematical function. As with other mathematical functions, the index is deactivated.

Do not use indexes:

SELECT account_name, AMOUNT

From TRANSACTION

WHERE AMOUNT + 3000 >5000;

Use index:

SELECT account_name, AMOUNT

From TRANSACTION

WHERE AMOUNT > 2000;

In the following example, the same indexed columns cannot be compared to each other, which enables full table scans.

Do not use indexes:

SELECT account_name, AMOUNT

From TRANSACTION

WHERE account_name = NVL (: acc_name,account_name);

Use index:

SELECT account_name, AMOUNT

From TRANSACTION

WHERE account_name like NVL (: acc_name, '% ');



Translator by:

If you must enable indexing on columns that use functions, Oracle's new functionality: A function-based index may be a better solution.

CREATE INDEX emp_i on EMP (UPPER (ename)); /* Establish a function based index * *

SELECT * from emp WHERE UPPER (ename) = ' blacksnail '; /* will use index * *