36. With UNION Replace OR (Suitable for Index Columns) Normally, Alternative in the WHERE clause in Union will play a better effect. Use OR to the index will cause full mete scans. Note that the above rules are only It is valid for multiple index columns. If there is a column that is not indexed, the query efficiency may decrease because you didn't choose or, in the example, Loc_ID and Region have an index. 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" inefficient: SELECT LOC_ID, LOC_DESC, Region from location where loc_id = 10 or region = "Melbourne" If you insist on using OR, then Need to return the least record column written in the forefront. Note: Where key1 = 10 (Return to least record) or key2 = 20 (Return to the most record) Oracle inside the above convert to where key1 = 10 and ((not key1 = 10) And key2 = 20) Translator Press: The following test data is for reference only: (a = 1003 returns a record, b = 1 Return 1003 records) SQL> Select * from offvsor / * 1st test * / 2 where a a = 1003 OR B = 1; 1003 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 Gets 144 Consistent Gets 0 Physical Reads 0 Redo Size 63749 BYtes Sent Via SQL * Net To Client 7751 BYtes Received Via SQL * Net from CLIENT 68 SQL * NET ROUNDTRIPS TO / FROM Client 0 Sorts (Memory) 0 Sorts (Disk) 1003 Rows Processed SQL> Select * from Unionvsor / * 2nd Test * / 2 WHERE B = 1 OR A = 1003; 1003 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 'UA' (Non-Unique) 4 1 Table Access (By Index Rowid) of 'Unionvsor' 5 4 Index (Non-Unique) statistics ---- -------------------------------------------------- ---- 0 recursive calls 0 db block gets 143 consistent gets 0 physical reads 0 redo size 63749 bytes sent via SQL * Net to client 7751 bytes received via SQL * Net from client 68 SQL * Net roundtrips to / from client 0 sorts ( Memory) 0 Sorts (Disk) 1003 Rows Processed SQL> Select * from Unionvsor / * 3rd Test * / 2 WHERE A = 1003 3 Union 4 Select * from Unionvsor 5 WHERE B = 1; 1003 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 gets 10 consistent gets 0 physical reads 0 redo size 63735 bytes sent via SQL * Net to client 7751 bytes received via SQL * Net from client 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. Replacing the following queries can be replaced by more efficient statements: SELECT .... Location WHERE LOC_ID = 10 or LOC_ID = 20 or LOC_ID = 30 High Efficiency Select ... from location where loc_in in (10, 20, 30); Translator Press: This is a simple rule, but the actual implementation is also subject to test. In Oracle8i, the execution path of the two seems to be the same. 38. Avoid using IS NULL and IS NOT NULL on an index column to avoid using any empty columns in an index, and Oracle will not be able to use the index. For a single column index, if the column contains a null value,
