ORACLESQL性能優(yōu)化系列(十一)

36. 用UNION替換OR (適用于索引列)
    通常情況下, 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果. 對(duì)索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對(duì)多個(gè)索引列有效. 如果有column沒(méi)有被索引, 查詢效率可能會(huì)因?yàn)槟銢](méi)有選擇OR而降低.
     在下面的例子中, LOC_ID 和REGION上都建有索引.
    高效:
     SELECT LOC_ID , LOC_DESC , REGION
     FROM LOCATION
     WHERE LOC_ID = 10
     :smarttags" />UNION
     SELECT LOC_ID , LOC_DESC , REGION
     FROM LOCATION
     WHERE REGION = “MELBOURNE”
    低效:
     SELECT LOC_ID , LOC_DESC , REGION
     FROM LOCATION
     WHERE LOC_ID = 10 OR REGION = “MELBOURNE”
    如果你堅(jiān)持要用OR, 那就需要返回記錄最少的索引列寫(xiě)在最前面.
    注意:
    WHERE KEY1 = 10 (返回最少記錄)
    OR KEY2 = 20 (返回最多記錄)
    ORACLE 內(nèi)部將以上轉(zhuǎn)換為
    WHERE KEY1 = 10 AND
    ((NOT KEY1 = 10) AND KEY2 = 20)
    譯者按:
    下面的測(cè)試數(shù)據(jù)僅供參考: (a = 1003 返回一條記錄 , b = 1 返回1003條記錄)
    SQL> select * from unionvsor /*1st test*/
     2 where 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
    ----------------------------------------------------------