【正文】 KER) CACHE(WORKER)*/ *FROM WORK。 索引hint 告訴ORACLE使用基于索引的掃描方式. 你不必說明具體的索引名稱例如:SELECT /*+ INDEX(LODGING) */ LODGINGFROM LODGINGWHERE MANAGER = ‘BILL GATES’。 在不使用hint的情況下, 以上的查詢應該也會使用索引,然而,如果該索引的重復值過多而你的優(yōu)化器是CBO, 優(yōu)化器就可能忽略索引. 在這種情況下, 你可以用INDEX hint強制ORACLE使用該索引. ORACLE hints 還包括ALL_ROWS, FIRST_ROWS, RULE,USE_NL, USE_MERGE, USE_HASH 等等. 譯者按:使用hint , 表示我們對ORACLE優(yōu)化器缺省的執(zhí)行路徑不滿意,需要手工修改.這是一個很有技巧性的工作. 我建議只針對特定的,少數(shù)的SQL進行hint的優(yōu)化.對ORACLE的優(yōu)化器還是要有信心(特別是CBO)ORACLE SQL性能優(yōu)化系列 (十三) 43. 用WHERE替代ORDER BYORDER BY 子句只在兩種嚴格的條件下使用索引. ORDER BY中所有的列必須包含在相同的索引中并保持在索引中的排列順序.ORDER BY中所有的列必須定義為非空. WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列. 例如:表DEPT包含以下列: DEPT_CODE PK NOT NULLDEPT_DESC NOT NULLDEPT_TYPE NULL 非唯一性的索引(DEPT_TYPE) 低效: (索引不被使用)SELECT DEPT_CODEFROM DEPTORDER BY DEPT_TYPE EXPLAIN PLAN:SORT ORDER BYTABLE ACCESS FULL 高效: (使用索引) SELECT DEPT_CODEFROM DEPTWHERE DEPT_TYPE 0 EXPLAIN PLAN:TABLE ACCESS BY ROWID ON EMPINDEX RANGE SCAN ON DEPT_IDX譯者按:ORDER BY 也能使用索引! 這的確是個容易被忽視的知識點. 我們來驗證一下:SQL select * from emp order by empno。Execution Plan0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 39。EMP39。2 1 INDEX (FULL SCAN) OF 39。EMPNO39。 (UNIQUE) 44. 避免改變索引列的類型.當比較不同數(shù)據(jù)類型的數(shù)據(jù)時, ORACLE自動對列進行簡單的類型轉(zhuǎn)換. 假設 EMPNO是一個數(shù)值類型的索引列. SELECT …FROM EMPWHERE EMPNO = ‘123’ 實際上,經(jīng)過ORACLE類型轉(zhuǎn)換, 語句轉(zhuǎn)化為:SELECT …FROM EMPWHERE EMPNO = TO_NUMBER(‘123’) 幸運的是,類型轉(zhuǎn)換沒有發(fā)生在索引列上,索引的用途沒有被改變. 現(xiàn)在,假設EMP_TYPE是一個字符類型的索引列.SELECT …FROM EMPWHERE EMP_TYPE = 123 這個語句被ORACLE轉(zhuǎn)換為:SELECT …FROM EMPWHERE TO_NUMBER(EMP_TYPE)=123 因為內(nèi)部發(fā)生的類型轉(zhuǎn)換, 這個索引將不會被用到!譯者按:為了避免ORACLE對你的SQL進行隱式的類型轉(zhuǎn)換, 最好把類型轉(zhuǎn)換用顯式表現(xiàn)出來. 注意當字符和數(shù)值比較時, ORACLE會優(yōu)先轉(zhuǎn)換數(shù)值類型到字符類型. 45. 需要當心的WHERE子句某些SELECT 語句中的WHERE子句不使用索引. 這里有一些例子.在下面的例子里, ‘!=’ 將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中.不使用索引:SELECT ACCOUNT_NAMEFROM TRANSACTIONWHERE AMOUNT !=0。使用索引:SELECT ACCOUNT_NAMEFROM TRANSACTIONWHERE AMOUNT 0。 下面的例子中, ‘||’是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引.不使用索引:SELECT ACCOUNT_NAME,AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’。使用索引:SELECT ACCOUNT_NAME,AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME = ‘AMEX’AND ACCOUNT_TYPE=’ A’。 下面的例子中, ‘+’是數(shù)學函數(shù). 就象其他數(shù)學函數(shù)那樣, 停用了索引.不使用索引:SELECT ACCOUNT_NAME, AMOUNTFROM TRANSACTIONWHERE AMOUNT + 3000 5000。使用索引:SELECT ACCOUNT_NAME, AMOUNTFROM TRANSACTIONWHERE AMOUNT 2000 。下面的例子中,相同的索引列不能互相比較,這將會啟用全表掃描.不使用索引:SELECT ACCOUNT_NAME, AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME)。使用索引:SELECT ACCOUNT_NAME, AMOUNTFROM TRANSACTIONWHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’)。 譯者按:如果一定要對使用函數(shù)的列啟用索引, ORACLE新的功能: 基于函數(shù)的索引(FunctionBased Index) 也許是一個較好的方案.CREATE INDEX EMP_I ON EMP (UPPER(ename))。 /*建立基于函數(shù)的索引*/SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’。 /*將使用索引*/ ORACLE SQL性能優(yōu)化系列 (十四) 完結(jié)篇46. 連接多個掃描如果你對一個列和一組有限的值進行比較, 優(yōu)化器可能執(zhí)行多次掃描并對結(jié)果進行合并連接.舉例:SELECT *FROM LODGINGWHERE MANAGER IN (‘BILL GATES’,’KEN MULLER’)。 優(yōu)化器可能將它轉(zhuǎn)換成以下形式SELECT *FROM LODGINGWHERE MANAGER = ‘BILL GATES’OR MANAGER = ’KEN MULLER’。 當選擇執(zhí)行路徑時, 優(yōu)化器可能對每個條件采用LODGING$MANAGER上的索引范圍掃描. 返回的ROWID用來訪問LODGING表的記錄 (通過TABLE ACCESS BY ROWID 的方式). 最后兩組記錄以連接(CONCATENATION)的形式被組合成一個單一的集合. Explain Plan : SELECT STATEMENT Optimizer=CHOOSECONCATENATIONTABLE ACCESS (BY INDEX ROWID) OF LODGINGINDEX (RANGE SCAN ) OF LODGING$MANAGER (NONUNIQUE)TABLE ACCESS (BY INDEX ROWID) OF LODGINGINDEX (RANGE SCAN ) OF LODGING$MANAGER (NONUNIQUE)譯者按:本節(jié)和第37節(jié)似乎有矛盾之處.47. CBO下使用更具選擇性的索引基于成本的優(yōu)化器(CBO, CostBased Optimizer)對索引的選擇性進行判斷來決定索引的使用是否能提高效率.如果索引有很高的選擇性, 那就是說對于每個不重復的索引鍵值,只對應數(shù)量很少的記錄.比如, 表中共有100條記錄而其中有80個不重復的索引鍵值. 這個索引的選擇性就是80/100 = . 選擇性越高, 通過索引鍵值檢索出的記錄就越少.如果索引的選擇性很低, 檢索數(shù)據(jù)就需要大量的索引范圍查詢操作和ROWID 訪問表的操作. 也許會比全表掃描的效率更低.譯者按:下列經(jīng)驗請參閱:a. 如果檢索數(shù)據(jù)量超過30%.b. 在特定情況下, 使用索引也許會比全表掃描慢, 但這是同一個數(shù)量級上的區(qū)別. 而通常情況下,使用索引比全表掃描要塊幾倍乃至幾千倍!48. 避免使用耗費資源的操作帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎執(zhí)行耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執(zhí)行兩次排序.例如,一個UNION查詢,其中每個查詢都帶有GROUP BY子句, GROUP BY會觸發(fā)嵌入排序(NESTED SORT) 。 這樣, 每個查詢需要執(zhí)行一次排序, 然后在執(zhí)行UNION時, 又一個唯一排序(SORT UNIQUE)操作被執(zhí)行而且它只能在前面的嵌入排序結(jié)束后才能開始執(zhí)行. 嵌入的排序的深度會大大影響查詢的效率.通常, 帶有UNION, MINUS , INTERSECT的SQL語句都可以用其他方式重寫.譯者按:如果你的數(shù)據(jù)庫的SORT_AREA_SIZE調(diào)配得好, 使用UNION , MINUS, INTERSECT也是可以考慮的, 畢竟它們的可讀性很強 49. 優(yōu)化GROUP BY提高GROUP BY 語句的效率, 可以通過將不需要的記錄在GROUP BY . 低效:SELECT JOB , AVG(SAL)FROM EMPGROUP JOBHAVING JOB = ‘PRESIDENT’OR JOB = ‘MANAGER’高效:SELECT JOB , AVG(SAL)FROM EMPWHERE JOB = ‘PRESIDENT’OR JOB = ‘MANAGER’GROUP JOB 譯者按:本節(jié)和14節(jié)相同. 可略過. 50. 使用日期當使用日期是,需要注意如果有超過5位小數(shù)加到日期上, 這個日期會進到下一天! 例如:1.SELECT TO_DATE(‘01JAN93’+.99999)FROM DUAL。 Returns:’01JAN93 23:59:59’2.SELECT TO_DATE(‘01JAN93’+.999999)FROM DUAL。 Returns:’02JAN93 00:00:00’ 譯者按:雖然本節(jié)和SQL性能優(yōu)化沒有關(guān)系, 但是作者的功力可見一斑 51. 使用顯式的游標(CURSORs)使用隱式的游標,將會執(zhí)行兩次操作. 第一次檢索記錄, 第二次檢查TOO MANY ROWS 這個exception . 而顯式游標不執(zhí)行第二次操作.52. 優(yōu)化EXPORT和IMPORT使用較大的BUFFER(比如10MB , 10,240,000)可以提高EXPORT和IMPORT的速度.ORACLE將盡可能地獲取你所指定的內(nèi)存大小,即使在內(nèi)存不滿足,否則列值會被截斷.譯者按:可以肯定的是, 增加BUFFER會大大提高EXPORT , IMPORT的效率. (曾經(jīng)碰到過一個CASE, 增加BUFFER后,IMPORT/EXPORT快了10倍!)作者可能犯了一個錯誤: “這個值至少要和表中最大的列相當,否則列值會被截斷. “其中最大的列也許是指最大的記錄大小.關(guān)于EXPORT/IMPORT的優(yōu)化,CSDN論壇中有一些總結(jié)性的貼子,比如關(guān)于BUFFER參數(shù), COMMIT參數(shù)等等, 詳情請查. 53. 分離表和索引總是將你的表和索引建立在不同的表空間內(nèi)(TABLESPACES). 決不要將不屬于ORACLE內(nèi)部系統(tǒng)的對象存放到SYSTEM表空間里. 同時,確保數(shù)據(jù)表空間和索引表空間置于不同的硬盤上.譯者按:“同時,確保數(shù)據(jù)表空間和索引表空間置與不同的硬盤上.”可能改為如下更為準確 “同時,確保數(shù)據(jù)表空間和索引表空間置與不同的硬盤控制卡控制的硬盤上.”47 /