優化器提示

eric0435發表於2012-10-14
優化
提示（Hint）概念
一般在優化時，無論採用基於規則的或是基於代價的方法，由Oracle 系統的優化器來決定語
句的執行路徑。這樣的選擇的路徑不要見得是最好的。所以，Oracle 提供了一種方法叫提示的
方法。它可以讓程式設計人員按照自己的要求來選擇執行路徑，即提示優化器該按照什麼樣的執
行規則來執行當前的語句。這樣可以在效能上比起Oracle 優化自主決定要好些。
通常情況下，程式設計人員可以利用提示來進行優化決策。通過運用提示可以對下面內容進行指
定：
 SQL 語句的優化方法；
 對於某條SQL 語句，基於開銷優化程式的目標；
 SQL 語句訪問的訪問路徑；
 連線語句的連線次序；
 連線語句中的連線操作

提示的指定
如果希望優化器按照程式設計人員的要求執行，則要在語句中給出提示。提示的有效範圍有
限制，即有提示的語句塊才能按照提示要求執行。下面語句可以指定提示：
 簡單的SELECT ,UPDATE ,DELETE 語句；
 複合的主語句或子查詢語句；
 組成查詢（UNION）的一部分。
提示的指定有原來的註釋語句在加“+”構成。語法如下：
[ SELECT | DELETE|UPDATE ] /*+ [hint | text ] */
或
[ SELECT | DELETE|UPDATE ] --+ [hint | text ]
注意在“/*”後不要空就直接加“+”，同樣 “--+”也是連著寫。
警告：如果該提示語句書寫不正確，則 Oracle 就忽略掉該語句。

指定完整的提示：
對於複雜的語句，要用/*+ */來指定，它可以指定多個提示語句。且可以換行。
SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)
USE_NL (glcc glf) USE_MERGE (gp gsb) */
b.application_id ,
b.set_of_books_id ,
b.personnel_id,
p.vendor_id Personnel,
p.segment1 PersonnelNumber,
p.vendor_name Name
FROM jl_br_journals j,
jl_br_balances b,
gl_code_combinations glcc,
fnd_flex_values_vl glf,
gl_periods gp,
gl_sets_of_books gsb,
po_vendors p
WHERE . . . . . . . . . . . .

提示的指定
使用提示，使用者可以在基於開銷的優化方法和基於規則的優化方法之間選擇，由此可以
對要求“最佳吞吐量”與“最佳響應時間”優化目標進行選擇。優化方法如下：
 ALL_ROWS
 FIRST_ROWS
 CHOOSE
 RULE
如果某條語句已經指定了優化方法和優化目標後，則Oracle 的優化器就按照指定的優化方法
和目標進行執行。並且不考慮：
１） 是否存在統計資訊；
２） 初始化引數OPTIMIZER_MODE 的取值；
３） ALTER SESSION 語句中OPTIMIZER_MODE 引數值。

ALL_ROWS
ALL_ROWS 表示對語句塊選擇基於開銷的優化方法，並且獲得最佳的吞吐量（資源消耗
總量最小）作為目標進行優化。語法如下：
/*+ ALL_ROWS */
例子：在查詢EMP 表希望用基於開銷的優化方法，並獲得最佳吞吐量，則使用下面語句：
SELECT /*+ ALL_ROWS */empno,ename,sal,job
FROM emp WHERE empno=7566;

FIRST_ROWS
FIRST_ROWS 表示對語句塊選擇基於開銷的優化方法，並且獲得最佳的響應（返回首行
的資源最小化）作為目標進行優化。
使用FIRST_ROWS 優化方法，優化器可能要進行下面工作：
 如果能利用索引掃描，則不進行全表掃描；
 當關聯表是巢狀迴圈的內部表且能用索引掃描，則優化器先優化巢狀迴圈聯結。
 如果通過order by 使得索引掃描可用，則優化器選擇索引掃描以避免排序操作。
語法如下：
/*+ FIRST_ROWS */
例子：選擇基於開銷的優化方法，並希望獲得最佳的響應時間，則：
SELECT /*+ FIRST_ROWS */empno,ename,sal,job
FROM emp
WHERE empno=7566;

CHOOSE
選擇CHOOSE 表示告訴優化器要在基於開銷和基於規則之間進行選擇。優化器的確定要
建立在是否存在訪問表的統計資訊之上：
 如果資料字典中存在該表的統計資料，則選擇基於開銷，並以最佳吞吐量作為目標。
 如果資料字典中不存在該表的統計資料，則選擇基於規則。
語法為：
/*+ CHOOSE */
例子：
SELECT /*+ CHOOSE */ empno,sal,job FROM emp WHERE empno=7566;

RULE
表示要求優化器對語句塊選擇基於規則的優化方法。語法如下：
/*+ RULE */
例子：
SELECT --+ RULE empno,ename,sal,job
FROM emp WHERE empno=7655;
或
SELECT /*+ RULE */ empno,ename,sal,job
FROM emp WHERE empno=7655;

訪問方法共有：
 FULL
 ROWID
 CLUSTER
 HASH
 INDEX
 INDEX_ASC
 INDEX_COMBINE
 INDEX_JOIN
 INDEX_DESC
 INDEX_FFS
 NO_INDEX
 AND_EQUAL
 USE_CONCAT
 NO_EXPAND
 REWRITE
 NOREWRITE
如果在語句中指定了上面的提示，並且語句所涉及的索引或簇是可用時，優化器就使用所指
定的訪問路徑。否則，優化器就忽略提示的要求。

FULL
FULL 提示表示對錶選擇全表掃描的訪問方法。語法如下：
/*+ FULL ( [table_name]| [table_aliase] ) */
例：
SELECT /*+ FULL(A) don’t use the index on accno */ accno,bal
FROM accounts a
WHERE accno=7789;
雖然這裡使用了帶索引的條件句，優化器也得選擇全表掃描。

ROWID
ROWID 表示對指定表選擇根據rowid 進行表掃描，語法如下：
/*+ ROWID( table_name ) */
例：
SELECT /*+ROWID(emp)*/ * FROM emp
WHERE rowid>’AAAATKAABAAAFNTAAA’ AND empno=155;

CLUSTER
CLUSTER 表示對指定表選擇簇掃描的訪問方法。它僅對CLUSTER 物件有效。語法如
下：
/*+CLUSTER(table_name) */
例：
SELECT --+CLUSTER emp.ename, deptno
FROM emp,dept
WHERE deptno=10 AND emp.deptno=dept.deptno;

HASH
HASH 表示對指定的表選擇HASH 掃描訪問方法，它只對CLUSTER 中的表有效。語法
如下：
/*+HASH(table_name)*/

INDEX
INDEX 表示對指定表選擇索引掃描的訪問方法。使用者可以對域、B*樹和點陣圖索引應用本
提示。對於建立了點陣圖的索引，建議用INDEX_COMBINE 更為合適。語法如下：
/*+INDEX(table_name [index]) */
例：
SELECT /*+INDEX(patients sex_index)use sex_index because there are few male patients*/
Name,height,weight FROM patients WHERE sex=’m’;

INDEX_ASC
INDEX_ASC 表示對指定的表選擇索引的訪問方法，並按照升序進行掃描。語法如下：
/*+INDEX_ASC(table_name [index])*/

INDEX_COMBINE
INDEX_COMBINE 表示對指定的表選擇點陣圖訪問路徑。
 如果沒有提供可參考的索引，則優化器以最低開銷為目標，選擇點陣圖索引的布林組合
方式；
 如果有可參考的索引，則優化器就使用該點陣圖的某寫布林組合。
語法如下：
/*+INDEX_COMBINE(table_name[index])*/
例：
SELECT /*+INDEX_COMBINE(emp sal_bmi hiredate_bmi)*/*
FROM emp
WHERE sal<5000 AND hiredate ,’01-JAN-1990’;

INDEX_JOIN
INDEX_JOIN 表示使用索引連線作為訪問路徑。語法如下：
/*+INDEX_JOIN(table_name[index])*/
例：
SELECT /*+INDEX_JOIN(emp sal_bmi hiredate_bmi)*/sal,hirdate
FROM emp
WHERE sal<5000 ;

INDEX_DESC
INDEX_DESC 表示對指定表選擇索引訪問方法。如果使用索引區域掃描，則按照降序進
行掃描。語法如下：
/*+INDEX_DESC(table_name[index])*/

INDEX_FFS
INDEX_DESC 表示對指定表選擇快速索引訪問方法（不是全表掃描）。語法如下：
/*+INDEX_FFS(table_name[index])*/
例：
SELECT /*+INDEX_FFS(emp emp_empnp)*/ empno
FROM emp
WHERE empno>200;

NO_INDEX
NO_INDEX 表示對指定表禁止選擇索引訪問方法。語法如下：
/*+NO_INDEX(table_name[index])*/
例：
SELECT /*+NO_INDEX(emp emp_empnp)*/ empno
FROM emp
WHERE empno>200;

AND_EQUAL
AND-EQUAL 表示要進行執行規則的選擇。使幾個列的索引的掃描合併起來。語法如
下：
/*+AND_EQUAL(table_name[index] [inex]…)*/

USE_CONCAT
USE_CONCAT 提示強制對查詢語句中的WHERE 從句的OR 條件進行轉換，轉化成由
UNION_ALL 集合操作符連線的組合查詢。一般來說，如果採用連線查詢比不用連線查詢低，
則轉換為用連線查詢。
/*+USE_COMCAT*/
例：
SELECT /*USE_CONCAT*/* FROM emp
WHERE empno>50 OR sal<50000;

NO_EXPAND
NO_EXPAND 對於具有OR 或IN 查詢語句，它將阻止基於開銷的優化器對其進行OR 擴
展。語法如下：
/*+NO_EXPAND*/
例：
SELECT /*+NO_EXPAND*/*
FROM emp
WHERE empno=50 OR empno=100;

REWRITE
REWRITE 表示可以將檢視列表作為引數來看，如果使用者使用REWRITE，並且該列表包
含有符合條件的實體化檢視，則Oracle 優化器將利用該檢視而不用基於開銷的方法。而對於
列表以外的檢視將不被考慮。如果在REWRITE 中沒有給出檢視列表，則Oracle 將搜尋符合
條件的實體化檢視。並且利用該檢視。語法如下：
/*+REWRITE(view,[view]…)*/

NOWRITE
NOWRITE 表示禁止對查詢塊的查詢重寫操作， 從而避免引數
QUERY_REWRITE_ENABLE 的設定。語法如下：
/*+NOWRITE*/

關於連線次序的提示
ORDERED
根據出現在FROM 中順序，ORDERED 提示將使得Oracle 依此次序對其進行連線。語法
如下：
/*+ORDERED*/
例：
SELECT /*+ORDERED*/tab1.col1,tabl2.col2,tab3.col3
FROM tab1,tab2,tab3
WHERE tab1.col1=tab2.col1 AND tab2.col1=tab3.col1;

STAR
強行讓優化器使用星型查詢規劃。星型規劃擁有查詢中最大的一個表，該表位於連線次
序的最後，並與巢狀式迴圈連線的級聯索引連線。如果滿足下面３個條件：
１）至少存在３個表；
２）最大表的級聯索引至少存在３列；
３）不存在衝突的訪問或連線訪問提示。
/*+ STAR */

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-746386/，如需轉載，請註明出處，否則將追究法律責任。

相關文章