關於Oracle10g常見HINT的用法

Oracle10g關於HINT的用法很多，下面小編為大家整理了Oracle10g常見HINT的用法，希望能為你提供幫助：

語法:

提示裡不區分大小寫, 多個提示用空格分開, 如：select /*+ hint1(tab1) hint2(TAB1 idx1) */ col1, col2 from tab1 where col1='xxx';

如果表使用了別名, 那麼提示裡也必須使用別名, 如：select /*+ hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';

在SQL優化過程中常見HINT的用法(前10個比較常用, 前3個最常用)：

1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(1 2) */ 和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */

表明對錶選擇索引的掃描方法. 第一種不指定索引名是讓Oracle對錶中可用索引比較並選擇某個最佳索引; 第二種是指定索引名且可指定多個索引; 第三種是10g開始有的, 指定列名, 且表名可不用別名; 第四種即全表掃描; 第五種表示禁用某個索引, 特別適合於準備刪除某個索引前的評估操作. 如果同時使用了INDEX和NO_INDEX則兩個提示都會被忽略掉.

例如:SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

2. /*+ ORDERED */

FROM子句中預設最後一個表是驅動表,ORDERED將from子句中第一個表作為驅動表. 特別適合於多表連線非常慢時嘗試.

例如:SELECT /*+ ORDERED */ 1,2,3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE 1=1 AND 1=1;

3. /*+ PARALLEL(table1,DEGREE) */ 和 /*+ NO_PARALLEL(table1) */

該提示會將需要執行全表掃描的查詢分成多個部分(並行度)執行, 然後在不同的作業系統程序中處理每個部分. 該提示還可用於DML語句. 如果SQL裡還有排序操作, 程序數會翻倍,此外還有一個一個負責組合這些部分的程序,如下面的例子會產生9個程序. 如果在提示中沒有指定DEGREE, 那麼就會使用建立表時的預設值. 該提示在預設情況下會使用APPEND提示. NO_PARALLEL是禁止並行操作,否則語句會使用由於定義了並行物件而產生的並行處理.

例如:select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;

4. /*+ FIRST_ROWS */ 和 /*+ FIRST_ROWS(n) */

表示用最快速度獲得第1/n行, 獲得最佳響應時間, 使資源消耗最小化.

在update和delete語句裡會被忽略, 使用分組語句如group by/distinct/intersect/minus/union時也會被忽略.

例如:SELECT /*+ FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+ RULE */

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

例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

6. /*+ FULL(TABLE) */

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

例如:SELECT /*+ FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

7. /*+ LEADING(TABLE) */

類似於ORDERED提示, 將指定的.表作為連線次序中的驅動表.

8. /*+ USE_NL(TABLE1,TABLE2) */

將指定表與巢狀的連線的行源進行連線,以最快速度返回第一行再連線,與USE_MERGE剛好相反.

例如:SELECT /*+ ORDERED USE_NL(BSEMPMS) */ _NO,_NO,_NAM FROM BSEMPMS,BSDPTMS WHERE _NO=_NO;

9. /*+ APPEND */ 和 /*+ NOAPPEND */

直接插入到表的最後,該提示不會檢查當前是否有插入操作所需的塊空間而是直接新增到新塊中, 所以可以提高速度. 當然也會浪費些空間, 因為它不會使用那些做了delete操作的塊空間. NOAPPEND提示則相反,所以會取消PARALLEL提示的預設APPEND提示.

例如:insert /*+ append */ into test1 select * from test4;

insert /*+ parallel(test1) noappend */ into test1 select * from test4;

10. /*+ USE_HASH(TABLE1,table2) */

將指定的表與其它行源通過雜湊連線方式連線起來.為較大的結果集提供最佳響應時間. 類似於在連線表的結果中遍歷每個表上每個結果的巢狀迴圈, 指定的hash表將被放入記憶體, 所以需要有足夠的記憶體(hash_area_size或pga_aggregate_target)才能保證語句正確執行, 否則將在磁盤裡進行.

例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE _NO=_NO;

----------------------------------------------------------------------------------------------------

11. /*+ USE_MERGE(TABLE) */

將指定的表與其它行源通過合併排序連線方式連線起來.特別適合於那種在多個表大量行上進行集合操作的查詢, 它會將指定表檢索到的的所有行排序後再被合併, 與USE_NL剛好相反.

例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE _NO=_NO;

12. /*+ ALL_ROWS */

表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化. 可能會限制某些索引的使用.

例如:SELECT /*+ ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

13. /*+ CLUSTER(TABLE) */

提示明確表明對指定表選擇簇掃描的訪問方法. 如果經常訪問連線表但很少修改它, 那就使用叢集提示.

例如:SELECT /*+ CLUSTER */ _NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND _NO=_NO;

14. /*+ INDEX_ASC(TABLE INDEX1, INDEX2) */

表明對錶選擇索引升序的掃描方法. 從8i開始, 這個提示和INDEX提示功能一樣, 因為預設oracle就是按照升序掃描索引的, 除非未來oracle還退出降序掃描索引.

例如:SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

15. /*+ INDEX_COMBINE(TABLE INDEX1, INDEX2) */

指定多個位圖索引, 對於B樹索引則使用INDEX這個提示,如果INDEX_COMBINE中沒有提供作為引數的索引,將選擇出點陣圖索引的布林組合方式.

例如:SELECT /*+ INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE

16. /*+ INDEX_JOIN(TABLE INDEX1, INDEX2) */

合併索引, 所有資料都已經包含在這兩個索引裡, 不會再去訪問表, 比使用索引並通過rowid去掃描表要快5倍.

例如:SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;

17. /*+ INDEX_DESC(TABLE INDEX1, INDEX2) */

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

例如:SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

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

對指定的表執行快速全索引掃描,而不是全表掃描的辦法.要求要檢索的列都在索引裡, 如果表有很多列時特別適用該提示.

例如:SELECT /*+ INDEX_FFS(BSEMPMS IN_EMPNAM) */ * FROM BSEMPMS WHERE DPT_NO='TEC305';

19. /*+ NO_EXPAND */

對於WHERE後面的OR 或者IN-LIST的查詢語句,NO_EXPAND將阻止其基於優化器對其進行擴充套件, 縮短解析時間.

例如:SELECT /*+ NO_EXPAND */ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

20. /*+ DRIVING_SITE(TABLE) */

強制與ORACLE所選擇的位置不同的表進行查詢執行.特別適用於通過dblink連線的遠端表.

例如:SELECT /*+ DRIVING_SITE(DEPT) */ * FROM BSEMPMS,DEPT@BSDPTMS DEPT WHERE _NO=_NO;

21. /*+ CACHE(TABLE) */ 和 /*+ NOCACHE(TABLE) */

當進行全表掃描時,CACHE提示能夠將表全部快取到記憶體中,這樣訪問同一個表的使用者可直接在記憶體中查詢資料. 比較適合資料量小但常被訪問的表, 也可以建表時指定cache選項這樣在第一次訪問時就可以對其快取. NOCACHE則表示對已經指定了CACHE選項的表不進行快取.

例如:SELECT /*+ FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

22. /*+ PUSH_SUBQ */

當SQL裡用到了子查詢且返回相對少的行時, 該提示可以儘可能早對子查詢進行評估從而改善效能, 不適用於合併連線或帶遠端表的連線.

例如:select /*+ PUSH_SUBQ */ o, e, itemno from emp, orders where o = o and no = (select deptno from dept where loc='XXX');

23. /*+ INDEX_SS(TABLE INDEX1,INDEX2) */

指示對特定表的索引使用跳躍掃描, 即當組合索引的第一列不在where子句中時, 讓其使用該索引.