Hints介绍 补充

Hints介绍 收藏

Oracle使用的hints调整机制一直很复杂，Oracle Technical Network对使用hints调整Oracle SQL的过程有很好的全面评述。根据对10g数据库的介绍，可使用更多新的optimizer hints来控制优化行为。现在让我们迅速了解一下这些强大的新hints:

spread_min_analysis

使用这一hint，你可以忽略一些关于如详细的关系依赖图分析等电子表格的编译时间优化规则。其他的一些优化，如创建过滤以有选择性的定位电子表格访问结构并限制修订规则等，得到了继续使用。

由于在规则数非常大的情况下，电子表格分析会很长。这一提示可以帮助我们减少由此产生的数以百小时计的编译时间。

例如:

SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

spread_no_analysis

通过这一hint，可以使无电子表格分析成为可能。同样，使用这一hint可以忽略修订规则和过滤产生。如果存在一电子表格分析，编译时间可以被减少到最低程度。

例如:

SELECT /*+ SPREAD_NO_ANALYSIS */ ...

use_nl_with_index

这项hint使CBO通过嵌套循环把特定的表格加入到另一原始行。只有在以下情况中，它才使用特定表格作为内部表格:如果没有指定标签，CBO必须 可以使用一些标签，且这些标签至少有一个作为索引键值加入判断;反之，CBO必须能够使用至少有一个作为索引键值加入判断的标签。

例如:

SELECT /*+ USE_NL_WITH_INDEX (polrecpolrind) */ ...

CARDINALITY

此hint定义了对由查询或查询部分返回的基数的评价。注意如果没有定义表格，基数是由整个查询所返回的总行数。

例如:

SELECT /*+ CARDINALITY ( [tablespec] card ) */

SELECTIVITY

此hint定义了对查询或查询部分选择性的评价。如果只定义了一个表格，选择性是在所定义表格里满足所有单一表格判断的行部分。如果定义了一系列表格，选择性是指在合并以任何顺序满足所有可用判断的全部表格后，所得结果中的行部分。

例如:

SELECT /*+ SELECTIVITY ( [tablespec] sel ) */

然而，注意如果hints CARDINALITY 和 SELECTIVITY都定义在同样的一批表格，二者都会被忽略。

no_use_nl

Hint no_use_nl使CBO执行循环嵌套，通过把指定表格作为内部表格，把每个指定表格连接到另一原始行。通过这一hint，只有hash join和sort-merge joins会为指定表格所考虑。

例如:

SELECT /*+ NO_USE_NL ( employees ) */ ...

no_use_merge

此hint使CBO通过把指定表格作为内部表格的方式，拒绝sort-merge把每个指定表格加入到另一原始行。

例如:

SELECT /*+ NO_USE_MERGE ( employees dept ) */ ...

no_use_hash

此hint使CBO通过把指定表格作为内部表格的方式，拒绝hash joins把每个指定表格加入到另一原始行。

例如:

SELECT /*+ NO_USE_HASH ( employees dept ) */ ...
no_index_ffs

此hint使CBO拒绝对指定表格的指定标签进行fast full-index scan。

 

Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */

no_index_ss

此hint使CBO拒绝对指定表格的指定标签进行skip scan。

 

Syntax: /*+ NO_INDEX_SS (tablespecindexspec ) */

no_star_transformation

此hint使CBO忽略star 询问信息。

 

Syntax: /*+ NO_STAR_TRANSFORMATION */

index_ss

此hint明确地为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的升序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的升序排列。

 

Syntax: /*+ INDEX_SS (tablespecindexspec) */

index_ss_asc

此hint明确地为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的升序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的升序排列。

 

Syntax: /*+ INDEX_SS_ASC (tablespecindexspec) */

index_ss_desc

此hint明确为指定表格选择index skip scan。如果语句使用index range scan，Oracle将以对其索引值的降序排列来检查索引入口。在被分割的索引中，其结果为对每个部分内部的降序排列。

 

Syntax: /*+ INDEX_SS_DESC (tablespecindexspec) */

cpu_costing

此hint为SQL语句打开CPU costing。这是对优化器的默认评估模式。优化器评估当执行给定查询时，数据库需要运行的IO操作数、IO操作种类、以及CPU周期数。

 

Syntax: /*+ CPU_COSTING (tablespecindexspec) */

no_cpu_costing

此hint为SQL语句关闭CPU costing。然后CBO使用IO cost模式，此模式忽略CPU花费，仅测量在single-block reads中的所有指标。

 

Syntax: /*+ NO_CPU_COSTING */

随着Oracle优化器越来越成熟，Oracle专家必须不断增加自己对调整SQL语句的工具储备。当然，讨论所有复杂的Oracle10g SQL新hints远远超出了本文的范围，你可以从Mike Ault的新书Oracle Database 10g New Features中获得关于Oracle10g的更多信息

2.

使用优化器提示(Optimizer Hints) 收藏

优化器提示(Optimizer Hints)可以用在SQL语句中改变执行计划。

理解优化器提示

提示让你作出决定，这决定一般由优化器来作出。作为一个应用的设计者，你知道一些优化器不知道的关于你的数据的信息。提示提供了一种机制来指导优化器基于特殊条件下选择某个查询执行计划。
例如，对于某些查询，你知道一个索引更具选择性。在这些信息基础上，你可以比优化器选择出一个更有效率的执行计划。在这样的情况下，使用提示来指导优化器使用最优执行计划。
注意：使用提示包含了必须管理、检查和控制的额外代码。

提示的类型
提示包括以下类型：
1. Single-table：Single-table提示是特定在一个表或视图上。INDEX和USE_NL是single-table提示的例子。
2. Multi-table：Multi-table提示与single-table提示相似，除了它可以特定在一个或多个表或视图上。LEADING是 multi-table提示的一个例子。注意USE_NL(table1 table2)不能认为是multi-table提示，因为它实际是USE_NL(table1)和USE_NL(table2)的快捷方式。
3. Query block：Query block提示在单个查询块中操作。STAR_TRANSFORMATION和UNNEST是query block提示的例子。
4. Statement：Statement提示应用在整个SQL语句中。ALL_ROWS是Statement提示的一个例子。

提示分成以下几种分类：
1. 用于优化方法和目标的提示
2. 用于访问路径的提示
3. 用于查询转换的提示
4. 用于连接顺序的提示
5. 用于连接操作的提示
6. 用于平行执行的提示
7. 其他提示

用于优化方法和目标(Optimization Approaches)的提示
以下提示让你在优化方法和目标之间选择：
1. ALL_ROWS
2. FIRST_ROWS(n)
如果一个SQL语句有一个提示来指定优化的方法和目标，那么优化器会使用指定的方法，而不管统计信息是否存在，或者OPTIMIZER_MODE初始化参数的值，或者ALTER SESSION语句的OPTIMIZER_MODE参数。
注意：优化器的目标只会应用在直接提交的查询。使用提示为任何从PL/SQL中提交的SQL语句指定访问路径。ALTER SESSION ... SET OPTIMIZER_MODE语句不会影响到PL/SQL中运行的SQL语句。
如 果你在SQL语句中指定ALL_ROWS或FIRST_ROWS(n)的其中一个提示，并且如果数据字段没有语句要访问的表的统计信息，那么优化器会使用默认值，例如那些表的分配存储空间，来估计缺少的统计信息，然后选择一个执行计划。这些估计可能没有用DBMS_STATS包收集的信息精确，所以你应该 用DMBS_STATS包来收集统计信息。
如果你连同ALL_ROWS或FIRST_ROWS(n)一起来为访问路径或连接操作指定提示，那么优化器会优先选择由提示指定的访问路径和连接操作。

用于访问路径(Access Paths)的提示
以下每种提示都指导优化器使用一种特定的访问路径来访问表：
1. FULL
2. CLUSTER
3. HASH
4. INDEX
5. NO_INDEX
6. INDEX_ASC
7. INDEX_COMBINE
8. INDEX_JOIN
9. INDEX_DESC
10. INDEX_FFS
11. NO_INDEX_FFS
12. INDEX_SS
13. INDEX_SS_ASC
14. INDEX_SS_DESC
15. NO_INDEX_SS
指定这些提示中的一个会引起优化器特定的访问路径，但只有在这个访问路径基于索引或簇的存在且SQL语句中语法的正确时才可用。如果提示指定的是一个不可用的访问路径，那么优化器会忽略它。
你必须在语句中指定要访问的表。如果语句中用了别名来引用表，那么在提示要用别名而不是表名。如果语句中有方案名，提示中的表名不要包含方案名。
注意：对于访问路径提示，如果你在SELECT语句中的FROM子句部分指定了SAMPLE选项，那么Oracle会忽略这个提示。

用于查询转换(Query Transformation)的提示
以下的提示各指导优化器使用一种特定的SQL查询转换：
1. NO_QUERY_TRANSFORMATION
2. USE_CONCAT
3. NO_EXPAND
4. REWRITE
5. NO_REWRITE
6. MERGE
7. NO_MERGE
8. STAR_TRANSFORMATION
9. NO_STAR_TRANSFORMATION
10. FACT
11. NO_FACT
12. UNNEST
13. NO_UNNEST

用于连接顺序(Join Orders)的提示
以下提示为连接顺序提供建议:
1. LEADING
2. ORDERED

用于连接操作(Join Operations)的提示
以下每个提示都可以指导优化器对表使用一种特定的连接操作：
1. USE_NL
2. NO_USE_NL
3. USE_NL_WITH_INDEX
4. USE_MERGE
5. NO_USE_MERGE
6. USE_HASH
7. NO_USE_HASH
推荐与任何连接顺序提示(join order hint)一起使用USE_NL和USE_MERGE提示。当引用的表被强制为连接中的内部表时，Oracle使用这些提示；如果引用的表是外部表，那么这些提示会被忽略。

用于平行执行(Parallel Execution)的提示
当使用平行执行时，以下提示是指导优化器如何平行化或非平行化语句的。
1. PARALLEL
2. PQ_DISTRIBUTE
3. PARALLEL_INDEX
4. NO_PARALLEL_INDEX

其他提示(Additional Hints)
以下是一些其他提示：
1. APPEND
2. NOAPPEND
3. CACHE
4. NOCACHE
5. PUSH_PRED
6. NO_PUSH_PRED
7. PUSH_SUBQ
8. NO_PUSH_SUBQ
9. QB_NAME
10. CURSOR_SHARING_EXACT
11. DRIVING_SITE
12. DYNAMIC_SAMPLING
13. MODEL_MIN_ANALYSIS

指定提示(Specifying Hints)
提示只对它们出现在的语句块的优化起作用。一个语句块是指以下语句或语句的一部分：
1. 一个简单的SELECT，UPDATE或DELETE语句
2. 一个父语句或一个复杂语句的子查询
3. 一个组合查询的一部分
例如，一个由两个通过UNION操作符构成组件查询(component queries)组成的组合查询有两个块，每个都对应一个组件查询。因此，在第一个组件查询的提示只对它自己的优化起作用，而不会去优化第二个组件查询。

指定提示的Full Set
当使用提示时，有些情况下，你需要指定提示的full set来确保最优执行计划。例如，如果你有一个非常复杂的查询，包含多个表连接，并且你只对其中一个给定的表指定了INDEX提示，那么优化器需要决定剩 余的要使用的访问路径和相关的连接方法。因此，尽管你给定了INDEX提示，优化器可能不会使用该提示，因为由优化器选择的连接方法和访问路径不会使用这 个请求的索引。
在下面的例子中，LEADING提示指定了要被使用的确实连接顺序；同时也指定了在不同表上使用的连接方法。
SELECT /*+ LEADING(e2 e1) USE_NL(e1) INDEX(e1 emp_emp_id_pk) USE_MERGE(j) FULL(j) */ e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal FROM employees e1, employees e2, job_history j WHERE e1.employee_id = e2.manager_id AND e1.employee_id = j.employee_id AND e1.hire_date = j.start_date GROUP BY e1.first_name, e1.last_name, j.job_id ORDER BY total_sal;

在提示中指定查询块(Query Block)
要识别一个查询中的查询块，可以在提示中使用一个可选的 查询块名来指定要对哪个查询块进行提示。查询块参数的语法是@queryblock的形式，这里的queryblock是在查询中指定查询块的识别符。识别符queryblock可以是系统产生的，也可以是用户指定的。
1. 用EXPLAIN PLAN来查看查询可以得到系统产生的识别符。可以通过运行EXPLAIN PLAN来决定用NO_QUERY_TRANSFORMATION提示的查询的预转换查询块(pre-transformation query block)的名字。
2. 用户指定的名字可以用QB_NAME提示来设置。
在下面的例子中，查询块名字被用来连同NO_UNNEST提示在视图上的SELECT语句指定查询块。
CREATE OR REPLACE VIEW v AS SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal FROM employees e1, ( SELECT * FROM employees e3) e2, job_history j WHERE e1.employee_id = e2.manager_id AND e1.employee_id = j.employee_id AND e1.hire_date = j.start_date AND e1.salary = ( SELECT max(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id ) GROUP BY e1.first_name, e1.last_name, j.job_id ORDER BY total_sal;
在为查询运行EXPLAIN PLAN并显示计划表(plan table)输出后，你就可以看到系统产生的查询块识别符。例如，下面例子中显示的输出：
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, NULL, 'SERIAL')); ... Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- ... 10 - SEL$4 / E2@SEL$4  
  知道查询块名称后，就可以使用到下面的SQL语句中：
SELECT /*+ NO_UNNEST( @SEL$4 ) */ * FROM v;

指定全局表(Global Table)提示
通常指定表的提示一般是引用提示所在的 DELETE，SELECT或UPDATE查询块中，而不是语句引用的视图里的表。当你想要为一个出现在视图内部的表指定提示时，Oracle推荐使用全 局提示来代替嵌入提示到视图中。在本章描述的表提示可以通过使用一个扩展的包含表名和视图名的tablespec语法来转换成全局提示。
除此之外，可以在tablespec语法之前设置一个可选的查询块名称。
使用以下的语法来指定一个表的提示：
tablespec::=[view.]table
其中：
1. view指定了视图的名称
2. table指定了表名或表的别名

如果指定了视图的路径，提示是从左到右处理的，这样第一个视图必须在FROM子句中，然后每个后续的视图必须在前一个视图的FROM子句中。
例如，在下面的例子中，视图v用来返回雇员的性和名，他的工作和他的下属各个部门最高工资的雇员的工资总和。当查询数据时，你想在视图e2中对表e3强制使用索引emp_job_ix。

CREATE OR REPLACE VIEW v AS SELECT e1.first_name, e1.last_name, j.job_id, sum(e2.salary) total_sal FROM employees e1, ( SELECT * FROM employees e3) e2, job_history j WHERE e1.employee_id = e2.manager_id AND e1.employee_id = j.employee_id AND e1.hire_date = j.start_date AND e1.salary = ( SELECT max(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id) GROUP BY e1.first_name, e1.last_name, j.job_id ORDER BY total_sal;
通过使用全局提示，你可以避免修改视图v来在视图e2中指定索引提示。要强制在表e3中使用索引emp_job_ix，可以用以下一种方法：
SELECT /*+ INDEX(v.e2.e3 emp_job_ix) */ * FROM v; SELECT /*+ INDEX(@SEL$2 e2.e3 emp_job_ix) */ * FROM v; SELECT /*+ INDEX(@SEL$3 e3 emp_job_ix) */ * FROM v;
全局提示语法也可以用在非融合视图中，如下：
CREATE OR REPLACE VIEW v1 AS SELECT * FROM employees WHERE employee_id < 150; CREATE OR REPLACE VIEW v2 AS SELECT v1.employee_id employee_id, departments.department_id department_id FROM v1, departments WHERE v1.department_id = departments.department_id; SELECT /*+ NO_MERGE(v2) INDEX(v2.v1.employees emp_emp_id_pk) FULL(v2.departments) */ * FROM v2 WHERE department_id = 30;
这个提示导致v2不被融合，且指定了employees和departments表的访问连接。这些提示都下推到（非融合的）视图v2里。

指定复杂索引(Complex Index)提示
指定一个索引的提示可以使用单个索引名称或用括号括起来的字段列表，如下：
indexspec::=index|([table.]column{1,})
其中：
1. table指定表名
2. column指定特定表的字段名
2.1 在字段前面可以可选地加上表限定词作为前缀，这样可以允许在索引字段不在索引表的情况下指定位图连接索引。如果使用了表限定词，那么必须使用全名，而不能用别名。
2.2 索引中的每个字读必须是特定表的基础字段，而不是表达式。基于函数的索引不能用于字读特定提示，除非索引说明指定的字段形成基于函数的索引的前缀。
3. index指定索引名

这种提示是这样处理的：
1. 如果指定了index名称，那么只考虑索引。
2. 如果指定了字段列表，且这个列表与一个存在的索引在指定的字段和顺序上吻合，那么只考虑这个索引。如果没有那样的索引存在，那么表上的任何含有指定字段作为前缀的索引都会被考虑。任何一种情况，都会像用户在所有符合的索引上指定相同的提示。
例如，下面的例子中，job_history表有一个在employee_id字段上的单字段索引和一个连接了employee_id和start_date字段的索引。要特定地指导优化器使用索引，查询要这样提示：
SELECT /*+ INDEX(v.j jhist_employee_ix (employee_id start_date)) */ * FROM v;

与视图一起使用提示
Oracle不建议在视图（或子查询）里面或本身上面使用提示。这是因为你可以在一个上下文中定义视图，然后在另一个中使用它们。还有，那样的提示可能导致不确定的执行计划。特别地，在视图内部或本身上的提示根据视图是否可以跟顶层查询融合来不同地处理。


如果你想在视图或子查询中为一个表指定提示，那么推荐使用全局提示语法。


虽然如此，如果你决定与视图一起使用提示，那下面部分给出每种情况的描述。

提示与复杂视图
默认地，提示不会扩展到一个复杂视图的内部。例如，如果你在一个选择复杂视图的查询中指定了提示，那么那个提示不会产生作用，因为它不能推到视图的内部。

注意：如果视图是一个单表，那么提示提示不会被扩展。


除非提示是在基视图的内部，否则它们是不会在一个对于该视图的查询中起作用的。

提示和可融合的视图
优化方法和目标(Optimation approach and goal)提示可以发生在顶层查询或视图内部。
1. 如果在顶层查询中有那样一个提示，那么提示会被使用而无视任何在视图内部的那种提示。
2. 如果没有顶层优化器模式提示，那么在引用视图里的模式提示将和视图中的索引模式提示一起使用。
3. 如果两个或多个在引用视图中的模式提示冲突的话，那么忽略所有在视图里的模式提示，并且使用会话模式，不管是默认或者是用户指定的。

在引用的视图上的访问路径和连接提示会被忽略，除非视图包含一个单表（或者应用的是一个和单表一起的其他提示的视图）。对于那些单表视图，访问路径提示或连接提示会应用到视图内部的表中。

访问路径和连接提示可以出现在视图的定义里。
1. 如果视图是一个内联视图（即如果它出现在SELECT语句的FROM子句里），那么视图内部的所有访问路径和连接提示在与顶层查询融合时都会保留下来。
2. 对于不是内联视图来说，视图内的访问路径和连接提示只有在引用的查询不引用其他表或视图（即如果SELECT语句的FROM子句只包含这个视图）时才保留下来。

在视图上的PARALLEL，NO_PARALLEL，PARALLEL_INDEX和NO_PARALLEL_INDEX提示会被递归地应用到引用视图的索引表中。在顶层查询的平行执行提示会覆盖引用视图内部的那些提示。

视图内部的PARALLEL，NO_PARALLEL，PARALLEL_INDEX和NO_PARALLEL_INDEX提示在当视图与顶层查询融合时保留下来。在顶层查询的平行执行提示会覆盖引用视图内部的那些提示。

提示和非融合视图
对于非融合视图，视图内的优化方法和目标提示被忽略；顶层查询决定优化模式。
因为非融合视图是从顶层查询中分开优化的，所以视图内部的访问路径和连接提示会被保留下来。相同原因，顶层查询里的视图上的访问路径提示会被忽略。
尽管如此，顶层查询里的视图上的连接提示会被保留，因为那种情况下，非融合视图类似一个表

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25897606/viewspace-705156/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25897606/viewspace-705156/