Oracle ——数据库 Hints

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 子句指定了模式，也不能用模式名。