Oracle Hint[转]

hint翻译:提示,线索

其实Oracle的优化器有两种优化方式,

基于规则的优化方式(Rule-Based Optimization，简称为RBO)

基于代价的优化方式(Cost-Based Optimization，简称为CBO)

所以hint也不例外，除了/*+rule*/其他的都是CBO优化方式

RBO方式：

  优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的，当一个where子句中的一列有索引时去走索引。

CBO方式：

   它是看语句的代价(Cost),这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小、有少行、每行的长度等信息。这些统计信息起初在库内是没有的，是做analyze后才出现的，很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因此应及时更新这些信息。

 

优化模式包括Rule、Choose、First rows、All rows四种方式：

 

    Rule：基于规则的方式。

 

    Choolse：默认的情况下Oracle用的便是这种方式。指的是当一个表或或索引有统计信息，则走CBO的方式，如果表或索引没统计信息，表又不是特别的小，而且相应的列有索引时，那么就走索引，走RBO的方式。

 

    First Rows：它与Choose方式是类似的，所不同的是当一个表有统计信息时，它将是以最快的方式返回查询的最先的几行，从总体上减少了响应时间。

 

    All Rows:也就是我们所说的Cost的方式，当一个表有统计信息时，它将以最快的方式返回表的所有的行，从总体上提高查询的吞吐量。没有统计信息则走RBO的方式

 

Oracle在那配置默认的优化规则

    A、Instance级别我们可以通过在initSID.ora文件中设定OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS如果没设定OPTIMIZER_MODE参数则默认用的是Choose方式。

    B、Sessions级别通过ALTER SESSION SET OPTIMIZER_MODE=RULE/CHOOSE/FIRST_ROWS/ALL_ROWS来设定。

    C、语句级别用Hint（/*+ ... */）来设定

为什么表的某个字段明明有索引，但执行计划却不走索引？

    1、优化模式是all_rows的方式

    2、表作过analyze，有统计信息

    3、表很小，Oracle的优化器认为不值得走索引。

提示

   不区分大小写, 多个提示用空格分开

  如：select /*+ hint1(tab1) hint2(TAB1 idx1) */ col1, col2 from tab1 where col1='xxx';

  如果表使用了别名, 那么提示里也必须使用别名

如：select /*+ hint1(t1) */ col1, col2 from tab1 t1 where col1='xxx';

如果使用同一个表的多个用,号分开

如： select /*+ index(t1.A,t1.B) */ col1, col2

    from   tab1 t1

    where  col1='xxx';

 

oracle 10g hints知识,

    10g数据库可以使用更多新的optimizer hints来控制优化行为。现在让我们快速解析一下这些强大的新hints：

 

1、spread_min_analysis

 

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

 

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

 

例：

    SELECT /*+ SPREAD_MIN_ANALYSIS */ ...

 

2、spread_no_analysis

 

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

 

例：

    SELECT /*+ SPREAD_NO_ANALYSIS */ ...

 

3、use_nl_with_index

 

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

 

例：

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

 

4、CARDINALITY

 

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

 

例：

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

 

5、SELECTIVITY

 

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

 

例：

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

 

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

 

6、no_use_nl

 

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

 

例：

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

 

7、no_use_merge

 

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

 

例：

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

 

8、no_use_hash

 

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

 

例：

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

 

9、no_index_ffs

 

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

Syntax: /*+ NO_INDEX_FFS ( tablespecindexspec ) */

 

 

在SQL优化过程中常见HINT的用法(前10个比较常用, 前3个最常用)：

 

1. /*+ INDEX */ 和 /*+ INDEX(TABLE INDEX1, index2) */ 和 /*+ INDEX(tab1.col1 tab2.col2) */ 和 /*+ NO_INDEX */ 和 /*+ NO_INDEX(TABLE INDEX1, index2) */

 

表明对表选择索引的扫描方法. 第一种不指定索引名是让oracle对表中可用索引比较并选择某个最佳索引; 第二种是指定索引名且可指定多个索引; 第三种是10g开始有的, 指定列名, 且表名可不用别名; 第四种即全表扫描; 第五种表示禁用某个索引, 特别适合于准备删除某个索引前的评估操作. 如果同时使用了INDEX和NO_INDEX则两个提示都会被忽略掉.

例如:SELECT /*+ INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

 

2. /*+ ORDERED */

FROM子句中默认最后一个表是驱动表,ORDERED将from子句中第一个表作为驱动表. 特别适合于多表连接非常慢时尝试.

例如:SELECT /*+ ORDERED */ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

 

3. /*+ PARALLEL(table1,DEGREE) */ 和 /*+ NO_PARALLEL(table1) */

该提示会将需要执行全表扫描的查询分成多个部分（并行度）执行, 然后在不同的操作系统进程中处理每个部分. 该提示还可用于DML语句. 如果SQL里还有排序操作, 进程数会翻倍,此外还有一个一个负责组合这些部分的进程,如下面的例子会产生9个进程. 如果在提示中没有指定DEGREE, 那么就会使用创建表时的默认值. 该提示在默认情况下会使用APPEND提示. NO_PARALLEL是禁止并行操作,否则语句会使用由于定义了并行对象而产生的并行处理.

例如:select /*+ PARALLEL(tab_test,4) */ col1, col2 from tab_test order by col2;

 

4. /*+ FIRST_ROWS */ 和 /*+ FIRST_ROWS(n) */

表示用最快速度获得第1/n行, 获得最佳响应时间, 使资源消耗最小化.

在update和delete语句里会被忽略, 使用分组语句如group by/distinct/intersect/minus/union时也会被忽略.

例如:SELECT /*+ FIRST_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

 

5. /*+ RULE */

表明对语句块选择基于规则的优化方法.

例如:SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

 

6. /*+ FULL(TABLE) */

表明对表选择全局扫描的方法.

例如:SELECT /*+ FULL(A) */ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

 

7. /*+ LEADING(TABLE) */

类似于ORDERED提示, 将指定的表作为连接次序中的驱动表.

 

8. /*+ USE_NL(TABLE1,TABLE2) */

将指定表与嵌套的连接的行源进行连接,以最快速度返回第一行再连接,与USE_MERGE刚好相反.

例如:SELECT /*+ ORDERED USE_NL(BSEMPMS) */ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

 

9. /*+ APPEND */ 和 /*+ NOAPPEND */

直接插入到表的最后,该提示不会检查当前是否有插入操作所需的块空间而是直接添加到新块中, 所以可以提高速度. 当然也会浪费些空间, 因为它不会使用那些做了delete操作的块空间. NOAPPEND提示则相反,所以会取消PARALLEL提示的默认APPEND提示.

例如:insert /*+ append */ into test1 select * from test4;

insert /*+ parallel(test1) noappend */ into test1 select * from test4;

 

10. /*+ USE_HASH(TABLE1,table2) */

将指定的表与其它行源通过哈希连接方式连接起来.为较大的结果集提供最佳响应时间. 类似于在连接表的结果中遍历每个表上每个结果的嵌套循环, 指定的hash表将被放入内存, 所以需要有足够的内存（hash_area_size或pga_aggregate_target）才能保证语句正确执行, 否则将在磁盘里进行.

例如:SELECT /*+ USE_HASH(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

 

---------------------------------------------------------------------

 

11. /*+ USE_MERGE(TABLE) */

将指定的表与其它行源通过合并排序连接方式连接起来.特别适合于那种在多个表大量行上进行集合操作的查询, 它会将指定表检索到的的所有行排序后再被合并, 与USE_NL刚好相反.

例如:SELECT /*+ USE_MERGE(BSEMPMS,BSDPTMS) */ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

 

12. /*+ ALL_ROWS */

表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化. 可能会限制某些索引的使用.

例如:SELECT /*+ ALL+_ROWS */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

 

13. /*+ CLUSTER(TABLE) */

提示明确表明对指定表选择簇扫描的访问方法. 如果经常访问连接表但很少修改它, 那就使用集群提示.

例如:SELECT /*+ CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

 

14. /*+ INDEX_ASC(TABLE INDEX1, INDEX2) */

表明对表选择索引升序的扫描方法. 从8i开始, 这个提示和INDEX提示功能一样, 因为默认oracle就是按照升序扫描索引的, 除非未来oracle还推出降序扫描索引.

例如:SELECT /*+ INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

 

15. /*+ INDEX_COMBINE(TABLE INDEX1, INDEX2) */

指定多个位图索引, 对于B树索引则使用INDEX这个提示,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.

例如:SELECT /*+ INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */ * FROM BSEMPMS WHERE SAL<5000000 AND HIREDATE<SYSDATE;

 

16. /*+ INDEX_JOIN(TABLE INDEX1, INDEX2) */

合并索引, 所有数据都已经包含在这两个索引里, 不会再去访问表, 比使用索引并通过rowid去扫描表要快5倍.

例如:SELECT /*+ INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI) */ SAL,HIREDATE FROM BSEMPMS WHERE SAL<60000;

 

17. /*+ INDEX_DESC(TABLE INDEX1, INDEX2) */

表明对表选择索引降序的扫描方法.

例如:SELECT /*+ INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

 

18. /*+ INDEX_FFS(TABLE INDEX_NAME) */

对指定的表执行快速全索引扫描,