Hint概述
基于代價(jià)的優(yōu)化器是很聰明的，在絕大多數(shù)情況下它會(huì)選擇正確的優(yōu)化器，減輕了DBA的負(fù)擔(dān)。但有時(shí)它也聰明反被聰明誤，選擇了很差的執(zhí)行計(jì)劃，使某個(gè)語(yǔ)句的執(zhí)行變得奇慢無(wú)比。
此時(shí)就需要DBA進(jìn)行人為的干預(yù)，告訴優(yōu)化器使用我們指定的存取路徑或連接類型生成執(zhí)行計(jì)劃，從而使語(yǔ)句高效的運(yùn)行。例如，如果我們認(rèn)為對(duì)于一個(gè)特定的語(yǔ)句，執(zhí)行全表掃描要比執(zhí)行索引掃描更有效，則我們就可以指示優(yōu)化器使用全表掃描。在Oracle 中，是通過(guò)為語(yǔ)句添加 Hints(提示)來(lái)實(shí)現(xiàn)干預(yù)優(yōu)化器優(yōu)化的目的。
不建議在代碼中使用hint，在代碼使用hint使得CBO無(wú)法根據(jù)實(shí)際的數(shù)據(jù)狀態(tài)選擇正確的執(zhí)行計(jì)劃。畢竟 數(shù)據(jù)是不斷變化的， 10g以后的CBO也越來(lái)越完善，大多數(shù)情況下我們?cè)撟孫racle自行決定采用什么執(zhí)行計(jì)劃。
Oracle Hints是一種機(jī)制，用來(lái)告訴優(yōu)化器按照我們的告訴它的方式生成執(zhí)行計(jì)劃。我們可以用Oracle Hints來(lái)實(shí)現(xiàn)：
1) 使用的優(yōu)化器的類型
2) 基于代價(jià)的優(yōu)化器的優(yōu)化目標(biāo)，是all_rows還是first_rows。
3) 表的訪問(wèn)路徑，是全表掃描，還是索引掃描，還是直接利用rowid。
4) 表之間的連接類型
5) 表之間的連接順序
6) 語(yǔ)句的并行程度
除了”RULE”提示外，一旦使用的別的提示，語(yǔ)句就會(huì)自動(dòng)的改為使用CBO優(yōu)化器，此時(shí)如果你的數(shù)據(jù)字典中沒(méi)有統(tǒng)計(jì)數(shù)據(jù)，就會(huì)使用缺省的統(tǒng)計(jì)數(shù)據(jù)。所以建議大家如果使用CBO或Hints提示，則最好對(duì)表和索引進(jìn)行定期的分析。
如何使用Hints:
Hints只應(yīng)用在它們所在sql語(yǔ)句塊(statement block，由select、update、delete關(guān)鍵字標(biāo)識(shí))上，對(duì)其它SQL語(yǔ)句或語(yǔ)句的其它部分沒(méi)有影響。如：對(duì)于使用union操作的2個(gè)sql語(yǔ)句，如果只在一個(gè)sql語(yǔ)句上有Hints，則該Hints不會(huì)影響另一個(gè)sql語(yǔ)句。
我們可以使用注釋(comment)來(lái)為一個(gè)語(yǔ)句添加Hints，一個(gè)語(yǔ)句塊只能有一個(gè)注釋，而且注釋只能放在SELECT, UPDATE, or DELETE關(guān)鍵字的后面
使用Oracle Hints的語(yǔ)法：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
注解：
1) DELETE、INSERT、SELECT和UPDATE是標(biāo)識(shí)一個(gè)語(yǔ)句塊開(kāi)始的關(guān)鍵字，包含提示的注釋只能出現(xiàn)在這些關(guān)鍵字的后面，否則提示無(wú)效。
2) “+”號(hào)表示該注釋是一個(gè)Hints，該加號(hào)必須立即跟在”/*”的后面，中間不能有空格。
3) hint是下面介紹的具體提示之一，如果包含多個(gè)提示，則每個(gè)提示之間需要用一個(gè)或多個(gè)空格隔開(kāi)。
4) text 是其它說(shuō)明hint的注釋性文本
5)使用表別名。如果在查詢中指定了表別名，那么提示必須也使用表別名。例如：select /*+ index(e,dept_idx) */ * from emp e;
6)不要在提示中使用模式名稱：如果在提示中指定了模式的所有者，那么提示將被忽略。例如：
select /*+ index(scott.emp,dept_idx) */ * from emp
注意：如果你沒(méi)有正確的指定Hints，Oracle將忽略該Hints，并且不會(huì)給出任何錯(cuò)誤。
hint被忽略
如果CBO認(rèn)為使用hint會(huì)導(dǎo)致錯(cuò)誤的結(jié)果時(shí)，hint將被忽略，詳見(jiàn)下例
SQL> select /*+ index(t t_ind) */ count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 57 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50366 | 57 (2)| 00:00:01 |
-------------------------------------------------------------------
因?yàn)槲覀兪菍?duì)記錄求總數(shù)，且我們并沒(méi)有在建立索引時(shí)指定不能為空，索引如果CBO選擇在索引上進(jìn)行count時(shí)，但索引字段上的值為空時(shí)，結(jié)果將不準(zhǔn)確，故CBO沒(méi)有選擇索引。
SQL> select /*+ index(t t_ind) */ count(id) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 646498162
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 285 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
| 2 | INDEX FULL SCAN| T_IND | 50366 | 245K| 285 (1)| 00:00:04 |
--------------------------------------------------------------------------
因?yàn)槲覀冎粚?duì)id進(jìn)行count，這個(gè)動(dòng)作相當(dāng)于count索引上的所有id值，這個(gè)操作和對(duì)表上的id字段進(jìn)行count是一樣的(組函數(shù)會(huì)忽略null值)
Hint的具體用法
和優(yōu)化器相關(guān)的hint
1、/*+ ALL_ROWS */
表明對(duì)語(yǔ)句塊選擇基于開(kāi)銷的優(yōu)化方法,并獲得最佳吞吐量,使資源消耗最小化.
SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2、/*+ FIRST_ROWS(n) */
表明對(duì)語(yǔ)句塊選擇基于開(kāi)銷的優(yōu)化方法,并獲得最佳響應(yīng)時(shí)間,使資源消耗最小化.
SELECT /*+FIRST_ROWS(20) */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3、/*+ RULE*/
表明對(duì)語(yǔ)句塊選擇基于規(guī)則的優(yōu)化方法.
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
和訪問(wèn)路徑相關(guān)的hint
1、/*+ FULL(TABLE)*/
表明對(duì)表選擇全局掃描的方法.
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
2、/*+ INDEX(TABLE INDEX_NAME) */
表明對(duì)表選擇索引的掃描方法.
SELECT /*+INDEX(BSEMPMS SEX_INDEX) */ * FROM BSEMPMS WHERE SEX='M';
5、/*+ INDEX_ASC(TABLE INDEX_NAME)*/
表明對(duì)表選擇索引升序的掃描方法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';
6、/*+ INDEX_COMBINE*/
為指定表選擇位圖訪問(wèn)路經(jīng),如果INDEX_COMBINE中沒(méi)有提供作為參數(shù)的索引,將選擇出位圖索引的布爾組合方式.
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE
7、/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */
當(dāng)謂詞中引用的列都有索引的時(shí)候，可以通過(guò)指定采用索引關(guān)聯(lián)的方式，來(lái)訪問(wèn)數(shù)據(jù)
select /*+ index_join(t t_ind t_bm) */ id from t where id=100 and object_name='EMPLOYEES'
8、/*+ INDEX_DESC(TABLE INDEX_NAME)*/
表明對(duì)表選擇索引降序的掃描方法.
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';
9、/*+ INDEX_FFS(TABLE INDEX_NAME) */
對(duì)指定的表執(zhí)行快速全索引掃描,而不是全表掃描的辦法.
SELECT /* + INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
10、/*+ INDEX_SS(T T_IND) */
從9i開(kāi)始，oracle引入了這種索引訪問(wèn)方式。當(dāng)在一個(gè)聯(lián)合索引中，某些謂詞條件并不在聯(lián)合索引的第一列時(shí)，可以通過(guò)Index Skip Scan來(lái)訪問(wèn)索引獲得數(shù)據(jù)。當(dāng)聯(lián)合索引第一列的唯一值個(gè)數(shù)很少時(shí)，使用這種方式比全表掃描效率高。
SQL> create table t as select 1 id,object_name from dba_objects;
Table created.
SQL> insert into t select 2,object_name from dba_objects;
50366 rows created.
SQL> insert into t select 3,object_name from dba_objects;
50366 rows created.
SQL> insert into t select 4,object_name from dba_objects;
50366 rows created.
SQL> commit;
Commit complete.
SQL> create index t_ind on t(id,object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=>true);
PL/SQL procedure successfully completed.
執(zhí)行全表掃描
SQL> select /*+ full(t) */ * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 215 (3)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| T | 5 | 135 | 215 (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
942 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
不采用hint
SQL> select * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2869677071
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 135 | 5 (0)| 00:00:01 |
|* 1 | INDEX SKIP SCAN | T_IND | 5 | 135 | 5 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("OBJECT_NAME"='EMPLOYEES')
filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
17 consistent gets
1 physical reads
0 redo size
538 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
當(dāng)全表掃描掃描了942個(gè)塊，聯(lián)合索引只掃描了17個(gè)數(shù)據(jù)塊。可以看到聯(lián)合索引的第一個(gè)字段的值重復(fù)率很高時(shí)，即使謂詞中沒(méi)有聯(lián)合索引的第一個(gè)字段，依然會(huì)使用index_ss方式，效率遠(yuǎn)遠(yuǎn)高于全表掃描效率。但當(dāng) 第一個(gè)字段的值重復(fù)率很低時(shí)，使用 index_ss的效率要低于 全表掃描，讀者可以自行實(shí)驗(yàn)
和表的關(guān)聯(lián)相關(guān)的hint
/*+ leading(table_1,table_2) */
在多表關(guān)聯(lián)查詢中，指定哪個(gè)表作為驅(qū)動(dòng)表，即告訴優(yōu)化器首先要訪問(wèn)哪個(gè)表上的數(shù)據(jù)。
select /*+ leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ order */
讓Oracle根據(jù)from后面表的順序來(lái)選擇驅(qū)動(dòng)表，oracle建議使用leading，他更為靈活
select /*+ order */ t.* from t,t1 where t.id=t1.id;
/*+ use_nl(table_1,table_2) */
在多表關(guān)聯(lián)查詢中，指定使用nest loops方式進(jìn)行多表關(guān)聯(lián)。
select /*+ use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ use_hash(table_1,table_2) */
在多表關(guān)聯(lián)查詢中，指定使用hash join方式進(jìn)行多表關(guān)聯(lián)。
select /*+ use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;
在多表關(guān)聯(lián)查詢中，指定使用hash join方式進(jìn)行多表關(guān)聯(lián)，并指定表t為驅(qū)動(dòng)表。
select /*+ use_hash(t,t1) leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ use_merge(table_1,table_2) */
在多表關(guān)聯(lián)查詢中，指定使用merge join方式進(jìn)行多表關(guān)聯(lián)。
select /*+ use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_nl(table_1,table_2) */
在多表關(guān)聯(lián)查詢中，指定不使用nest loops方式進(jìn)行多表關(guān)聯(lián)。
select /*+ no_use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_hash(table_1,table_2) */
在多表關(guān)聯(lián)查詢中，指定不使用hash join方式進(jìn)行多表關(guān)聯(lián)。
select /*+ no_use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_use_merge(table_1,table_2) */
在多表關(guān)聯(lián)查詢中，指定不使用merge join方式進(jìn)行多表關(guān)聯(lián)。
select /*+ no_use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;
其他常用的hint
/*+ parallel(table_name n) */
在sql中指定執(zhí)行的并行度，這個(gè)值將會(huì)覆蓋自身的并行度
select /*+ parallel(t 4) */ count(*) from t;
/*+ no_parallel(table_name) */
在sql中指定執(zhí)行的不使用并行
select /*+ no_parallel(t) */ count(*) from t;
/*+ append */以直接加載的方式將數(shù)據(jù)加載入庫(kù)
insert into t /*+ append */ select * from t;
/*+ dynamic_sampling(table_name n) */
設(shè)置sql執(zhí)行時(shí)動(dòng)態(tài)采用的級(jí)別，這個(gè)級(jí)別為0~10
select /*+ dynamic_sampling(t 4) */ * from t where id > 1234
/*+ cache(table_name) */
進(jìn)行全表掃描時(shí)將table置于LRU列表的最活躍端，類似于table的cache屬性
select /*+ full(employees) cache(employees) */ last_name from employees
附錄hint表格
|
Hints for Optimization Approaches and Goals |
| ALL_ROWS |
The ALL_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption). |
| FIRST_ROWS |
The FIRST_ROWS hint explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row). In newer Oracle version you should give a parameter with this hint: FIRST_ROWS(n) means that the optimizer will determine an executionplan to give a fast response for returning the first n rows. |
| CHOOSE |
The CHOOSE hint causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement |
| RULE |
The RULE hint explicitly chooses rule-based optimization for a statement block. This hint also causes the optimizer to ignore any other hints specified for the statement block. The RULE hint does not work any more in Oracle 10g. |
|
Hints for Access Paths |
| FULL |
The FULL hint explicitly chooses a full table scan for the specified table. The syntax of the FULL hint is FULL(table) where table specifies the alias of the table (or table name if alias does not exist) on which the full table scan is to be performed. |
| ROWID |
The ROWID hint explicitly chooses a table scan by ROWID for the specified table. The syntax of the ROWID hint is ROWID(table) where table specifies the name or alias of the table on which the table access by ROWID is to be performed. (This hint depricated in Oracle 10g) |
| CLUSTER |
The CLUSTER hint explicitly chooses a cluster scan to access the specified table. The syntax of the CLUSTER hint is CLUSTER(table) where table specifies the name or alias of the table to be accessed by a cluster scan. |
| HASH |
The HASH hint explicitly chooses a hash scan to access the specified table. The syntax of the HASH hint is HASH(table) where table specifies the name or alias of the table to be accessed by a hash scan. |
| HASH_AJ |
The HASH_AJ hint transforms a NOT IN subquery into a hash anti-join to access the specified table. The syntax of the HASH_AJ hint is HASH_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g) |
| INDEX |
The INDEX hint explicitly chooses an index scan for the specified table. The syntax of the INDEX hint is INDEX(table index) where:table specifies the name or alias of the table associated with the index to be scanned and index specifies an index on which an index scan is to be performed. This hint may optionally specify one or more indexes: |
| NO_INDEX |
The NO_INDEX hint explicitly disallows a set of indexes for the specified table. The syntax of the NO_INDEX hint is NO_INDEX(table index) |
| INDEX_ASC |
The INDEX_ASC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in ascending order of their indexed values. |
| INDEX_COMBINE |
If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer will use on the table whatever boolean combination of bitmap indexes has the best cost estimate. If certain indexes are given as arguments, the optimizer will try to use some boolean combination of those particular bitmap indexes. The syntax of INDEX_COMBINE is INDEX_COMBINE(table index). |
| INDEX_JOIN |
Explicitly instructs the optimizer to use an index join as an access path. For the hint to have a positive effect, a sufficiently small number of indexes must exist that contain all the columns required to resolve the query. |
| INDEX_DESC |
The INDEX_DESC hint explicitly chooses an index scan for the specified table. If the statement uses an index range scan, Oracle scans the index entries in descending order of their indexed values. |
| INDEX_FFS |
This hint causes a fast full index scan to be performed rather than a full table. |
| NO_INDEX_FFS |
Do not use fast full index scan (from Oracle 10g) |
| INDEX_SS |
Exclude range scan from query plan (from Oracle 10g) |
| INDEX_SS_ASC |
Exclude range scan from query plan (from Oracle 10g) |
| INDEX_SS_DESC |
Exclude range scan from query plan (from Oracle 10g) |
| NO_INDEX_SS |
The NO_INDEX_SS hint causes the optimizer to exclude a skip scan of the specified indexes on the specified table. (from Oracle 10g) |
|
Hints for Query Transformations
|
| NO_QUERY_TRANSFORMATION |
Prevents the optimizer performing query transformations. (from Oracle 10g) |
| USE_CONCAT |
The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Normally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them. |
| NO_EXPAND |
The NO_EXPAND hint prevents the optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause. Usually, the optimizer considers using OR expansion and uses this method if it decides that the cost is lower than not using it. |
| REWRITE |
The REWRITE hint forces the optimizer to rewrite a query in terms of materialized views, when possible, without cost consideration. Use the REWRITE hint with or without a view list. If you use REWRITE with a view list and the list contains an eligible materialized view, then Oracle uses that view regardless of its cost. |
| NOREWRITE / NO_REWRITE |
In Oracle 10g renamed to NO_REWRITE. The NOREWRITE/NO_REWRITE hint disables query rewrite for the query block, overriding the setting of the parameter QUERY_REWRITE_ENABLED. |
| MERGE |
The MERGE hint lets you merge views in a query. |
| NO_MERGE |
The NO_MERGE hint causes Oracle not to merge mergeable views. This hint is most often used to reduce the number of possible permutations for a query and make optimization faster. |
| FACT |
The FACT hint indicated that the table should be considered as a fact table. This is used in the context of the star transformation. |
| NO_FACT |
The NO_FACT hint is used in the context of the star transformation to indicate to the transformation that the hinted table should not be considered as a fact table. |
| STAR_TRANSFORMATION |
The STAR_TRANSFORMATION hint makes the optimizer use the best plan in which the transformation has been used. Without the hint, the optimizer could make a query optimization decision to use the best plan generated without the transformation, instead of the best plan for the transformed query. |
| NO_STAR_TRANSFORMATION |
Do not use star transformation (from Oracle 10g) |
| UNNEST |
The UNNEST hint specifies subquery unnesting. |
| NO_UNNEST |
Use of the NO_UNNEST hint turns off unnesting for specific subquery blocks. |
|
Hints for Join Orders
|
| LEADING |
Give this hint to indicate the leading table in a join. This will indicate only 1 table. If you want to specify the whole order of tables, you can use the ORDERED hint. Syntax: LEADING(table) |
| ORDERED |
The ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join , the optimizer chooses the order in which to join the tables. You may want to use the ORDERED hint to specify a join order if you know something about the number of rows selected from each table that the optimizer does not. Such information would allow you to choose an inner and outer table better than the optimizer could. |
|
Hints for Join Operations
|
| USE_NL |
The USE_NL hint causes Oracle to join each specified table to another row source with a nested loops join using the specified table as the inner table. The syntax of the USE_NL hint is USE_NL(table table) where table is the name or alias of a table to be used as the inner table of a nested loops join. |
| NO_USE_NL |
Do not use nested loop (from Oracle 10g) |
| USE_NL_WITH_INDEX |
Specifies a nested loops join. (from Oracle 10g) |
| USE_MERGE |
The USE_MERGE hint causes Oracle to join each specified table with another row source with a sort-merge join. The syntax of the USE_MERGE hint is USE_MERGE(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a sort-merge join. |
| NO_USE_MERGE |
Do not use merge (from Oracle 10g) |
| USE_HASH |
The USE_HASH hint causes Oracle to join each specified table with another row source with a hash join. The syntax of the USE_HASH hint is USE_HASH(table table) where table is a table to be joined to the row source resulting from joining the previous tables in the join order using a hash join. |
| NO_USE_HASH |
Do not use hash (from Oracle 10g) |
| Hints for Parallel Execution |
| PARALLEL |
The PARALLEL hint allows you to specify the desired number of concurrent query servers that can be used for the query. The syntax is PARALLEL(table number number). The PARALLEL hint must use the table alias if an alias is specified in the query. The PARALLEL hint can then take two values separated by commas after the table name. The first value specifies the degree of parallelism for the given table, the second value specifies how the table is to be split among the instances of a parallel server. Specifying DEFAULT or no value signifies the query coordinator should examine the settings of the initialization parameters (described in a later section) to determine the default degree of parallelism. |
| NOPARALLEL / NO_PARALLEL |
The NOPARALLEL hint allows you to disable parallel scanning of a table, even if the table was created with a PARALLEL clause. In Oracle 10g this hint was renamed to NO_PARALLEL. |
| PQ_DISTRIBUTE |
The PQ_DISTRIBUTE hint improves the performance of parallel join operations. Do this by specifying how rows of joined tables should be distributed among producer and consumer query servers. Using this hint overrides decisions the optimizer would normally make. |
| NO_PARALLEL_INDEX |
The NO_PARALLEL_INDEX hint overrides a PARALLEL attribute setting on an index to avoid a parallel index scan operation. |
| Additional Hints |
| APPEND |
When the APPEND hint is used with the INSERT statement, data is appended to the table. Existing free space in the block is not used. 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. |
| NOAPPEND |
Overrides the append mode. |
| CACHE |
The CACHE hint specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. In the following example, the CACHE hint overrides the table default caching specification. |
| NOCACHE |
The NOCACHE hint specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed. This is the normal behavior of blocks in the buffer cache. |
| PUSH_PRED |
The PUSH_PRED hint forces pushing of a join predicate into the view. |
| NO_PUSH_PRED |
The NO_PUSH_PRED hint prevents pushing of a join predicate into the view. |
| PUSH_SUBQ |
The PUSH_SUBQ hint causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan. |
| NO_PUSH_SUBQ |
The NO_PUSH_SUBQ hint causes non-merged subqueries to be evaluated as the last step in the execution plan. |
| QB_NAME |
Specifies a name for a query block. (from Oracle 10g) |
| CURSOR_SHARING_EXACT |
Oracle can replace literals in SQL statements with bind variables, if it is safe to do so. This is controlled with the CURSOR_SHARING startup parameter. The CURSOR_SHARING_EXACT hint causes this behavior to be switched off. In other words, Oracle executes the SQL statement without any attempt to replace literals by bind variables. |
| DRIVING_SITE |
The DRIVING_SITE hint forces query execution to be done for the table at a different site than that selected by Oracle |
| DYNAMIC_SAMPLING |
The DYNAMIC_SAMPLING hint lets you control dynamic sampling to improve server performance by determining more accurate predicate selectivity and statistics for tables and indexes. You can set the value of DYNAMIC_SAMPLING to a value from 0 to 10. The higher the level, the more effort the compiler puts into dynamic sampling and the more broadly it is applied. Sampling defaults to cursor level unless you specify a table. |
| SPREAD_MIN_ANALYSIS |
This hint omits some of the compile time optimizations of the rules, mainly detailed dependency graph analysis, on spreadsheets. Some optimizations such as creating filters to selectively populate spreadsheet access structures and limited rule pruning are still used. (from Oracle 10g) |
|
Hints with unknown status |
| MERGE_AJ |
The MERGE_AJ hint transforms a NOT IN subquery into a merge anti-join to access the specified table. The syntax of the MERGE_AJ hint is MERGE_AJ(table) where table specifies the name or alias of the table to be accessed.(depricated in Oracle 10g) |
| 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. The syntax of the AND_EQUAL hint is AND_EQUAL(table index index) where table specifies the name or alias of the table associated with the indexes to be merged. and index specifies an index on which an index scan is to be performed. You must specify at least two indexes. You cannot specify more than five. (depricated in Oracle 10g) |
| STAR |
The STAR hint forces the large table to be joined last using a nested loops join on the index. The optimizer will consider different permutations of the small tables. (depricated in Oracle 10g) |
| BITMAP |
Usage: BITMAP(table_name index_name) Uses a bitmap index to access the table. (depricated ?) |
HASH_SJ
|
Use a Hash Anti-Join to evaluate a NOT IN sub-query. Use this hint in the sub-query, not in the main query. Use this when your high volume NOT IN sub-query is using a FILTER or NESTED LOOPS join. Try MERGE_AJ if HASH_AJ refuses to work.(depricated in Oracle 10g) |
| NL_SJ |
Use a Nested Loop in a sub-query. (depricated in Oracle 10g) |
| NL_AJ |
Use an anti-join in a sub-query. (depricated in Oracle 10g) |
| ORDERED_PREDICATES |
(depricated in Oracle 10g)
|
| EXPAND_GSET_TO_UNION |
(depricated in Oracle 10g) |
參考至：《讓Oracle跑得更快》譚懷遠(yuǎn)著