【文章內(nèi)容簡介】 PK . 由于索引范圍查詢將返回一組值, 它的效率就要比索引唯一掃描低一些. 例2: SELECT LODGINGFROM LODGINGWHERE MANAGER = ‘BILL GATES’。 這個SQL的執(zhí)行分兩步, LODGING$MANAGER的索引范圍查詢(得到所有符合條件記錄的ROWID) 和下一步同過ROWID訪問表得到LODGING列的值. 由于LODGING$MANAGER是一個非唯一性的索引,數(shù)據(jù)庫不能對它執(zhí)行索引唯一掃描.由于SQL返回LODGING列,而它并不存在于LODGING$MANAGER索引中, 所以在索引范圍查詢后會執(zhí)行一個通過ROWID訪問表的操作. WHERE子句中, 如果索引列所對應(yīng)的值的第一個字符由通配符(WILDCARD)開始, 索引將不被采用. SELECT LODGINGFROM LODGINGWHERE MANAGER LIKE ‘％HANMAN’。 在這種情況下，ORACLE將使用全表掃描.ORACLE SQL性能優(yōu)化系列 (九) 27. 基礎(chǔ)表的選擇 基礎(chǔ)表(Driving Table)是指被最先訪問的表(通常以全表掃描的方式被訪問). 根據(jù)優(yōu)化器的不同, SQL語句中基礎(chǔ)表的選擇是不一樣的.如果你使用的是CBO (COST BASED OPTIMIZER),優(yōu)化器會檢查SQL語句中的每個表的物理大小,索引的狀態(tài),然后選用花費最低的執(zhí)行路徑.如果你用RBO (RULE BASED OPTIMIZER) , 并且所有的連接條件都有索引對應(yīng), 在這種情況下, 基礎(chǔ)表就是FROM 子句中列在最后的那個表.舉例:SELECT , FROM　WORKER A,LODGING BWHERE　 = 。由于LODGING表的LODING列上有一個索引, 而且WORKER表中沒有相比較的索引, WORKER表將被作為查詢中的基礎(chǔ)表. 28. 多個平等的索引當(dāng)SQL語句的執(zhí)行路徑可以使用分布在多個表上的多個索引時, ORACLE會同時使用多個索引并在運行時對它們的記錄進(jìn)行合并, 檢索出僅對全部索引有效的記錄.在ORACLE選擇執(zhí)行路徑時,唯一性索引的等級高于非唯一性索引. 然而這個規(guī)則只有. 這種子句在優(yōu)化器中的等級是非常低的.如果不同表中兩個想同等級的索引將被引用, FROM子句中表的順序?qū)Q定哪個會被率先使用. FROM子句中最后的表的索引將有最高的優(yōu)先級.如果相同表中兩個想同等級的索引將被引用, WHERE子句中最先被引用的索引將有最高的優(yōu)先級.舉例:DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.SELECT ENAME,FROM EMPWHERE DEPT_NO = 20AND EMP_CAT = ‘A’。這里,DEPTNO索引將被最先檢索,然后同EMP_CAT索引檢索出的記錄進(jìn)行合并. 執(zhí)行路徑如下: TABLE ACCESS BY ROWID ON EMPANDEQUALINDEX RANGE SCAN ON DEPT_IDXINDEX RANGE SCAN ON CAT_IDX 29. 等式比較和范圍比較當(dāng)WHERE子句中有索引列, ORACLE不能合并它們,ORACLE將用范圍比較. 舉例:DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引.SELECT ENAMEFROM EMPWHERE DEPTNO 20AND EMP_CAT = ‘A’。 這里只有EMP_CAT索引被用到,然后所有的記錄將逐條與DEPTNO條件進(jìn)行比較. 執(zhí)行路徑如下:TABLE ACCESS BY ROWID ON EMPINDEX RANGE SCAN ON CAT_IDX 30. 不明確的索引等級 當(dāng)ORACLE無法判斷索引的等級高低差別,優(yōu)化器將只使用一個索引,它就是在WHERE子句中被列在最前面的.舉例:DEPTNO上有一個非唯一性索引,EMP_CAT也有一個非唯一性索引. SELECT ENAMEFROM EMPWHERE DEPTNO 20AND EMP_CAT ‘A’。 這里, ORACLE只用到了DEPT_NO索引. 執(zhí)行路徑如下: TABLE ACCESS BY ROWID ON EMPINDEX RANGE SCAN ON DEPT_IDX 譯者按:我們來試一下以下這種情況:SQL select index_name, uniqueness from user_indexes where table_name = 39。EMP39。 INDEX_NAME UNIQUENES EMPNO UNIQUEEMPTYPE NONUNIQUE SQL select * from emp where empno = 2 and emp_type = 39。A39。 。 no rows selected Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 39。EMP39。2 1 INDEX (RANGE SCAN) OF 39。EMPTYPE39。 (NONUNIQUE) 雖然EMPNO是唯一性索引,但是由于它所做的是范圍比較, 等級要比非唯一性索引的等式比較低!ORACLE SQL性能優(yōu)化系列 (十) 31. 強(qiáng)制索引失效 如果兩個或以上索引具有相同的等級,你可以強(qiáng)制命令ORACLE優(yōu)化器使用其中的一個(通過它,檢索出的記錄數(shù)量少) . 舉例: SELECT ENAMEFROM EMPWHERE EMPNO = 7935AND DEPTNO + 0 = 10 /*DEPTNO上的索引將失效*/AND EMP_TYPE || ‘’ = ‘A’ /*EMP_TYPE上的索引將失效*/ 這是一種相當(dāng)直接的提高查詢效率的辦法. 但是你必須謹(jǐn)慎考慮這種策略,一般來說,只有在你希望單獨優(yōu)化幾個SQL時才能采用它. 這里有一個例子關(guān)于何時采用這種策略, 假設(shè)在EMP表的EMP_TYPE列上有一個非唯一性的索引而EMP_CLASS上沒有索引. SELECT ENAMEFROM EMPWHERE EMP_TYPE = ‘A’AND EMP_CLASS = ‘X’。 優(yōu)化器會注意到EMP_TYPE上的索引并使用它. 這是目前唯一的選擇. 如果,一段時間以后, 另一個非唯一性建立在EMP_CLASS上,優(yōu)化器必須對兩個索引進(jìn)行選擇,在通常情況下,優(yōu)化器將使用兩個索引并在他們的結(jié)果集合上執(zhí)行排序及合并. 然而,如果其中一個索引（EMP_TYPE）接近于唯一性而另一個索引（EMP_CLASS）上有幾千個重復(fù)的值. 排序及合并就會成為一種不必要的負(fù)擔(dān). 在這種情況下,你希望使優(yōu)化器屏蔽掉EMP_CLASS索引.用下面的方案就可以解決問題.SELECT ENAMEFROM EMPWHERE EMP_TYPE = ‘A’AND EMP_CLASS||’’ = ‘X’。 32. 避免在索引列上使用計算．WHERE子句中，如果索引列是函數(shù)的一部分．優(yōu)化器將不使用索引而使用全表掃描． 舉例: 低效：SELECT …FROM DEPTWHERE SAL * 12 25000。 高效:SELECT …FROM DEPTWHERE SAL 25000/12。 譯者按:這是一個非常實用的規(guī)則，請務(wù)必牢記 33. 自動選擇索引如果表中有兩個以上（包括兩個）索引，其中有一個唯一性索引，而其他是非唯一性．在這種情況下，ORACLE將使用唯一性索引而完全忽略非唯一性索引． 舉例:SELECT ENAMEFROM EMPWHERE EMPNO = 2326AND DEPTNO = 20 。 這里，只有EMPNO上的索引是唯一性的，所以EMPNO索引將用來檢索記錄．TABLE ACCESS BY ROWID ON EMPINDEX UNIQUE SCAN ON EMP_NO_IDX 34. 避免在索引列上使用NOT通常，　我們要避免在索引列上使用NOT, NOT會產(chǎn)生在和在索引列上使用函數(shù)相同的影響. 當(dāng)ORACLE”遇到”NOT,他就會停止使用索引轉(zhuǎn)而執(zhí)行全表掃描.舉例: 低效: (這里,不使用索引) SELECT …FROM DEPTWHERE DEPT_CODE NOT = 0。 高效: (這里,使用了索引) SELECT …FROM DEPTWHERE DEPT_CODE 0。 需要注意的是,在某些時候, ORACLE優(yōu)化器會自動將NOT轉(zhuǎn)化成相對應(yīng)的關(guān)系操作符.NOT to =NOT = to NOT to =NOT = to 譯者按:在這個例子中,作者犯了一些錯誤. 例子中的低效率SQL是不能被執(zhí)行的.我做了一些測試: SQL select * from emp where NOT empno 1。no rows selectedExecution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 39。EMP39。2 1 INDEX (RANGE SCAN) OF 39。EMPNO39。 (UNIQUE) SQL select * from emp where empno = 1。no rows selectedExecution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 39。EMP39。2 1 INDEX (RANGE SCAN) OF 39。EMPNO39。 (UNIQUE) 兩者的效率完全一樣，也許這符合作者關(guān)于” 在某些時候, ORACLE優(yōu)化器會自動將NOT轉(zhuǎn)化成相對應(yīng)的關(guān)系操作符” 的觀點． 35. 用=替代 如果DEPTNO上有一個索引, 高效: SELECT *FROM EMPWHERE DEPTNO =4 低效: SELECT *FROM EMPWHERE DEPTNO 3 兩者的區(qū)別在于, 前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄.ORACLE SQL性能優(yōu)化系列 (十一) 36. 用UNION替換OR (適用于索引列)通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果. 對索引列使用OR將造成全表掃描. 注意, 以上規(guī)則只針對多個索引列有效. 如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低.在下面的例子中, LOC_ID 和REGION上都建有索引.高效:SELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE LOC_ID = 10UNIONSELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE REGION = “MELBOURNE” 低效:SELECT LOC_ID , LOC_DESC , REGIONFROM LOCATIONWHERE LOC_ID = 10 OR REGION = “MELBOURNE” 如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面. 注意: WHERE KEY1 = 10 (返回最少記錄)OR KEY2 = 20 (返回最多記錄) ORACLE 內(nèi)部將以上轉(zhuǎn)換為WHERE KEY1 = 10 AND((NOT KEY1 = 10) AND KEY2 = 20) 譯者按: 下面的測試數(shù)據(jù)僅供參考: (a = 1003 返回一條記錄 , b = 1 返回1003條記錄)SQL select * from unionvsor /*1st test*/