36. Replace the OR with UNION (for index columns)
Typically, the OR in the WHERE clause will be replaced with Union will play a better effect. Use OR to the index column will result in full mete scans. Note that the above rules are only valid for multiple indexes. If there is a column is not Index, the query efficiency may be reduced because you didn't choose OR.
In the following example, there is an index on the LOC_ID and Region.
Efficient:
SELECT LOC_ID, LOC_DESC, REGON
From location
WHERE LOC_ID = 10
Union
SELECT LOC_ID, LOC_DESC, REGON
From location
WHERE region = "
Melbourne
"
Inefficient:
SELECT LOC_ID, LOC_DESC, REGON
From location
WHERE LOC_ID = 10 or region = "
Melbourne
"
If you insist on using OR, you need to return to the least index column written in the front.
note:
Where key1 = 10 (return to the minimum record)
Or Key2 = 20 (return to the most record)
Oracle is converted to
Where key1 = 10 and
((Not Key1 = 10) and key2 = 20)
Translator presses:
The following test data is for reference only: (A = 1003 returns a record, b = 1 returns 1003 records)
SQL> Select * from offline / * 1st test * /
2 WHERE A = 1003 or B = 1;
Rows SELECTED.
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 ConcateNation
2 1 Table Access (by index rowid) of 'unionvsor'
3 2 Index (Range Scan) of 'Ub' (Non-Unique)
4 1 Table Access (by Index Rowid) of 'Unionvsor'
5 4 Index (Range scan) of 'ua' (non-unique)
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
144 Consistent Gets
0 Physical READS
0 redo size
63749 BYTES SENT VIA SQL * NET to Client
7751 Bytes Received Via SQL * Net from Cliant
68 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
1003 Rows Processed
SQL> SELECT * ANONVSOR / * 2ND TEST * /
2 WHERE B = 1 or A = 1003;
Rows SELECTED.
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose1 0 Concateation
2 1 Table Access (by index rowid) of 'unionvsor'
3 2 INDEX (RANGE SCAN) of 'UA' (non-unique)
4 1 Table Access (by Index Rowid) of 'Unionvsor'
5 4 Index (Range Scan) of 'Ub' (Non-Unique)
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
143 Consistent Gets
0 Physical READS
0 redo size
63749 BYTES SENT VIA SQL * NET to Client
7751 Bytes Received Via SQL * Net from Cliant
68 SQL * NET ROUNDTRIPS TO / FROM Client
0 Sorts (Memory)
0 Sorts (Disk)
1003 Rows Processed
SQL> SELECT * ANONVSOR / * 3RD Test * /
2 WHERE A = 1003
3 Union
4 SELECT * ANONVSOR
5 WHERE B = 1;
Rows SELECTED.
Execution Plan
-------------------------------------------------- ------------
0 Select Statement Optimizer = Choose
1 0 sort (unique)
2 1 Union-All
3 2 Table Access (by index rowid) of 'Unionvsor'
4 3 INDEX (RANGE SCAN) of 'UA' (Non-Unique)
5 2 Table Access (By Index Rowid) of 'Unionvsor'
6 5 Index (Range Scan) of 'Ub' (Non-Unique)
Statistics
-------------------------------------------------- ------------
0 Recursive Calls
0 DB Block Get
10 consistent gets
0 Physical READS
0 redo size
63735 BYTES SENT VIA SQL * NET to Client
7751 Bytes Received Via SQL * Net from Cliant
68 SQL * NET ROUNDTRIPS TO / FROM Client
1 Sorts (Memory)
0 Sorts (Disk)
1003 Rows Processed
The effect of Union can be seen from the reduction in the data exchange amount of Consistent Gets and SQL * NET.
37. Replace the OR with IN
The following query can be replaced by more efficient statements:
Inefficient:
SELECT ....
From location
WHERE LOC_ID = 10
Or LOC_ID = 20OR LOC_ID = 30
Efficient
SELECT ...
From location
WHERE LOC_IN in (10, 20, 30);
Translator presses:
This is a simple rule, but the actual execution effect must be tested, in Oracle8i, the execution path of the two seems to be the same.
38. Avoid using is NULL and IS NOT NULL in the index columns
Avoid using any columns that can be empty in the index, Oracle will not be able to use the index. For a single column index, this record will not exist if the column contains null values. For a composite index, if each column is empty, this record is not present. If there is at least one column is not empty, then record existence In the index.
Example:
If the unique index is built on the list and B column of the table, there is a recorded A and B value (123, null) in the table, and Oracle will not accept the next article having the same A, B value (123, null) Record (insert). However
All index columns are empty, and Oracle will consider the entire key to empty, so you can insert 1000
The strip has a record of the same key value, of course they are empty!
Since null value does not exist in the index column, the NG value comparison of the index column in the WHERE clause will cause Oracle to deactivate the index.
Example:
Inefficient: (Index Failure)
SELECT ...
From department
WHERE DEPT_CODE IS NOT NULL;
Efficient: (Index is effective)
SELECT ...
From department
WHERE DEPT_CODE> = 0;
