43. Replace Order by WHERE
The ORDER BY clause uses an index only under two strict conditions.
All columns in ORDER BY must be included in the same index and remain in the index in the index.
All columns in Order BY must be defined as non-empty.
The index used by WHERE clauses and the index used in the Order By clause cannot be columns.
E.g:
Table DEPT includes the following:
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
Non-only index (dept_type)
Inefficient: (index is not used)
Select dept_code
From dept
Order by DePT_TYPE
Explain Plan:
Sort ORDER BY
Table Access Full
Efficient: (Use 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 presses:
ORDER BY can also use an index! This is indeed a knowledge point that is easy to overlook. 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 index.
When comparing data for different data types, Oracle automatically makes simple type conversion.
Suppose EmpNO is a numeric type index column.
SELECT ...
From EMP
WHERE EMPNO = '123'
In fact, after the Oracle type conversion, the statement is transformed into:
SELECT ...
From EMP
WHERE Empno = TO_NUMBER ('123')
Fortunately, the type conversion does not occur on the index column, and the use of the index is not changed.
Now, assume that EMP_TYPE is an index column of a character type.
SELECT ...
From EMP
WHERE EMP_TYPE = 123
This statement is converted by Oracle:
SELECT ...
From EMP
Where to_number (EMP_TYPE) = 123
This index will not be used because of the type conversion of internal incidence!
Translator presses:
To avoid Oracle's implicit type conversion, it is best to express type conversion with explicitly. Note that Oracle is prioritized to translate numerical types to character types when compared to characters and numerical values.
45. Need to beware of WHERE clause
There is no index in some SELECT statements. There are some examples here.
In the following example, '! =' Will not use an index. Remember, the index can only tell you what exists in the table, and cannot tell you that it does not exist in the table.
Do not use the index:
SELECT Account_name
From Transaction
WHERE AMOUNT! = 0;
Use an index:
SELECT Account_name
From Transaction
WHERE AMOUNT> 0;
In the example below, '||' is a character connection function. So similar to other functions, the index is deactivated.
Do not use the index:
SELECT ACCOUNT_NAME, AMOUNT
From Transaction
Where account_name || account_type = 'amexa';
Use an index:
Select Account_name, AmountFrom Transaction
Where account_name = 'amex'
And Account_Type = 'A';
In the following example, ' ' is a mathematical function. Since other mathematical functions, the index is deactivated.
Do not use the index:
SELECT ACCOUNT_NAME, AMOUNT
From Transaction
WHERE AMOUNT 3000> 5000;
Use an index:
SELECT ACCOUNT_NAME, AMOUNT
From Transaction
WHERE AMOUNT> 2000;
In the following example, the same index column cannot be compared to each other, which will enable full mete scan.
Do not use the index:
SELECT ACCOUNT_NAME, AMOUNT
From Transaction
Where account_name = NVL (: ACC_NAME, Account_name);
Use an index:
SELECT ACCOUNT_NAME, AMOUNT
From Transaction
Where account_name like NVL (: ACC_NAME, '%');
Translator presses:
If you must enable indexing using a column using a function, Oracle's new features: Function-based Index is perhaps 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 * /
