其實Oracle的優化器有兩種優化方式,
基于規則的優化方式(Rule-Based Optimization，簡稱為RBO)
基于代價的優化方式(Cost-Based Optimization，簡稱為CBO)
所以hint也不例外，除了/*+rule*/其他的都是CBO優化方式
RBO方式：
優化器在分析SQL語句時,所遵循的是Oracle內部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。
CBO方式：
它是看語句的代價(Cost),這里的代價主要指Cpu和內存。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計信息。統計信息給出表的大小、有少行、每行的長度等信息。這些統計信息起初在庫內是沒有的，是做analyze后才出現的，很多的時侯過期統計信息會令優化器做出一個錯誤的執行計劃,因些應及時更新這些信息。
優化模式包括Rule、Choose、First rows、All rows四種方式：
Rule：基于規則的方式。
Choolse：默認的情況下Oracle用的便是這種方式。指的是當一個表或或索引有統計信息，則走CBO的方式，如果表或索引沒統計信息，表又不是特別的小，而且相應的列有索引時，那么就走索引，走RBO的方式。
First Rows：它與Choose方式是類似的，所不同的是當一個表有統計信息時，它將是以最快的方式返回查詢的最先的幾行，從總體上減少了響應時間。
All Rows:也就是我們所說的Cost的方式，當一個表有統計信息時，它將以最快的方式返回表的所有的行，從總體上提高查詢的吞吐量。沒有統計信息則走RBO的方式
Oracle在那配置默認的優化規則
A、Instance級別我們可以通過在initSID.ora文件中設定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果沒設定OPTIMIZER_MODE參數則默認用的是Choose方式。
B、Sessions級別通過ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS來設定。
C、語句級別用Hint（/*+ ... */）來設定
為什么表的某個字段明明有索引，但執行計劃卻不走索引？
1、優化模式是all_rows的方式
2、表作過analyze，有統計信息
3、表很小，Oracle的優化器認為不值得走索引。
提示
不區分大小寫, 多個提示用空格分開
如：select /*+ hint1(tab1) hint2(TAB1 idx1) */ col1, col2 from tab1 where col1='xxx';
如果表使用了別名, 那么提示里也必須使用別名
如：select /*+ hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';
如果使用同一個表的多個用,號分開
如： select /*+ index(t1.A,t1.B) */ col1, col2
from tab1 t1
where col1='xxx';
oracle 10g hints知識,
10g數據庫可以使用更多新的optimizer hints來控制優化行為。現在讓我們快速解析一下這些強大的新hints：
1、spread_min_analysis
使用這一hint，你可以忽略一些關于如詳細的關系依賴圖分析等電子表格的編譯時間優化規則。其他的一些優化，如創建過濾以有選擇性的定位電子表格訪問結構并限制修訂規則等，得到了繼續使用。
由于在規則數非常大的情況下，電子表格分析會很長。這一提示可以幫助我們減少由此產生的數以百小時計的編譯時間。
例：
SELECT /*+ SPREAD_MIN_ANALYSIS */ ...
2、spread_no_analysis
通過這一hint，可以使無電子表格分析成為可能。同樣，使用這一hint可以忽略修訂規則和過濾產生。如果存在一電子表格分析，編譯時間可以被減少到最低程度。
例：
SELECT /*+ SPREAD_NO_ANALYSIS */ ...
3、use_nl_with_index
這項hint使CBO通過嵌套循環把特定的表格加入到另一原始行。只有在以下情況中，它才使用特定表格作為內部表格：如果沒有指定標簽，CBO必須可以使用一些標簽，且這些標簽至少有一個作為索引鍵值加入判斷；反之，CBO必須能夠使用至少有一個作為索引鍵值加入判斷的標簽。
例：
SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...
4、CARDINALITY
此hint定義了對由查詢或查詢部分返回的基數的評價。注意如果沒有定義表格，基數是由整個查詢所返回的總行數。
例：
SELECT /*+ CARDINALITY ( [tablespec] card ) */
5、SELECTIVITY
此hint定義了對查詢或查詢部分選擇性的評價。如果只定義了一個表格，選擇性是在所定義表格里滿足所有單一表格判斷的行部分。如果定義了一系列表格，選擇性是指在合并以任何順序滿足所有可用判斷的全部表格后，所得結果中的行部分。
例：
SELECT /*+ SELECTIVITY ( [tablespec] sel ) */
然而，注意如果hints CARDINALITY 和 SELECTIVITY都定義在同樣的一批表格，二者都會被忽略。
6、no_use_nl
Hint no_use_nl使CBO執行循環嵌套，通過把指定表格作為內部表格，把每個指定表格連接到另一原始行。通過這一hint，只有hash join和sort-merge joins會為指定表格所考慮。
例：
SELECT /*+ NO_USE_NL ( employees ) */ ...
7、no_use_merge
此hint使CBO通過把指定表格作為內部表格的方式，拒絕sort-merge把每個指定表格加入到另一原始行。
例：
SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...
8、no_use_hash
此hint使CBO通過把指定表格作為內部表格的方式，拒絕hash joins把每個指定表格加入到另一原始行。
例：
SELECT /*+ NO_USE_HASH ( employees dept ) */ ...
9、no_index_ffs
此hint使CBO拒絕對指定表格的指定標簽進行fast full-index scan。
Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */
在SQL優化過程中常見HINT的用法(前10個比較常用, 前3個最常用)：
1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(tab1.col1 tab2.col2) */ 和 /*+ 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 */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;
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) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_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 BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
---------------------------------------------------------------------
11. /*+ USE_MERGE(TABLE) */
將指定的表與其它行源通過合并排序連接方式連接起來.特別適合于那種在多個表大量行上進行集合操作的查詢, 它會將指定表檢索到的的所有行排序后再被合并, 與USE_NL剛好相反.
例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;
12. /*+ ALL_ROWS */
表明對語句塊選擇基于開銷的優化方法,并獲得最佳吞吐量,使資源消耗最小化. 可能會限制某些索引的使用.
例如:SELECT /*+ ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
13. /*+ CLUSTER(TABLE) */
提示明確表明對指定表選擇簇掃描的訪問方法. 如果經常訪問連接表但很少修改它, 那就使用集群提示.
例如:SELECT /*+ CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_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<SYSDATE;
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 BSEMPMS.DPT_NO=DEPT.DPT_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 */ emp.empno, emp.ename, itemno from emp, orders where emp.empno = orders.empno and emp.deptno = (select deptno from dept where loc='XXX');
遠程連接其他數據庫，注意判斷數據庫是否啟動，或者是否有需要的表，否則會出錯
23. /*+ INDEX_SS(TABLE INDEX1,INDEX2) */
指示對特定表的索引使用跳躍掃描, 即當組合索引的第一列不在where子句中時, 讓其使用該索引
參考資料
Oracle SQL hints
/*+ hint */
/*+ hint(argument) */
/*+ hint(argument-1 argument-2) */
All hints except /*+ rule */ cause the CBO to be used. Therefore, it is good practise to analyze the underlying tables if hints are used (or the query is fully hinted. There should be no schema names in hints. Hints must use aliases if alias names are used for table names. So the following is wrong:
select /*+ index(scott.emp ix_emp) */ from scott.emp emp_alias
better:
select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias
Why using hints
It is a perfect valid question to ask why hints should be used. Oracle comes with an optimizer that promises to optimize a query's execution plan. When this optimizer is really doing a good job, no hints should be required at all. Sometimes, however, the characteristics of the data in the database are changing rapidly, so that the optimizer (or more accuratly, its statistics) are out of date. In this case, a hint could help. It must also be noted, that Oracle allows to lock the statistics when they look ideal which should make the hints meaningless again.
Hint categories
Hints can be categorized as follows:
Hints for Optimization Approaches and Goals,
Hints for Access Paths, Hints for Query Transformations,
Hints for Join Orders,
Hints for Join Operations,
Hints for Parallel Execution,
Additional Hints
Documented Hints
Hints for Optimization Approaches and Goals
ALL_ROWS
One of the hints that 'invokes' the Cost based optimizer
ALL_ROWS is usually used for batch processing or data warehousing systems.
FIRST_ROWS
One of the hints that 'invokes' the Cost based optimizer
FIRST_ROWS is usually used for OLTP systems.
CHOOSE
One of the hints that 'invokes' the Cost based optimizer
This hint lets the server choose (between ALL_ROWS and FIRST_ROWS, based on statistics gathered.
RULE
The RULE hint should be considered deprecated as it is dropped from Oracle9i2.
See also the following initialization parameters: optimizer_mode, optimizer_max_permutations, optimizer_index_cost_adj, optimizer_index_caching and
Hints for Access Paths
CLUSTER
Performs a nested loop by the cluster index of one of the tables.
FULL
Performs full table scan.
HASH
Hashes one table (full scan) and creates a hash index for that table. Then hashes other table and uses hash index to find corresponding records. Therefore not suitable for < or > join conditions.
ROWID
Retrieves the row by rowid
INDEX
Specifying that index index_name should be used on table tab_name: /*+ index (tab_name index_name) */
Specifying that the index should be used the the CBO thinks is most suitable. (Not always a good choice).
Starting with Oracle 10g, the index hint can be described: /*+ index(my_tab my_tab(col_1, col_2)) */. Using the index on my_tab that starts with the columns col_1 and col_2.
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
NO_INDEX
AND_EQUAL
The AND_EQUAL hint explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes
Hints for Query Transformations
FACT
The FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should be considered as a fact table.
MERGE
NO_EXPAND
NO_EXPAND_GSET_TO_UNION
NO_FACT
NO_MERGE
NOREWRITE
REWRITE
STAR_TRANSFORMATION
USE_CONCAT
Hints for Join Operations
DRIVING_SITE
HASH_AJ
HASH_SJ
LEADING
MERGE_AJ
MERGE_SJ
NL_AJ
NL_SJ
USE_HASH
USE_MERGE
USE_NL
Hints for Parallel Execution
NOPARALLEL
PARALLEL
NOPARALLEL_INDEX
PARALLEL_INDEX
PQ_DISTRIBUTE
Additional Hints
ANTIJOIN
APPEND
If a table or an index is specified with nologging, this hint applied with an insert statement produces a direct path insert which reduces generation of redo.
BITMAP
BUFFER
CACHE
CARDINALITY
CPU_COSTING
DYNAMIC_SAMPLING
INLINE
MATERIALIZE
NO_ACCESS
NO_BUFFER
NO_MONITORING
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_QKN_BUFF
NO_SEMIJOIN
NOAPPEND
NOCACHE
OR_EXPAND
ORDERED
ORDERED_PREDICATES
PUSH_PRED
PUSH_SUBQ
QB_NAME
RESULT_CACHE (Oracle 11g)
SELECTIVITY
SEMIJOIN
SEMIJOIN_DRIVER
STAR
The STAR hint forces a star query plan to be used, if possible. A star plan has the largest table in the query last in the join order and joins it with a nested loops join on a concatenated index. The STAR hint applies when there are at least three tables, the large table's concatenated index has at least three columns, and there are no conflicting access or join method hints. The optimizer also considers different permutations of the small tables.
SWAP_JOIN_INPUTS
USE_ANTI
USE_SEMI
Undocumented hints:
BYPASS_RECURSIVE_CHECK
Workaraound for bug 1816154
BYPASS_UJVC
CACHE_CB
CACHE_TEMP_TABLE
CIV_GB
COLLECTIONS_GET_REFS
CUBE_GB
CURSOR_SHARING_EXACT
DEREF_NO_REWRITE
DML_UPDATE
DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN
EXPAND_GSET_TO_UNION
FORCE_SAMPLE_BLOCK
GBY_CONC_ROLLUP
GLOBAL_TABLE_HINTS
HWM_BROKERED
IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE
INDEX_RRS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
LIKE_EXPAND
LOCAL_INDEXES
MV_MERGE
NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID
NO_FILTERING
NO_ORDER_ROLLUPS
NO_PRUNE_GSETS
NO_STATS_GSETS
NO_UNNEST
NOCPU_COSTING
OVERFLOW_NOMOVE
PIV_GB
PIV_SSF
PQ_MAP
PQ_NOMAP
REMOTE_MAPPED
RESTORE_AS_INTERVALS
SAVE_AS_INTERVALS
SCN_ASCENDING
SKIP_EXT_OPTIMIZER
SQLLDR
SYS_DL_CURSOR
SYS_PARALLEL_TXN
SYS_RID_ORDER
TIV_GB
TIV_SSF
UNNEST
USE_TTT_FOR_GSETS