ORACLE SQL效能最佳化系列 (十一)

36.       用UNION替換OR (適用於索引列)

通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低.

   在下面的例子中, LOC_ID 和REGION上都建有索引.

高效:

   SELECT LOC_ID , LOC_DESC , REGION

   FROM LOCATION

   WHERE LOC_ID = 10

   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”

 

如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面.

 

注意:

 

WHERE KEY1 = 10   (返回最少記錄)

OR KEY2 = 20        (返回最多記錄)

 

ORACLE 內部將以上轉換為

WHERE KEY1 = 10 AND

((NOT KEY1 = 10) AND KEY2 = 20)       

 

譯者按:

 

下面的測試資料僅供參考: (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

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

   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 (RANGE SCAN) OF 'UB' (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

用UNION的效果可以從consistent gets和 SQL*NET的資料交換量的減少看出

 

37.       用IN來替換OR

 

下面的查詢可以被更有效率的語句替換:

 

低效:

 

SELECT….

FROM LOCATION

WHERE LOC_ID = 10

OR     LOC_ID = 20

OR     LOC_ID = 30

 

高效

SELECT…

FROM LOCATION

WHERE LOC_IN IN (10,20,30);

   

譯者按:

這是一條簡單易記的規則，但是實際的執行效果還須檢驗，在ORACLE8i下，兩者的執行路徑似乎是相同的．　

 

 

38.       避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以為空白的列，ORACLE將無法使用該索引 ．對於單列索引，如果列包含空值，索引中將不存在此記錄. 對於複合索引，如果每個列都為空白，索引中同樣不存在此記錄.　如果至少有一個列不為空白，則記錄存在於索引中．

舉例:

 如果唯一性索引建立在表的A列和B列上, 並且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入). 然而如果

所有的索引列都為空白，ORACLE將認為整個索引值為空白而空不等於空. 因此你可以插入1000

條具有相同索引值的記錄,當然它們都是空!

 

      因為空白值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引.

舉例:

 

低效: (索引失效)

SELECT …

FROM DEPARTMENT

WHERE DEPT_CODE IS NOT NULL;

 

高效: (索引有效)

SELECT …

FROM DEPARTMENT

WHERE DEPT_CODE >=0;