Oracle 常用HINT介紹

Oracle的最佳化器有兩種最佳化方式：

基於規則的最佳化方式：Rule-Based Optimization（RBO）

基於成本或者統計資訊的最佳化方式(Cost-Based Optimization：CBO)

RBO方式：最佳化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。

CBO方式：CBO是在ORACLE7 引入，但到ORACLE8i 中才成熟。ORACLE 已經宣告在ORACLE9i之後的版本中，RBO將不再支援。它是看語句的代價(Cost),這裡的代價主要指Cpu和記憶體。CPU Costing的計算方式現在預設為CPU+I/O兩者之和.可透過DBMS_XPLAN.DISPLAY_CURSOR觀察更為詳細的執行計劃。最佳化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。

[@more@]

在SQL語句最佳化過程中，我們經常會用到hint,現總結一下在SQL最佳化過程中常見Oracle HINT的用法：

1. /*+ALL_ROWS*/

表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳吞吐量,使資源消耗最小化.

例如：SELECT /*+ALL_ROWS*/YSID FROM TANGYUNORACLE WHERE YSID='TANGYUN';

2. /*+FIRST_ROWS*/

表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳響應時間,使資源消耗最小化.

SELECT /*+FIRST_ROWS(20)*/YSID FROM TANGYUNORACLE WHERE YSID='TANGYUN';

3. /*+CHOOSE*/

表明如果資料字典中有訪問表的統計資訊,將基於開銷的最佳化方法,並獲得最佳的吞吐量;

表明如果資料字典中沒有訪問表的統計資訊,將基於規則開銷的最佳化方法;

例如：SELECT /*+CHOOSE*/YSID FROM TANGYUNORACLE WHERE YSID='TANGYUN';

4. /*+RULE*/

表明對語句塊選擇基於規則的最佳化方法.

例如：SELECT /*+RULE*/YSID FROM TANGYUNORACLE WHERE YSID='TANGYUN';

5. /*+FULL(TABLE)*/

表明對錶選擇全域性掃描的方法.

例如：SELECT /*+RULE(A)*/YSID FROM TANGYUNORACLE A WHERE YSID='TANGYUN';

6. /*+ROWID(TABLE)*/

提示明確表明對指定表根據ROWID進行訪問.

例如：SELECT /*+ROWID(A)*/ * FROM TANGYUNORACLE A WHERE YSID='TANGYUN' AND ROWID>=CHARTOROWID('AAAhq3AA7AAAAGeAAB');7. /*+CLUSTER(TABLE)*/

提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇物件有效.

8. /*+INDEX(TABLE INDEX_NAME)*/

表明對錶選擇索引的掃描方法.

例如：SELECT /*+INDEX(A IDX_TANGYUN)*/ * FROM TANGYUNORACLE A WHERE YSID='TANGYUN';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/

表明對錶選擇索引升序的掃描方法.

例如：SELECT /*+INDEX_ASC(A IDX_TANGYUN)*/ * FROM TANGYUNORACLE A WHERE YSID='TANGYUN';

10. /*+INDEX_DESC(TABLE INDEX_NAME)*/

表明對錶選擇索引降序的掃描方法.

例如：SELECT /*+INDEX_DESC(A IDX_TANGYUN)*/ * FROM TANGYUNORACLE A WHERE YSID='TANGYUN';

11. /*+INDEX_FFS(TABLE INDEX_NAME)*/

對指定的表執行快速全索引掃描,而不是全表掃描的辦法.

例如：SELECT /*+INDEX_FFS(A IDX_TANGYUN)*/ * FROM TANGYUNORACLE A WHERE YSID='TANGYUN';

12. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/

提示明確進行執行計劃的選擇,將幾個單列索引的掃描合起來.

例如：SELECT /*+INDEX_ASC(A IDX_YSID,IDX_YWID)*/ * FROM TANGYUNORACLE A WHERE YSID='TANGYUN' AND YWID='TANGYUN';

13. /*+USE_CONCAT*/

對查詢中的WHERE後面的OR條件進行轉換為UNION ALL的組合查詢.

例如:

例如：SELECT /*+ USE_CONCAT */ * FROM TANGYUNORACLE A WHERE YSID='TANGYUN' OR YWID='TANGYUN';

14. /*+NOWRITE*/

禁止對查詢塊的查詢重寫操作.

15. /*+REWRITE*/

可以將檢視作為引數.

16. /*+MERGE(TABLE)*/

能夠對檢視的各個查詢進行相應的合併.

例如：SELECT /*+MERGE(C) */ A.YSID,A.YWID,B.YSID FROM TANGYUNORACLE A (SELET YSID,YWID FROM TANGYUNORACLE B GROUP BY YSID) C WHERE A.YSID=C.YSID

AND A.YWID>C.YWID;

17. /*+NO_MERGE(TABLE)*/

對於有可合併的檢視不再合併.

例如：SELECT /*+NO_MERGE(C) */ A.YSID,A.YWID,B.YSID FROM TANGYUNORACLE A (SELET YSID,YWID FROM TANGYUNORACLE B GROUP BY YSID) C WHERE A.YSID=C.YSID

AND A.YWID>C.YWID;

18. /*+ORDERED*/

根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連線.

例如:

SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

19. /*+USE_NL(TABLE)*/

將指定表與巢狀的連線的行源進行連線,並把指定表作為內部表.

20. /*+USE_MERGE(TABLE)*/

將指定的表與其他行源透過合併排序連線方式連線起來.

21. /*+USE_HASH(TABLE)*/

將指定的表與其他行源透過雜湊連線方式連線起來.

22. /*+LEADING(TABLE)*/

將指定的表作為連線次序中的首表.

23. /*+CACHE(TABLE)*/

當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區快取中最近最少列表LRU的最近使用端

24. /*+NOCACHE(TABLE)*/

當進行全表掃描時,CACHE提示不能夠將表的檢索塊放置在緩衝區快取中最近最少列表LRU的最近使用端

25. /*+APPEND*/

直接插入到表的最後,可以提高速度.

例如：insert /*+APPEND*/into tangyun_test select * from tangyunoracle;

26. /*+NOAPPEND*/

透過在插入語句生存期內停止並行模式來啟動常規插入.

例如：insert /*+NOAPPEND*/into tangyun_test select * from tangyunoracle;

27./*+INDEX(TABLE_NAME (TABLE_COLUMN))*/

oracle10以上版本還增加了可以直接使用列的索引方式。

例如：SELECT /*+ INDEX(A (YSID))*/ * FROM TANGYUNORACLE A WHERE YSID='TANGYUN' ;

---------------------------------End-------------------------------------------------