Oracle Hint

Hint 是Oracle 提供的一种SQL语法，它允许用户在SQL语句中插入相关的语法，从而影响SQL的执行方式。

因为Hint的特殊作用，所以对于开发人员不应该在代码中使用它，Hint 更像是Oracle提供给DBA用来分析问题的工具 。在SQL代码中使用Hint，可能导致非常严重的后果，因为数据库的数据是变化的，在某一时刻使用这个执行计划是最优的，在另一个时刻，却可能很差，这也是CBO 取代RBO的原因之一，规则是死的，而数据是时刻变化的，为了获得最正确的执行计划，只有知道表中数据的实际情况，通过计算各种执行计划的成本，则其最优，才是最科学的，这也是CBO的工作机制。 在SQL代码中加入Hint，特别是性能相关的Hint是很危险的做法。

Hints

Hints are comments in a SQL statement that pass instructions to the Oracle Database optimizer. The optimizer uses these hints to choose an execution plan for the statement, unless some condition exists that prevents the optimizer from doing so.

Hints were introduced in Oracle7, when users had little recourse if the optimizer generated suboptimal plans. Now Oracle provides a number of tools, including the SQL Tuning Advisor, SQL plan management, and SQL Performance Analyzer, to help you address performance problems that are not solved by the optimizer. Oracle strongly recommends that you use those tools rather than hints. The tools are far superior to hints, because when used on an ongoing basis, they provide fresh solutions as your data and database environment change.

Hints should be used sparingly, and only after you have collected statistics on the relevant tables and evaluated the optimizer plan without hints using the EXPLAIN PLAN statement. Changing database conditions as well as query performance enhancements in subsequent releases can have significant impact on how hints in your code affect performance.

The remainder of this section provides information on some commonly used hints. If you decide to use hints rather than the more advanced tuning tools, be aware that any short-term benefit resulting from the use of hints may not continue to result in improved performance over the long term.

Oracle 联机文档对Hint的说明：

http://download.oracle.com/docs/cd/E11882_01/server.112/e10592/sql_elements006.htm#SQLRF50705

之前整理的一篇文章：

常见Oracle HINT的用法

http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4969702.aspx

在使用Hint时需要注意的一点是，并非任何时刻Hint都起作用。 导致HINT 失效的原因有如下2点：

（1） 如果CBO 认为使用Hint 会导致错误的结果时，Hint将被忽略。

如索引中的记录因为空值而和表的记录不一致时，结果就是错误的，会忽略hint。

（2） 如果表中指定了别名，那么Hint中也必须使用别名，否则Hint也会忽略。

Select /*+full(a)*/ * from t a; -- 使用hint

Select /*+full(t) */ * from t a; --不使用hint

根据hint的功能，可以分成如下几类：

Hint	Hint 语法
优化器模式提示	ALL_ROWS Hint
	FIRST_ROWS Hint
	RULE Hint

访问路径提示	CLUSTER Hint
	FULL Hint
	HASH Hint
	INDEX Hint
	NO_INDEX Hint
	INDEX_ASC Hint
	INDEX_DESC Hint
	INDEX_COMBINE Hint
	INDEX_FFS Hint
	INDEX_SS Hint
	INDEX_SS_ASC Hint
	INDEX_SS_DESC Hint
	NO_INDEX_FFS Hint
	NO_INDEX_SS Hint
	ORDERED Hint
	LEADING Hint
	USE_HASH Hint
	NO_USE_HASH Hint
表连接顺序提示	USE_MERGE Hint
	NO_USE_MERGE Hint
	USE_NL Hint
	USE_NL_WITH_INDEX Hint
	NO_USE_NL Hint
表关联方式提示	PARALLEL Hint
	NO_PARALLEL Hint
	PARALLEL_INDEX Hint
	NO_PARALLEL_INDEX Hint
	PQ_DISTRIBUTE Hint
并行执行提示	FACT Hint
	NO_FACT Hint
	MERGE Hint
	NO_MERGE Hint
	NO_EXPAND Hint
	USE_CONCAT Hint
查询转换提示	REWRITE Hint
	NO_REWRITE Hint
	UNNEST Hint
	NO_UNNEST Hint
	STAR_TRANSFORMATION Hint
	NO_STAR_TRANSFORMATION Hint
	NO_QUERY_TRANSFORMATION Hint
	APPEND Hint
	NOAPPEND Hint
	CACHE Hint
	NOCACHE Hint
	CURSOR_SHARING_EXACT Hint
其他Hint	DRIVING_SITE Hint
	DYNAMIC_SAMPLING Hint
	PUSH_PRED Hint
	NO_PUSH_PRED Hint
	PUSH_SUBQ Hint
	NO_PUSH_SUBQ Hint
	PX_JOIN_FILTER Hint
	NO_PX_JOIN_FILTER Hint
	NO_XML_QUERY_REWRITE Hint
	QB_NAME Hint
	MODEL_MIN_ANALYSIS Hint

 

转自：http://www.cnblogs.com/ebs-blog/archive/2011/09/05/2167730.html