Oracle語句優化規則匯總(9)

使用提示(Hints)

　　對於表的訪問可以使用兩種HintsFULL 和 ROWID

　　FULL hint 告訴ORACLE使用全表掃描的方式訪問指定表

　　例如

　　 　　SELECT /*+ FULL(EMP) */ *
　　FROM EMP
　　WHERE EMPNO = ;

　　ROWID hint 告訴ORACLE使用TABLE ACCESS BY ROWID的操作訪問表

　　通常 你需要采用TABLE ACCESS BY ROWID的方式特別是當訪問大表的時候 使用這種方式 你需要知道ROIWD的值或者使用索引

　　如果一個大表沒有被設定為緩存(CACHED)表而你希望它的數據在查詢結束是仍然停留在SGA中你就可以使用CACHE hint 來告訴優化器把數據保留在SGA中 通常CACHE hint 和 FULL hint 一起使用

　　例如

　　 　　SELECT /*+ FULL(WORKER) CACHE(WORKER)*/ *
　　FROM WORK;

　　索引hint 告訴ORACLE使用基於索引的掃描方式 你不必說明具體的索引名稱

　　例如

　　 　　SELECT /*+ INDEX(LODGING) */ LODGING
　　FROM LODGING
　　WHERE MANAGER = BILL GATES;

　　在不使用hint的情況下 以上的查詢應該也會使用索引然而如果該索引的重復值過多而你的優化器是CBO 優化器就可能忽略索引 在這種情況下 你可以用INDEX hint強制ORACLE使用該索引

　　ORACLE hints 還包括ALL_ROWS FIRST_ROWS RULEUSE_NL USE_MERGE USE_HASH 等等

　　使用hint 表示我們對ORACLE優化器缺省的執行路徑不滿意需要手工修改這是一個很有技巧性的工作 我建議只針對特定的少數的SQL進行hint的優化對ORACLE的優化器還是要有信心(特別是CBO)

　　 用WHERE替代ORDER BY

　　ORDER BY 子句只在兩種嚴格的條件下使用索引
　　ORDER BY中所有的列必須包含在相同的索引中並保持在索引中的排列順序
　　ORDER BY中所有的列必須定義為非空
　　WHERE子句使用的索引和ORDER BY子句中所使用的索引不能並列

　　例如

　　表DEPT包含以下列

　　 　　DEPT_CODE PK NOT NULL
　　DEPT_DESC NOT NULL
　　DEPT_TYPE NULL

　　非唯一性的索引(DEPT_TYPE)

　　低效 (索引不被使用)

　　 　　SELECT DEPT_CODE
　　FROM DEPT
　　ORDER BY DEPT_TYPE
　　EXPLAIN PLAN
　　SORT ORDER BY
　　TABLE ACCESS FULL

　　高效 (使用索引)

　　 　　SELECT DEPT_CODE
　　FROM DEPT
　　WHERE DEPT_TYPE > 
　　EXPLAIN PLAN
　　TABLE ACCESS BY ROWID ON EMP
　　INDEX RANGE SCAN ON DEPT_IDX

　　ORDER BY 也能使用索引! 這的確是個容易被忽視的知識點 我們來驗證一下

　　 SQL> select * from emp order by empno
Execution Plan

          SELECT STATEMENT Optimizer=CHOOSE
　       TABLE ACCESS （BY INDEX ROWID） OF EMP
　         INDEX （FULL SCAN） OF EMPNO （UNIQUE）

　　 避免改變索引列的類型

　　當比較不同數據類型的數據時 ORACLE自動對列進行簡單的類型轉換

　　假設 EMPNO是一個數值類型的索引列

　　 　　SELECT …
　　FROM EMP
　　WHERE EMPNO = 

　　實際上經過ORACLE類型轉換 語句轉化為

　　 　　SELECT …
　　FROM EMP
　　WHERE EMPNO = TO_NUMBER()

　　幸運的是類型轉換沒有發生在索引列上索引的用途沒有被改變

　　現在假設EMP_TYPE是一個字符類型的索引列

　　 　　SELECT …
　　FROM EMP
　　WHERE EMP_TYPE = 

　　這個語句被ORACLE轉換為

　　 　　SELECT …
　　FROM EMP
　　WHERE TO_NUMBER(EMP_TYPE)=

　　因為內部發生的類型轉換 這個索引將不會被用到!

　　為了避免ORACLE對你的SQL進行隱式的類型轉換 最好把類型轉換用顯式表現出來 注意當字符和數值比較時 ORACLE會優先轉換數值類型到字符類型

From:http://tw.wingwit.com/Article/program/Oracle/201311/16677.html