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   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   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   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;