Hint是Oracle数据库里SQL优化的终极手段，通常用于直接指定目标SQL的执行计划，它作为一种非常规的直接影响优化器，指定执行计划的手段。深入理解Hint，能明白Hint是如何影响优化器对于执行计划的选择的，进而用好Hint，能起到事半功倍的效果。

1 什么是Hint

Hint实际上是一种特殊的注释，它以一种固定的格式和位置出现在SQL语句的SQL文本中，可以影响优化器对于执行计划的选择，但这种影响不是强制性的，优化器在某些情况下也可能忽略目标SQL中的Hint,即使这个Hint在语法和语义上是有效的。如果目标SQL的SQL文本中出现了Hint，优化器在选择最终执行计划时会把Hint一并考虑进来，如果优化器判断这个Hint给出的建议是合适的，就会直接遵循这个Hint给出的建议，并据此选定执行计划。反之，则会忽略该Hint仍然采用原先的判断标准来选定执行计划。

 注：Oracle数据库里并不是所有的Hint都是针对优化器的，虽然大多数Hint都是，也就是说Hint并非只能影响优化器对于执行计划的选择，不是针对优化器的Hint包括但不限于如下所列

APPEND  控制INSERT语句是否能以直接路径插入的方式插入数据

MINITOR  控制被执行的目标SQL是否会被SQL Monitor监控

STATEMENT_QUEUING  控制目标SQL在执行时是否启用并行执行排队

GATHER_PLAN_STATISTICS 用于在目标SQL执行时收集一些额外的统计信息，比如每一个具体执行步骤的实际返回结果集的Cardinality,每一个具体执行步骤的实际执行时间，消耗的物理读，逻辑读等。

2 Hint是如何影响优化器解析目标SQL产生执行计划这个过程的每一步的？

• 影响目标SQL是否可以被查询改写，如NO_QUERY_TRANSFORMATION、   MERGE 、 UNNEST、 USE_CONCAT等
• 影响优化器对于执行路径的选择，如FULL、INDEX等。
• 影响优化器对表连接方式的选择，如USE_HASH、 USE_NL等。
• 影响优化器对于执行计划中执行步骤返回结果集（即Cardinality）的判断，如DYNAMIC_SAMPLING、CARDINALITY等。

3 Hint的用法

Hint以一种固定的格式和位置出现在SQL语句的SQL文本中，固定的格式是指Hint必须以如下的格式出现在SQL文本中，/*+ <具体的Hint内容> */ 

固定的位置是指Hint在SQL文本中必须紧随关键字SELECT、INSERT、UPDATE、DELETE或MERGE之后，总结起来，Hint应该以如下的方式出现在SQL文本中：

SELECT |  INSERT |  DELETE l  UPDATE |  MERGE /*+ <具体的Hint内容> */

4 常见的Hint

4.1 与优化器模式相关的Hint

 4.4.1 ALL_ROWS   

    ALL_ROWS是针对整个目标SQL的Hint，它的含义是让优化器启用CBO，而且在得到目标SQL的执行计划时会选择那些吞吐量最佳的执行路径，即IO，CPU等硬件资源的消耗量最小，也就是说在             ALL_ROWS生效的情况下，优化器会启用CBO而且会根据各个执行路径的资源消耗量来计算他们各自的成本。

 4.1.2 FIRST_ROWS(n)  

   FIRST_ROWS(n)是针对整个目标SQL的Hint，它的含义是让优化器启用Hint模式，而且在得到目标SQL的执行计划时会选择那些能以最快的响应时间返回头n条记录的执行路径，也就是说在FIRST_ROWS(n)    生效的情况下，优化器会启用CBO，而且会依据返回头n条记录的响应时间来决定目标SQL的执行计划。

 4.1.3 RULE

  RULE是针对整个目标SQL的Hint,它表示对目标SQL启用RULE优化器

4.2 与表访问相关的Hint

 4.2.1 FULL

   FULL是针对单个目标表的Hint，让优化器对目标表执行全表扫描

 4.2.2 ROWID

   ROWID是针对单个目标表的Hint，让优化器对目标表执行ROWID扫描

4.3 与索引访问相关的Hint

 4.3.1 INDEX

   INDEX是针对单个目标表的Hint，让优化器对目标表上的目标索引执行索引扫描操作

 4.3.2 NO_INDEX

   NO_INDEX是针对单个目标表的Hint，它是INDEX的反义，让优化器不对目标表上的目标索引执行索引扫描操作

 4.3.3 INDEX_DESC

    INDEX_DESC是针对单个目标表的Hint，让优化器对目标表上的目标索引执行索引降序扫描操作

 4.3.4 INDEX_COMBIN

    INDEX_COMBIN是针对单个目标表的Hint，让优化器对目标表上的多个目标索引执行位图布尔运算

 4.3.5 INDEX_FFS

    INDEX_FFS是针对单个目标表的Hint，让优化器对目标表上的目标索引执行快速全扫描操作

 4.3.6 INDEX_JOIN

    INDEX_JOIN是针对单个目标表的Hint，让优化器对目标表上的多个目标索引执行INDEX JOIN操作

 4.3.7 AND_EQUAL

    AND_EQUAL是针对单个目标表的Hint，让优化器对目标表上的多个目标索引执行INDEX MERGE操作

4.4 与表连接顺序相关的Hint

 4.4.1 ORDERED

    ORDERED是针对多个目标表的Hint，让优化器对多个目标表执行表连接操作时，按照它们在目标SQL的WHERE条件中出现的顺序  从左到右依次连接

 4.4.2 LEADING

    LEADING时针对多个目标表的Hint，让优化器将我们指定的多个表的连接结果作为目标SQL表连接过程中的驱动结果集，并且将LEADING中从左至右出现的第一个目标表作为整个表连接过程中的首个驱动表

4.5 与表连接方法相关的Hint

 4.5.1 USE_MERGE

    USE_MERGE是针对多个目标表的Hint，让优化器将我们指定的多个目标表作为驱动板与其它表或结果集做排序合并连接

 4.5.2 NO_USE_MERGE 

    NO_USE_MERGE是针对多个目标表的Hint，是USE_MERGE的反义，不让优化器将我们指定的多个目标表作为驱动板与其它表或结果集做排序合并连接

 4.5.3 USE_NL

    USE_NL是针对多个目标表的Hint，让优化器将我们指定的多个目标表作为被驱动表与其它表或结果集做嵌套循环连接

 4 .5.4 NO_USE_NL

    NO_USE_NL是针对多个目标表的Hint，是USE_NL的反义，不让优化器将我们指定的多个目标表作为被驱动表与其它表或结果集做嵌套循环连接

 4.5.5 USE_HASH

    USE_HASH是针对多个目标表的Hint，让优化器将我们指定的多个目标表作为被驱动板与其它表或结果集做哈希连接

 4.5.6 NO_USE_HASH

    NO_USE_HASH是针对多个目标表的Hint，他是USE_HASH的反义，不让优化器将我们指定的多个目标表作为被驱动板与其它表或结果集做哈希连接

 4.5.7 MERGE_AJ

    MERGE_AJ是针对子查询的Hint，让优化器对相关目标表执行排序合并连接

 4.5.8 NL_AJ

    NL_AJ是针对子查询的Hint，让优化器对相关目标表执行嵌套循环反链接

 4.5.9 HASH_AJ

    HASH_AJ是针对子查询的Hint，让优化器对相关目标表执行哈希反连接

 4.5.10 MERGE_SJ

    MERGE_SJ是针对子查询的Hint，让优化器对相关目标表执行排序合并半连接

 4.5.11 NL_SJ

    NL_SJ是针对子查询的Hint，让优化器对相关目标表执行嵌套循环半连接

 4.5.12 HASH_SJ

    HASH_SJ是针对子查询的Hint，让优化器对相关目标表执行哈希半连接

4.6 与查询转换相关的Hint

 4.6.1 USE_CONCAT

    USE_CONCAT是针对整个目标SQL的Hint，让优化器对目标SQL使用IN-LIST扩展或OR扩展

 4.6.2 NO_EXPAND

    NO_EXPAND是针对整个目标SQL的Hint，是USE_CONCAT的反义，不让优化器对目标SQL使用IN-LIST扩展或OR扩展

 4.6.3 MERGE

    MERGE是针对单个目标视图的Hint，让优化器对目标视图执行视图合并

 4.6.4 NO_MERGE

    NO_MERGE是针对单个目标视图的Hint，是MERGE的反义，不让优化器对目标视图执行视图合并

 4.6.5 UNNEST

    UNNEST是针对子查询的Hint，让优化器对目标SQL中的子查询执行子查询展开

 4.6.6 NO_UNNEST

    NO_UNNEST是针对子查询的Hint，是UNNEST的反义，不让优化器对目标SQL中的子查询执行子查询展开

 4.6.7 EXPAND_TABLE

    EXPAND_TABLE是针对单个目标表的Hint，让优化器在不考虑成本的情况下对目标SQL中的目标表执行表扩展

 4.6.8 NO_EXPAND_TABLE

    NO_EXPAND_TABLE是针对单个目标表的Hint，不让优化器在不考虑成本的情况下对目标SQL中的目标表执行表扩展

4.7 与并行相关的Hint

 4.7.1 PARALLEL

    PARALLEL是针对整个目标SQL的Hint,让优化器以指定的或者系统计算出来的并行度去并行访问目标表

 4.7.2 NO_PARALLEL

    NO_PARALLEL是针对整个目标表的Hint，PARALLEL的反义，不让优化器以指定的或者系统计算出来的并行度去并行访问目标表

 4.7.3 PARALLEL_INDEX

    PARALLEL_INDEX是针对单个目标表的Hint，让优化器以指定的或者系统计算出来的并行度去对目标表上的目标分区索引执行并行扫描操作

 4.7.4 NO_PARALLEL_INDEX

    NO_PARALLEL_INDEX是针对单个目标表的Hint，PARALLEL_INDEX的反义，不让优化器以指定的或者系统计算出来的并行度去对目标表上的目标分区索引执行并行扫描操作

4.8 其它常见Hint

 4.8.1 DRIVING_SITE

    DRIVING_SITES是针对单个目标表的Hint，让优化器在指定的目标表所在的节点上执行目标SQL，适用于带dblink的分布式查询语句

 4.8.2 APPEND

    APPEND是针对整个目标SQL的Hint，优化器在执行带子查询的INSERT语句（即形如INSERT INTO XXX SELECT * FROM XXX的INSERT语句）时绕开Buffer Cache，使用直接路径插入。

    默认情况下Oracle执行INSERT语句时使用的是常规插入，这时相关的数据块会缓存在Buffer Cache中。而对于直接路径插入，Oracle在插入数据时会直接在目标表的高水位线以上插入数据（最后在抬高高水位线），而不是像常规插入那样先去目标表的高水位线以下去寻找是否存在能插入数据的数据块，这样就省去了寻找合适的数据块的开销。另外，由于直接路径插入不需要在Buffer Cache中缓存相关的数据块，所以也省去了缓存相关数据块的开销。最后在非归档模式，或者归档模式但表是NOLOGGING时，使用APPEND在目标表插入数据时不会产生redo，这样就省去了产生redo这部分工作的开销（并不是完全不产生redo，只是对表的数据块的数据部分的更改不会有redo产生，但是SQL语句执行过程中对数据字典的更改，空间分配等递归SQL语句，段头和位图块的更改等操作还是会产生少量redo）。基于以上种种原因使用APPEND Hint的直接路径插入通常会比常规插入的速度快很多。

 注：当使用APPEND以直接路径插入对目标表INSERT数据时，Oracle会在目标表上加上排它锁，意味着插入数据时其他人无法对目标表做任何DML操作

 4.8.3  APPEND_VALUES

    APPEND_VALUES时针对整个目标SQL的Hint，让优化器在执行带VALUES的INSERT语句（即形如INSERT INTO XXX VALUES(..........)的INSERT语句）时绕开Buffer Cache使用直接路径插入

  注：APPEND_VALUES适用于一次插入一批数据的场景，比如（PL/SQL的FORALL子句）不可将其应用与单个的INSERT语句，否则可能造成存储空间的严重浪费

 4.8.4 PUSH_PRED

    PUSH_PRED是针对单个目标视图的Hint，让优化器对目标视图执行连接谓词推入，即将目标SQL中处于该视图定义SQL语句外部的谓词连接条件推入到该视图定义SQL语句中

 4.8.5 NO_PUSH_PRED

    NO_PUSH_PRED是针对单个目标视图的Hint，是PUSH_PRED的反义，让优化器不对目标视图执行连接谓词推入，即不让优化器将目标SQL中处于该视图定义SQL语句外部的谓词连接条件推入到该视图定义SQL语句中

 4.8.6 PUSH_SUBQ

     PUSH_SUBQ是针对子查询的Hint，让优化器尽可能早地执行目标SQL中不能做子查询展开的子查询。通常情况下，目标SQL中不能做子查询展开的子查询总是在其执行计划的最后一步才被执行，但如果执行这个子查询之后能显著减少返回结果集的数量，则先执行这个子查询就有可能提高该SQL的执行效率

 4.8.7 NO_PUSH_SUBQ

    NO_PUSH_SUBQ是针对子查询的Hint，它是PUSH_SUBQ的反义，让优化器最后执行目标SQL中不能做子查询展开的子查询

 4.8.8 OPT_PARAM

    OPT_PARAM是针对整个目标SQL的Hint，可以用它来修改针对目标SQL的与优化器相关的一些参数，其好处在于它所修改的参数仅对它所在的目标SQL有效，这是比在系统级和Session级修改相关参数更细   的一个粒度

 4.8.9 OPTIMIZER_FEATURES_ENABLE

    OPTIMIZER_FEATURES_ENABLE是针对整个目标SQL的Hint，可以用它来修改针对目标SQL的优化器版本， OPTIMIZER_FEATURES_ENABLE实际上相当于针对目标SQL修改 OPTIMIZER_FEATURES_ENABLE的值，好处在于它只对它所在的目标SQL有效，这是比在系统级和session级修改 OPTIMIZER_FEATURES_ENABLE参数更细的一个粒度，用 OPTIMIZER_FEATURES_ENABLE可以很容易将解析目标SQL的优化器回退到之前的版本，这样就可以保证该SQL执行计划的稳定性，从而规避由于数据块升级后新版本优化器的一些新特性和新功能可能带来的对该目标SQL执行计划的不利影响

 4.8.10 QB_NAME

    QB_NAME是针对整个目标SQL的Hint，可以用此Hint来对一个Query Block指定自定义的名称。一旦用QB_NAME指定了自定义的Query Block名称，则这个名称在整个SQL的范围内都是可用的，所以，用QB_NAME就可以将一些本该在子查询或者内嵌视图中使用的Hint挪到外部使用

 4.8.11 CARDINALITY

     CARDINALITY是针对单个目标表的Hint，它可以用来设置对目标表执行扫描操作后返回结果集的Cardinality值，它能够设置的扫描类型包括全表扫描，索引范围扫描，索引全扫描，索引快速全扫描

   注：CARDINALITY对索引唯一扫描而言是无效的

 4.8.12 SWAP_JOIN_INPUTS

    SWAP_JOIN_INPUTS是针对哈希连接的Hint，让优化器交换原哈希连接的驱动表和被驱动表的顺序，即在依然走哈希连接的情况下让原哈希连接的驱动表变为被驱动表，让原哈希连接的被驱动表变为驱动表