常见OracleHINT的⽤法
Hint概述
基于代价的优化器是很聪明的，在绝⼤多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执⾏计划，使某个语句的执⾏变得奇慢⽆⽐。
此时就需要DBA进⾏⼈为的⼲预，告诉优化器使⽤我们指定的存取路径或连接类型⽣成执⾏计划，从⽽使语句⾼效的运⾏。例如，如果我们认为对于⼀个特定的语句，执⾏全表扫描要⽐执⾏索引扫描更有效，则我们就可以指⽰优化器使⽤全表扫描。在Oracle 中，是通过为语句添加 Hints(提⽰)来实现⼲预优化器优化的⽬的。
虎妈战歌
不建议在代码中使⽤hint，在代码使⽤hint使得CBO⽆法根据实际的数据状态选择正确的执⾏计划。毕竟数据是不断变化的， 10g以后的CBO也越来越完善，⼤多数情况下我们该让Oracle⾃⾏决定采⽤什么执⾏计划。
Oracle Hints是⼀种机制，⽤来告诉优化器按照我们的告诉它的⽅式⽣成执⾏计划。我们可以⽤Oracle Hints来实现：
1) 使⽤的优化器的类型
2) 基于代价的优化器的优化⽬标，是all_rows还是first_rows。
3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利⽤rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并⾏程度
除了”RULE”提⽰外，⼀旦使⽤的别的提⽰，语句就会⾃动的改为使⽤CBO优化器，此时如果你的数据字典中没有统计数据，就会使⽤缺省的统计数据。所以建议⼤家如果使⽤CBO或Hints提⽰，则最好对表和索引进⾏定期的分析。borneo
如何使⽤Hints:
Hints只应⽤在它们所在sql语句块(statement block，由lect、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使⽤union操作的2个sql语句，如果只在⼀个sql语句上有Hints，则该Hints不会影响另⼀个sql语句。
我们可以使⽤注释(comment)来为⼀个语句添加Hints，⼀个语句块只能有⼀个注释，⽽且注释只能放在SELECT, UPDATE, or DELETE关键字的后⾯
使⽤Oracle Hints的语法：
{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...
注解：
1) DELETE、INSERT、SELECT和UPDATE是标识⼀个语句块开始的关键字，包含提⽰的注释只能出现在这些关键字的后⾯，否则提⽰⽆效。
2) “+”号表⽰该注释是⼀个Hints，该加号必须⽴即跟在”/*”的后⾯，中间不能有空格。
3) hint是下⾯介绍的具体提⽰之⼀，如果包含多个提⽰，则每个提⽰之间需要⽤⼀个或多个空格隔开。
4) text 是其它说明hint的注释性⽂本
5)使⽤表别名。如果在查询中指定了表别名，那么提⽰必须也使⽤表别名。例如：lect /*+ index(e,dept_idx) */ * from emp e;
6)不要在提⽰中使⽤模式名称：如果在提⽰中指定了模式的所有者，那么提⽰将被忽略。例如：
lect /*+ p,dept_idx) */ * from emp
注意：如果你没有正确的指定Hints，Oracle将忽略该Hints，并且不会给出任何错误。
hint被忽略
如果CBO认为使⽤hint会导致错误的结果时，hint将被忽略，详见下例
SQL> lect /*+ 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 |
-------------------------------------------------------------------
因为我们是对记录求总数，且我们并没有在建⽴索引时指定不能为空，索引如果CBO选择在索引上进⾏count时，但索引字段上的值为空时，结果将不准确，故CBO没有选择索引。
SQL> lect /*+ 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 |
--------------------------------------------------------------------------
因为我们只对id进⾏count，这个动作相当于count索引上的所有id值，这个操作和对表上的id字段进⾏count是⼀样的(组函数会忽略null值)
Hint的具体⽤法
和优化器相关的hint
1、/*+ ALL_ROWS */
表明对语句块选择基于开销的优化⽅法，并获得最佳吞吐量，使资源消耗最⼩化.
SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2、/*+ FIRST_ROWS(n) */
表明对语句块选择基于开销的优化⽅法，并获得最佳响应时间，使资源消耗最⼩化.
SELECT /*+FIRST_ROWS(20) */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3、/*+ RULE*/
表明对语句块选择基于规则的优化⽅法.
rmax
wellcomeSELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
和访问路径相关的hint
1、/*+ FULL(TABLE)*/
表明对表选择全局扫描的⽅法.
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
2、/*+ INDEX(TABLE INDEX_NAME) */
表明对表选择索引的扫描⽅法.
SELECT /*+INDEX(BSEMPMS SEX_INDEX) */ * FROM BSEMPMS WHERE SEX='M';
5、/*+ INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描⽅法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';
6、/*+ INDEX_COMBINE*/
为指定表选择位图访问路经，如果INDEX_COMBINE中没有提供作为参数的索引，将选择出位图索引的布尔组合⽅式.
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE
7、/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */
当谓词中引⽤的列都有索引的时候，可以通过指定采⽤索引关联的⽅式，来访问数据
lect /*+ index_join(t t_ind t_bm) */ id from t where id=100 and object_name='EMPLOYEES'
8、/*+ INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描⽅法.
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';
9、/*+ INDEX_FFS(TABLE INDEX_NAME) */
对指定的表执⾏快速全索引扫描,⽽不是全表扫描的办法.
SELECT /* + INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';
10、/*+ INDEX_SS(T T_IND) */
从9i开始，oracle引⼊了这种索引访问⽅式。当在⼀个联合索引中，某些谓词条件并不在联合索引的第⼀列时，可以通过Index Skip Scan来访问索引获得数据。当联合索引第⼀列的唯⼀值个数很少时，使⽤这种⽅式⽐全表扫描效率⾼。
SQL> create table t as lect 1 id,object_name from dba_objects;
Table created.
SQL> inrt into t lect 2,object_name from dba_objects;
50366 rows created.
SQL> inrt into t lect 3,object_name from dba_objects;
50366 rows created.
SQL> inrt into t lect 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.
执⾏全表扫描
SQL> lect /*+ full(t) */ * from t where object_name='EMPLOYEES';
6 rows lected.
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 nt 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 procesd
不采⽤hint
SQL> lect * from t where object_name='EMPLOYEES';
teachers
6 rows lected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2869677071
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
punish
sixt| 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 nt 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 procesd
当全表扫描扫描了942个块，联合索引只扫描了17个数据块。可以看到联合索引的第⼀个字段的值重复率很⾼时，即使谓词中没有联合索引的第⼀个字段，依然会使⽤index_ss⽅式，效率远远⾼于全表扫描效率。但当第⼀个字段的值重复率很低时，使⽤ index_ss的效率要低
于全表扫描，读者可以⾃⾏实验
和表的关联相关的hint
/*+ leading(table_1,table_2) */
在多表关联查询中，指定哪个表作为驱动表，即告诉优化器⾸先要访问哪个表上的数据。
lect /*+ leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ order */
让Oracle根据from后⾯表的顺序来选择驱动表，oracle建议使⽤leading，他更为灵活
lect /*+ order */ t.* from t,t1 where t.id=t1.id;
/*+ u_nl(table_1,table_2) */
在多表关联查询中，指定使⽤nest loops⽅式进⾏多表关联。
lect /*+ u_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ u_hash(table_1,table_2) */
在多表关联查询中，指定使⽤hash join⽅式进⾏多表关联。
lect /*+ u_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;
在多表关联查询中，指定使⽤hash join⽅式进⾏多表关联，并指定表t为驱动表。lect /*+ u_hash(t,t1) leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ u_merge(table_1,table_2) */
在多表关联查询中，指定使⽤merge join⽅式进⾏多表关联。
lect /*+ u_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_u_nl(table_1,table_2) */
在多表关联查询中，指定不使⽤nest loops⽅式进⾏多表关联。
lect /*+ no_u_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_u_hash(table_1,table_2) */
在多表关联查询中，指定不使⽤hash join⽅式进⾏多表关联。
lect /*+ no_u_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ no_u_merge(table_1,table_2) */
在多表关联查询中，指定不使⽤merge join⽅式进⾏多表关联。
lect /*+ no_u_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;
其他常⽤的hint
/*+ parallel(table_name n) */
在sql中指定执⾏的并⾏度，这个值将会覆盖⾃⾝的并⾏度
lect /*+ parallel(t 4) */ count(*) from t;
/
*+ no_parallel(table_name) */
在sql中指定执⾏的不使⽤并⾏
lect /*+ no_parallel(t) */ count(*) from t;
/*+ append */以直接加载的⽅式将数据加载⼊库
inrt into t /*+ append */ lect * from t;
/*+ dynamic_sampling(table_name n) */
设置sql执⾏时动态采⽤的级别，这个级别为0~10
lect /*+ dynamic_sampling(t 4) */ * from t where id > 1234
世界大学排名2012
/*+ cache(table_name) */
进⾏全表扫描时将table置于LRU列表的最活跃端，类似于table的cache属性lect /*+ full(employees) cache(employees) */ last_name from employees
附录hint表格
Hints for Optimization Approaches
and Goals
ALL_ROWS The ALL_ROWS hint explicitly choos the cost-bad approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
The FIRST_ROWS hint explicitly choos the cost-bad approach to optimize a statement block with a goal of best respon time (minimum resource usage to return first row). In newer Oracle
FIRST_ROWS version you should give a parameter with this hint: FIRST_ROWS(n) means that the optimizer will
determine an executionplan to give a fast respon for returning the first n rows.
CHOOSE The CHOOSE hint caus the optimizer to choo between the rule-bad approach and the cost-bad approach for a SQL statement bad on the prence of statistics for the tables accesd by the statement
RULE The RULE hint explicitly choos rule-bad optimization for a statement block. This hint also
caus 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 choos 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 choos 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 choos 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 accesd by a cluster scan.
HASH The HASH hint explicitly choos 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 accesd 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 accesd.(depricated in Oracle 10g)
cartoon是什么意思INDEX The INDEX hint explicitly choos 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 t 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 choos an index scan for the specified table. If the statement us 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 u 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 u some boolean combi
nation of tho particular bitmap indexes. The syntax of INDEX_COMBINE is INDEX_COMBINE(table index).
INDEX_JOIN Explicitly instructs the optimizer to u 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 choos an index scan for the specified table. If the statement us an index range scan, Oracle scans the index entries in descending order of their indexed values.
INDEX_FFS This hint caus a fast full index scan to be performed rather than a full table. NO_INDEX_FFS Do not u 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 caus 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 clau of a query to be transformed into a compound query using the UNION ALL t 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 clau. Usually, the optimizer considers using OR expansion and us this method if it decides that the cost is lower than not using it.
