ORACLE SQL性能優化系列 (六)

　　通常情況下, 用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下，兩者的執行路徑似乎是相同的．