SQL優化常用方法46

1. 連線多個掃描
   如果你對一個列和一組有限的值進行比較, 優化器可能執行多次掃描並對結果進行合併連線. 舉例:

SELECT * FROM LODGING WHERE MANAGER IN (‘BILL GATES`,`KEN MULLER`);
優化器可能將它轉換成以下形式
SELECT * FROM LODGING WHERE MANAGER = ‘BILL GATES` OR MANAGER = `KEN MULLER`;
當選擇執行路徑時, 優化器可能對每個條件採用LODGING$MANAGER上的索引範圍掃描. 返回的ROWID用來訪問LODGING表的記錄 (通過TABLE ACCESS BY ROWID 的方式). 最後兩組記錄以連線(CONCATENATION)的形式被組合成一個單一的集合. Explain Plan :
SELECT STATEMENT Optimizer=CHOOSE
CONCATENATION
TABLE ACCESS (BY INDEX ROWID) OF LODGING
INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)
TABLE ACCESS (BY INDEX ROWID) OF LODGING
INDEX (RANGE SCAN ) OF LODGING$MANAGER (NON-UNIQUE)