oracle hint 简单说明

1.1          hint

根据Merriam-Webster在线字典，hint是一个间接或概要的建议。在Oracle的术语中，hint的定义稍有不同。简单地说，hint是添加到SQL语句中的指令，用来影响查询优化器的判定。换句话说，hint不是仅仅建议某个动作，而是向着该动作推进。在我看来，Oracle选择这个词来命名此功能并不是最佳选择。无论如何，名称并不重要，hint能为你做的才是重要的。不要让名称误导你。

警告 仅因为hint是一个指令，并不代表查询优化器就总是会使用它。或者反过来说，仅因为查询优化器不使用hint,并不代表hint仅仅是一个建议。就像我稍后将介绍的，有些案例里，hint只是不相关或不合法，因此不会影响查询优化器生成的执行计划。

1.1.1      工作原理

1.1.1.1   什么是hint

当处理一条SQL语句时，查询优化器会考虑许多种执行计划。理论上，它会考虑所有可行的执行计划。实际上，除了简单的SQL语句之外，优化器为了保持合理的优化时间，不会考虑太多种组合。因此，查询优化器会根据推断排除某些执行计划。当然，完全忽略一些执行计划的决定很关键，并且这么做查询优化器的可信性也会受到怀疑。

指定一个hint时，你的目的要么是改变执行环境，启用或者禁用某个特性，要么是降低查询优化器需要考虑的执行计划数量。除非改变执行环境，使用hint你将告诉查询优化器，针对某条特定SQL语句应该考虑哪些操作或不应该考虑哪些操作。例如，查询优化器要为以下查询生成执行计划：

select * FROM emp WHERE empno  =  7788;

如果emp表是堆表并且empno列有索引，那么查询优化器至少考虑两种执行计划。第一种通过全表扫描彻底读了一遍emp表：

第二种是基于WHERE子句(empno=7788)的谓词做一次索引查找，然后通过在索引里找到的rowid去访问表中的数据：

在这样的案例里，要控制查询优化器提供的执行计划，你可以加入hint来指定使用全表扫描或者索引扫描。重要的是需要明白你不能告诉查询优化器，“我想要在emo表上执行全表扫描，所以去搜一个包含它的执行计划"。然而，你可以告诉它，“如果需要在对emp表执行全表扫描还是索引扫描之间做出选择，请选择全表扫描"。这是一个轻量的但本质上的不同。当查询优化器必须在几个可能的执行计划间选择时，hint可以允许你影响它的选择。

只有在查询优化器决定了应用hint的选择后才会对它做评估。因此，一旦指定了一个hint,你或许会被迫加入几个hint来确保它正常工作。并且在实践中，随着执行计划复杂度的增加，想要找到所有可用的hint来获得想要的执行计划会变得越来越困难。

1.1.1.2   指定hint

hint是Oracle的扩展。为了不影响SQL语句与其他数据库引擎的兼容性，Oracle决定把它们作为一种特殊的注释来加入。注释与hint仅有的不同如下所示。

Ø  hint必须紧随DELETE、INSERT、MERGE、select和UPDATE关键字。换句话说，它们不能像注释那样指定在SQL语句的任意位置。

Ø  注释分隔符的第一个字符必须是加号(+)。

一般而言，hint的语法错误不会引发报错。如果解析器无法解析它们，就会把它们当作注释。有时，注释与hint混合同样可行。下面的两个例子展示了如何使用上一节介绍的查询强制在emp表上执行全表扫描：

select /*+ full(emp) */ * FROM emp WHERE empno=7788;

select /*+ full(emp) you can add a real comment after the hint */ * FROM emp WHERE empno=7788;

然而，混合注释与hint并不总是可行的。例如，注释加在hint前面就会使hint失效。以下查询展示了这样的案例：

select /*+ but this one does not work full(emp) */ * FROM emp WHERE empno=7788;

因为注释能使hint失效，所以不建议将注释与hint混合使用。最好是分开它们。

1.1.1.3   hint的类别

划分hint的类别有好几种方法(观点)。个人而言，我喜欢按以下类别对它们进行分组。

Ø  初始化参数hint(initialization parameter hint)会重写一些在系统或会话级别定义的初始化参数的设置。我将以下hint划分在这个类别里：all_rows、cursor_sharing_exact、dynamic_sampling、first_rows、gather_plan_statistics、optimizer_features_enable和opt_param。请注意，当指定这些hint时，它们总是会重写实例或会话级别的值。

Ø  查询转换hint(query transformation hint)控制着逻辑优化期间查询转换技术的利用率。我将以下hint划分在这个类别里：(no_)eliminate_join、no_expand、(no_)expand_table、(no_)fact、(no_)merge、(no_)outer_join_to_inner、(no_)rewrite、(no_)star_transformation、(no_)unnest、no_xmlindex_rewrite、no_xml_query_rewrite和use_concat。

Ø  访问路径hint(access path hint)控制着用来访问数据的方法(例如，是否使用索引)。我将以下hint划分在这个类别里：cluster、full、hash、(no_)index、index_asc、index_combine、index_desc、(no_)index_ffs、index_join、(no_)index_ss、index_ss_asc和index_ss_desc。

Ø  联接hint(join_hint)不仅控制着联接方法，也包含用来联接表的顺序。我将以下hint划分在这个类别里：leading、(no_)nlj_batching、ordered、(no_)swap_join_inputs、(no_)use_cube、_(no_)use_hash、(no_)use_merge、use_merge_cartesian、(no_)use_nl和use_nl_with_index。

Ø  并行处理hint(parallel processing hint)控制如何使用以及是否使用并行处理。我将以下hint划分在这个类别里：(no_)parallel、(no_)parallel_index、(no_)pq_concurrent_union、pq_distribute、pq_filter、(no_)pq_skew、(no_)px_join_filter和(no_)statement_queuing。

Ø  其他hint控制着不属于上面任何类别的其他特性。我将以下hint划分在这个类别里：(no_)append_、append_values_、(no_)bind_aware_、(no_)result_cache、(no_)cache、change_dupkey_error_index_、driving_site、(no_)gather_optimizer_statistics_、ignore_row_on_dupkey_index、inline、materialized、(no_)monitor、model_min_analysis、(no_)monitor、qb_name和retry_on_row_change。

真实的参考或它们完整的语法。此类参考在Oracle Database SQL Lanaguage Reference手册的第2章中提供。

值得指出的是，存在大量hint会禁用某个特殊操作或特性(no_前缀的hint)。好处是有时指定某些操作或特性不可使用要更容易。

从11.1版本起，可以查询v$sql_hint视图来获取接近完整的hint列表。

1.1.1.4   hint的有效性

简单的SQL语句只有单个查询块。当使用视图或集合时才会存在多个查询块，如子查询、内敛视图和集合运算。例如，以下查询有两个查询块(仅仅出于演示的目的，我使用子查询来替代一个真实的视图)。第一个查询块是引用dept表的主查询。第二个是引用emp表的子查询：

WITH emps AS (select deptno, count(*) AS cnt FROM emp GROUP BY deptno)

select dept.dname,emps.cnt

FROM dept,emps

WHERE dept.deptno = emps.deptno;

通常情况下，初始化参数hint对整个SQL语句都有效(dynamic_sampling是个例外)。其他大多数hint仅对单个查询块有效(有两个例外，bind_aware和monitor)。

对单个查询块有效的hint必须指定在它们控制的块内。例如，如果想让上个查询里的两张表都指定访问路径hint,那么一个hint需要加在主查询里，另一个需要加在子查询里。它们的有效性仅限于它们定义的查询块中：

WITH emps AS (select /*+ full(emp) */ deptno, count(*) AS cnt FROM emp GROUP BY deptno)

select /*+ full(dept) */ dept.dname,emps.cnt

FROM dept,emps

WHERE dept.deptno = emps.deptno;

这条规则的例外是全局hint(global hint)。使用全局hint时，有可能通过使用点记法引用包含在其他查询块中的对象(如果已命名它们)。例如，下面的SQL语句，主查询包含作用于子查询的hint。请注意子查询对引用名称的使用：

WITH emps AS (select  deptno, count(*) AS cnt FROM emp GROUP BY deptno)

select /*+ full(dept) full(emps.emp) */ dept.dname,emps.cnt

FROM dept,emps

WHERE dept.deptno = emps.deptno;

全局hint的语法支持超过两层级别的引用(例如，一个视图引用自另一个视图)。对象必须要用点分隔开(例如，view1.view2.view3.table)。

提示 全局hint并非总处理某些查询转换，我建议你使用基于查询块名称(立刻显示)的语法。

由于WHERE子句的子查询不能命名，因此它们的对象无法被全局hint引用。为了解决这个问题，有另一种方法可以达到此目的。实际上，大多数hint可以接受一个参数，这个参数指定这些hint对哪个查询块有效。

这样的话，hint可以在SQL语句开头被分组并且仅引用它们应用的查询块。要使用这些引用，不仅需要查询优化器对每个查询块生成一个查询块名称(query block name),而且允许你使用qb_name hint来自定义名称。例如，下面的查询，两个查询块分别叫main和sq。接着在full hint里，查询块名称通过前缀@标识来引用。请注意在主查询中指定对emp表进行子查询的访问路径hint:

WITH emps AS(select /*+ qb_name(sq) */ deptno, count(*) AS cnt FROM emp GROUP BY deptno)

select /*+ qb_name(main) full(@main dept) full(@sq emp) */ dept.dname,emps.cnt

FROM dept, emps

WHERE dept.deptno = emps.deptno;

上一个例子显示了如何指定自己的名称。现在让我们来看看如何使用查询优化器生成的名称。首先，你必须知道它们是什么。为此，你可以使用EXPLAIN PLAN语句和dbms_xplan包，如下面的例子所示。请注意，alias选项被传递给display函数以确保查询块名称和别名是输出的一部分：

EXPLAIN PLAN FOR

WITH emps AS (select deptno, count(*) AS cnt FROM emp GROUP BY deptno)

select dept.dname,emps.cnt

FROM dept,emps

WHERE dept.deptno = emps.deptno;

select *FROM table(dbms_xplan.display(NULL,NULL,'basic +alias'));

系统生成的查询块名称由前缀和字符串组成。前缀是根据查询块里的操作生成的。表11-2做了总结。字符串是查询块的编号，基于它们解析SQL语句时所在的位置(左或右)。在前面的例子中，主查询块被命名为SEL$2,子查询块被命名为SEL$1。

表11-2 前缀在查询块名称中的使用

前  缀	用  途
CRI$	CREATE INDEX语句
DEL$	DELETE语句
INS$	INSERT语句
MISC$	其他SQL语句，比如LOCK TABLE
MRC$	MERGE语句
SEL$	select语句
SET$	集合运算符，比如：UNION和MINUS
UPD$	UPDATE语句

如下所示，系统生成的查询块名称的利用率与用户定义的查询块名称的利用率并无不同：

WITH emps AS(select deptno,count(*) AS cnt FROM emp GROUP BY deptno)

select /*+ full(@sel$2 dept) full(@sel$1 emp) */ dept.dname,emps.cnt

FROM dept,emps

WHERE dept.deptno  =  emps.deptno;

我需要对查询转换期间生成的查询块名称做最后一次解释。由于它们不是SQL语句的一部分，因而它们无法像其他对象那样计数。因此，查询优化器会为它们生成8位的散列值。下面的例子展示了这种情况。这里系统生成的查询块名称为SEL$5DA710D3:

EXPLAIN PLAN FOR

select deptno FROM dept

WHERE NOT EXISTS(select 1 FROM emp WHERE emp.deptno=dept.deptno);

select* FROM table(dbms_xplan.display(NULL,NULL,'basic +alias'));

在前面的输出中，会发现一件有趣的事情，当查询转换发生时，执行计划里的一些行(比如第二行)会有两个查询块名称。它们都可以使用hint。但是从查询优化器的角度来看，仅当完全相同的查询转换发生时，查询转换之后的查询块名称(这里是SEL$5DA710D3)才可用。