http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/HNTS/Default.aspx
与优化器相比，应用程序开发人员和最终用户更了解数据以及如何使用。Oracle 提供了一个称为 HINT 的方法让你可以告诉优化器 SQL 语句使用的方法。Oracle 建议不要用 HINT 作为控制 SQL 语句优化的主要方法。而是应该适当地重写 SQL 语句以获得更好的性能。
可以指定的 HINTS "提示（暗示）"：
- 一个 SQL 语句的优化方法
- 一个 SQL 语句基于代价方法的目标
- 语句访问一个表的访问路径
- 一个连接语句的连接顺序
- 一个连接语句的连接运算符
一个 SQL 语句只能有一个包含 HINTS 的备注。HINT 必须放在 SQL 语句的 SELECT、UPDATE 或 DELETE 关键字后。HINT 放在标记 /*+ 和 */ 之间。如下所示：
/*+ FULL(a) */
另一个方法是使用标记 --+。如果使用多个 HINT，那么必须用空格分隔它们。
下面例子在 SQL 语句中使用 HINT：
SELECT /*+ FULL(a) */
*
FROM EMP a
WHERE empno > 1;
注意，在这种情况下，我们使用 FULL HINT，Oracle 在 EMP 表上会发生全表扫描。另外，HINT 上也可以使用表名。其他 HINT 形式如下所示：
SELECT --+ FULL(emp)
*
FROM EMP a
WHERE empno > 1;
HINTS 使用
如果指定了错误的 HINT，那么 Oracle 将把 HINT 当作备注，在 SQL 优化期间，将忽略。你不会收到任何错误信息。
如果存在多个 HINT，那么 Oracle 将忽略那些语法错误的 HINT。
如果多个 HINT 间提供了冲突的优化请求，那么 Oracle 将都不选择，冲突的 HINT 将被忽略。
只有当使用基于代价的方法（CBO）时，优化器才会去识别 HINT。如果你在语句块中包含了一个 HINT，除了 RULE HINT，那么优化器将自动使用基于代价的方法。
表 1 HINTS 和其含义
| Hints | 含义 |
| ALL_ROWS | Uses the Cost Based Optimizer if tables or indexes are analyzed. You should set this option if you have a database that is used predominantly for batch processing such as a Data Warehouse. |
| FIRST_ROWS | Uses the Cost Based Optimizer if tables or indexes are analyzed. You should set this option if you have a database that is used predominantly for OLTP processing. |
| CHOOSE | If any of the tables or indexes in the statement are analyzed, use the Cost Based Optimizer otherwise use the Rule Based. |
| RULE | Use the rule based optimizer for this statement. |
| AND_EQUAL | Used to join single column indexes. You must specify at least 2 indexes. |
| APPEND | INTO table…causes the data being inserted to be placed at the end of the table. It does not use free space in the earlier blocks of the table. This is new with Oracle8. |
| CACHE | Places blocks read into the most recently used end of the buffer cache which will retain the data in the buffer cache longer. |
| CLUSTER |
Uses a cluster scan. Clusters store each child related to a parent in the same physical address. |
| CURSOR_SHARING_EXACT | Disables cursor sharing if it is enabled. |
| DRIVING_SITE | Forces query execution to be done at a different site. |
| DYNAMIC_SAMPLING | Allows you to control dynamic sampling. |
| EXPAND_GSET_TO_UNION | Used for queries containing grouping sets (such as queries with GROUP BY GROUPING SET or GROUP BY ROLLUP). The hint forces a query to be transformed into a corresponding query with UNION ALL of individual groupings. |
| FACT | Denotes that the hinted table is a fact table when the transformation is a star transformation. |
| FULL | Tells Oracle to perform a full table scan on the table, even if there is an index in place. If you have the degree of parallelism set, it may also cause the table to be read using parallel query processors. |
| HASH | Uses a hash scan to access the specified table. |
| HASH_AJ | Uses a hash anti-join to speed up NOT IN. |
| HASH_SJ | Uses a hash semi-join to speed up EXISTS. |
| INDEX | Informs the optimizer to use a specific index on the specified table. |
| INDEX_ASC | Informs the optimizer to use a specific index on the specified table. |
| INDEX_COMBINE | Informs the optimizer to use a specific index on the specified table. Used for bitmap indexes. |
| INDEX_DESC | Informs the optimizer to use a specific index on the specified table. |
| INDEX_FFS | Informs the optimizer to perform a fast full index scan instead of a full table scan. It is faster than a normal index scan. |
| INDEX_JOIN | Informs the optimizer to use an index join as the access path. |
| LEADING | The LEADING hint causes Oracle to use the specified table as the first table in the join order. An ORDERED hint will override this hint. |
| MERGE | Merge a view on a per-query basis. |
| MERGE_AJ | Causes NOT IN to be processed using a Merge Join. It is often significantly faster than standard NOT processing. |
| MERGE_SJ | Transforms a correlated EXISTS subquery into a merge semi-join to access the specified table. |
| NOAPPEND | INTO table…overrides APPEND, which is used by default with parallel inserts. |
| NOCACHE | Places the data into the least recently used end of the buffer cache, which is standard behavior. It also overrides the CACHE setting on a table. |
| NO_EXPAND | Prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or INLISTS in the WHERE clause. |
| NO_FACT | Denotes that the hinted table is not a fact table when the transformation is a star transformation. |
| NO_INDEX | Explicitly disallows a set of indexes for the specified table. |
| NO_MERGE | Causes Oracle not to merge views specified in the FROM clause. |
| NO_PUSH_PRED | Prevents a join predicate from being pushed into the view. |
| NO_PUSH_SUBQ | Delays evaluation of non-merged subqueries until the last step in the execution plan. |
| NOPARALLEL | Overrides the degree of parallelism against a table to run in non parallel mode. |
| NOPARALLEL_INDEX | Overrides a PARALLEL attribute setting on an index. |
| NO_PUSH_JOIN_PRED | Prevents pushing a join predicate into the view. |
| NO_UNNEST | Prevents unnesting for specific subquery blocks. |
| NOREWRITE | Disables query rewrite for the query block. |
| ORDERED | Causes the SQL to be driven by the tables in the order left to right. |
| ORDERED_PREDICATES | Causes the order of predicate evaluation to be preserved by the optimizer. |
| PARALLEL | Sets the number of parallel processors to scan a table. |
| PARALLEL_INDEX | Will use parallel query processes for fast full index scans for indexes which have PARALLEL set. |
| PQ_DISTRIBUTE | Improves parallel join operations. |
| PUSH_JOIN_PRED | Forces pushing a join predicate into the view. |
| PUSH_PRED | Forces a join predicate to be pushed into the view. |
| PUSH_SUBQ | Place this hint in a non merged subquery if the subquery performs little processing. |
| REWRITE | Use with or without a view list to select the materialized view to be used. |
| ROWID | Uses a table scan by rowid. |
| STAR |
Causes Oracle to merge the reference tables together and join them to the central table using a nested loop |
|
STAR_TRANSFORMATION |
Causes Oracle to use a star query. It does not always use cartesian product of the reference tables, unlike the STAR hint. |
| UNNEST | Merges nested subqueries into the body of the statement that contains them. The optimizr then considers them together as it evaluates access paths and joins. |
| USE_CONCAT | Causes all ORs in the statement to be transferred to UNION ALLs. |
| USE_HASH | Tables are joined to the row resulting from using a hash join. |
| USE_NL | Selects a row from one row and then returns the associated row from another table using an index. Use for OLTP. |
| USE_MERGE | Will sort each table and merge the rows together. Use for batch processing. |
RULE、CHOOSE、ALL_ROWS 和 FIRST_ROWS 可以通过 INIT.ora 文件 OPTIMIZER_MODE 参数应用在实例级别。如果在 INIT.ora 中设置，那么设置会应用到所有 SQL 语句。INIT.ORA 通常是应用 HINT 的首先方法，除非一个特定语句在基于规则优化下运行得很好，而在基于代价下是不可接受的。
CHOOSE 和 RULE 指定是否使用 CBO 或 RBO。正确指定的 HINT 将会覆盖优化器模式指定的模式。如果指定了一个优化方法，那么将使用该方法，而不管初始化参数 OPTIMIZER_MODE 或会话设置 OPTIMIZER_GOAL 是什么。即使一个表存在统计信息，而 HINT 指定为 CHOOSE，那么优化器将使用 CBO。如果没有可用的统计信息，优化器将使用 RBO。使用 RULE 会导致优化器忽略任何其他指定的 HINT。
ALL_ROWS 和 FIRST_ROWS 都使用 CBO。如果没有可用的统计信息，那么优化器将使用任何可用的存储信息。在使用 ALL_ROWS 或 FIRST_ROWS HINT 前，可以用 ANALYZE 命令提供统计信息。如果 HINT 还指定了一个访问路径或连接操作，那么将优先考虑 ALL_ROWS 和 FIRST_ROWS。
ALL_ROWS 关注用最少的总资源消耗获得最好的吞吐量。FIRST_ROWS 的目标是用最少的资源获得最好的响应时间来返回第一行数据。 DELETE 和 UPDATE 语句都会忽略 FIRST_ROWS。
如果所描述的访问方法需要的索引不存在，那么 HINT 将忽略。在 HINT 中指定的表与 SQL 语句相同。如果使用表别名，那么 HINT 中指定的表也必须使用表别名，而不能用表名。即使 FORM 子句指定了模式，也不能用模式名。
