16. 基礎表的選擇基礎表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 根據優化器的不同, SQL語句中基礎表的選擇是不一樣的.
如果你使用的是CBO (COST BASED OPTIMIZER),優化器會檢查SQL語句中的每個表的物理大小,索引的狀態,然後選用花費最低的執行路徑.
如果你用RBO (RULE BASED OPTIMIZER) , 並且所有的連接條件都有索引對應, 在這種情況下, 基礎表就是FROM 子句中列在最後的那個表.
舉例:
SELECT A.NAME , B.MANAGER
FROM WORKER A,
LODGING B
WHERE A.LODGING = B.LODING;
由於LODGING表的LODING列上有一個索引, 而且WORKER表中沒有相比較的索引, WORKER表將被作為查詢中的基礎表. 17. 多個平等的索引
當SQL語句的執行路徑可以使用分布在多個表上的多個索引時, Oracle會同時使用多個索引並在運行時對它們的記錄進行合並, 檢索出僅對全部索引有效的記錄.
在Oracle選擇執行路徑時,唯一性索引的等級高於非唯一性索引. 然而這個規則只有
當WHERE子句中索引列和常量比較才有效.如果索引列和其他表的索引類相比較. 這種子句在優化器中的等級是非常低的.
如果不同表中兩個想同等級的索引將被引用, FROM子句中表的順序將決定哪個會被率先使用. FROM子句中最後的表的索引將有最高的優先級.
如果相同表中兩個想同等級的索引將被引用, WHERE子句中最先被引用的索引將有最高的優先級.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME,
FROM EMP
WHERE DEPT_NO = 20
AND EMP_CAT = ‘A’;
這裡,DEPTNO索引將被最先檢索,然後同EMP_CAT索引檢索出的記錄進行合並. 執行路徑如下: TABLE Access BY ROWID ON EMP
AND-EQUAL
INDEX RANGE SCAN ON DEPT_IDX
INDEX RANGE SCAN ON CAT_IDX 18. 等式比較和范圍比較
當WHERE子句中有索引列, ORACLE不能合並它們,Oracle將用范圍比較. 舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT = ‘A’;
這裡只有EMP_CAT索引被用到,然後所有的記錄將逐條與DEPTNO條件進行比較. 執行路徑如下:
TABLE Access BY ROWID ON EMP
INDEX RANGE SCAN ON CAT_IDX 19. 不明確的索引等級 當Oracle無法判斷索引的等級高低差別,優化器將只使用一個索引,它就是在WHERE子句中被列在最前面的.
舉例:
DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.
SELECT ENAME
FROM EMP
WHERE DEPTNO > 20
AND EMP_CAT > ‘A’; 這裡, Oracle只用到了DEPT_NO索引. 執行路徑如下:
TABLE Access BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
我們來試一下以下這種情況:
SQL> select index_name, uniqueness from user_indexes where table_name = 'EMP'; INDEX_NAME UNIQUENES
------------------------------ ---------
EMPNO UNIQUE
EMPTYPE NONUNIQUE SQL> select * from emp where empno >= 2 and emp_type = 'A' ; no rows selected Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE Access (BY INDEX ROWID) OF 'EMP'
2 1 INDEX (RANGE SCAN) OF 'EMPTYPE' (NON-UNIQUE) 雖然EMPNO是唯一性索引,但是由於它所做的是范圍比較, 等級要比非唯一性索引的等式比較低!
20. 強制索引失效
如果兩個或以上索引具有相同的等級,你可以強制命令Oracle優化器使用其中的一個(通過它,檢索出的記錄數量少) . 舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 7935
AND DEPTNO + 0 = 10 /*DEPTNO上的索引將失效*/
AND EMP_TYPE || ‘’ = ‘A’ /*EMP_TYPE上的索引將失效*/ 這是一種相當直接的提高查詢效率的辦法. 但是你必須謹慎考慮這種策略,一般來說,只有在你希望單獨優化幾個SQL時才能采用它. 這裡有一個例子關於何時采用這種策略, 假設在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引. SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS = ‘X’; 優化器會注意到EMP_TYPE上的索引並使用它. 這是目前唯一的選擇. 如果,一段時間以後, 另一個非唯一性建立在EMP_CLASS上,優化器必須對兩個索引進行選擇,在通常情況下,優化器將使用兩個索引並在他們的結果集合上執行排序及合並. 然而,如果其中一個索引（EMP_TYPE）接近於唯一性而另一個索引（EMP_CLASS）上有幾千個重復的值. 排序及合並就會成為一種不必要的負擔. 在這種情況下,你希望使優化器屏蔽掉EMP_CLASS索引.
用下面的方案就可以解決問題.
SELECT ENAME
FROM EMP
WHERE EMP_TYPE = ‘A’
AND EMP_CLASS||’’ = ‘X’; 21. 避免在索引列上使用計算．
WHERE子句中，如果索引列是函數的一部分．優化器將不使用索引而使用全表掃描． 舉例: 低效：
SELECT …
FROM DEPT
WHERE SAL * 12 > 25000; 高效:
SELECT …
FROM DEPT
WHERE SAL > 25000/12;
這是一個非常實用的規則，請務必牢記 22. 自動選擇索引
如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．
在這種情況下，Oracle將使用唯一性索引而完全忽略非唯一性索引． 舉例:
SELECT ENAME
FROM EMP
WHERE EMPNO = 2326
AND DEPTNO = 20 ; 這裡，只有EMPNO上的索引是唯一性的，所以EMPNO索引將用來檢索記錄．
TABLE Access BY ROWID ON EMP
INDEX UNIQUE SCAN ON EMP_NO_IDX 23. 避免在索引列上使用NOT
通常， 我們要避免在索引列上使用NOT, NOT會產生在和在索引列上使用函數相同的
影響. 當Oracle”遇到”NOT ,!=,他就會停止使用索引轉而執行全表掃描.
舉例: 低效: (這裡,不使用索引) SELECT …
FROM DEPT
WHERE DEPT_CODE != 0;
高效: (這裡,使用了索引) SELECT …
FROM DEPT
WHERE DEPT_CODE > 0; 需要注意的是,在某些時候, Oracle優化器會自動將NOT轉化成相對應的關系操作符.
NOT > to <=
NOT >= to <
NOT < to >=
NOT <= to > 24. 用>=替代> 如果DEPTNO上有一個索引, 高效: SELECT *
FROM EMP
WHERE DEPTNO >=4
低效: SELECT *
FROM EMP
WHERE DEPTNO >3 兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄.
25. 用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的數據交換量的減少看出
