• Oracle中Hint被忽略的几种常见情形


    Oracle中Hint被忽略的几种常见情形

    作者:hbxztc 时间: 2021-02-05 09:26:55
    【摘要】Hint可以影响优化器对于执行计划的选择,但这种影响不是强制性的,优化器在某些情况下可能会忽略目标SQL中的Hint。由于各种原因导致Hint被Oracle忽略后,Oracle并不会给出任何提示或者警告,更不会报错,目标SQL依然可以正常运行,这也符合Hint实际上是一种特殊注释的身份。注释本来就是可有可无的东西,不应该因为它的存在而而导致原先在没有Hint时可以正常执行的SQL因为加了Hi...

    Hint可以影响优化器对于执行计划的选择,但这种影响不是强制性的,优化器在某些情况下可能会忽略目标SQL中的Hint。由于各种原因导致Hint被Oracle忽略后,Oracle并不会给出任何提示或者警告,更不会报错,目标SQL依然可以正常运行,这也符合Hint实际上是一种特殊注释的身份。注释本来就是可有可无的东西,不应该因为它的存在而而导致原先在没有Hint时可以正常执行的SQL因为加了Hint后而变得不能正常执行。

    下面来看几种Hint被Oracle忽略的常见情形。

    1 使用的Hint有语法或者拼写错误

    一旦使用的Hint中有语法或者拼写错误,Oracle就会忽略该Hint,看几个示例SQL:

    1. select /*+ ind(emp pk_emp) */* from emp;

    2. select /*+ index(emp pk_emp */* from emp;

    3. select /* + index(emp pk_emp) */* from emp;

    4. select */*+ index(emp pk_emp) */ from emp;

    5. select /*+ index(scott.emp pk_emp) */* from emp;

    6. select /*+ index(emp pk_emp) */* from emp e;

    7. select /*+ index(emp emp_pk) */* from emp;

    8. select /*+ full(t2) */ t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from detp t where t2.loc='CHICAGO');

    实际上,上述8条SQL中的Hint都是无效的,它们都会被Oracle忽略。

    1是因为关键字应该是"index"而不是"ind"

    2是因为漏掉了一个右括号

    3是因为Hint中第一个*和+之间出现了空格

    4是因为Hint出现的位置不对,它应该出现在*前面

    5是因为emp表前面带上了SCHEME名称

    6是因为没有emp表的别名

    7是因为索引名称写错了

    8是因为Hint跨了Query Block。Hint生效的范围公限于它本身所在的Query Block,如果将某个Hint生将范围扩展到它所在的Query Block之外而又没在该Hint中指定其生效的Query Block名称的话,Oracle就会忽略该Hint。

    2 使用的Hint无效

    即使语法是正确的,但如果由于某种原因导致Oracle认为这个Hint无效,则Oracle还是会忽略该Hint。

    看几个实例

    scott@TEST>set autotrace traceonly 
    scott@TEST>select /*+ index(dept idx_dept_loc) */ deptno,dname from dept where loc='CHICAGO';
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 492093765
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | |	10 |   300 |	 2   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| DEPT |	10 |   300 |	 2   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN | IDX_DEPT_LOC |	 4 | |	 1   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------
    ......

    从上面的输出可以看出,上面的SQL的执行计划走的是对索引IDX_DEPT_LOC的索引范围扫描,说明Hint生效了,但是如果把where条件替换为与索引IDX_DEPT_LOC毫不相关的deptno=30,再来看执行情况

    scott@TEST>select /*+ index(dept idx_dept_loc) */ deptno,dname from dept where deptno=30;
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2852011669
    
    ---------------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | | 1 | 22 | 2	(0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 22 | 2	(0)| 00:00:01 |
    |*  2 |   INDEX UNIQUE SCAN | PK_DEPT | 1 | | 1	(0)| 00:00:01 |
    ---------------------------------------------------------------------------------------
    ......

    从上面的输出可以看出,执行计划走的是对主键PK_DEPT的INDEX UNIQUE SCAN,面不是Hint里的IDX_DEPT_LOC。这就说明Hint在这个SQL失效了。

    即使不改where条件,如果把索引IDX_DEPT_LOC删除,这个Hint也会失效:

    scott@TEST>drop index idx_dept_loc;
    
    Index dropped.
    
    scott@TEST>select /*+ index(dept idx_dept_loc) */ deptno,dname from dept where loc='CHICAGO';
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3383998547
    
    --------------------------------------------------------------------------
    | Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |	 | 10 |   300 | 29   (0)| 00:00:01 |
    |*  1 |  TABLE ACCESS FULL| DEPT | 10 |   300 | 29   (0)| 00:00:01 |
    --------------------------------------------------------------------------

    从上面的执行计划可以看出走的是对表DEPT的TABLE ACCESS FULL,Hint也是失效的。

    再来看一个使用组合Hint的例子,先看如下SQL的执行计划

    scott@TEST>select /*+ full(dept) parallel(dept 2) */ deptno from dept;
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 587379989
    
    --------------------------------------------------------------------------------------------------------------
    | Id  | Operation | Name	| Rows	| Bytes | Cost (%CPU)| Time	| TQ  |IN-OUT| PQ Distrib |
    --------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT |		|  1000 | 13000 | 16   (0)| 00:00:01 |	 |	| |
    |   1 |  PX COORDINATOR |		|	|	| |		|	 |	| |
    |   2 |   PX SEND QC (RANDOM)| :TQ10000 |  1000 | 13000 | 16   (0)| 00:00:01 |  Q1,00 | P->S | QC (RAND)  |
    |   3 | PX BLOCK ITERATOR |		|  1000 | 13000 | 16   (0)| 00:00:01 |  Q1,00 | PCWC | |
    |   4 | TABLE ACCESS FULL| DEPT	|  1000 | 13000 | 16   (0)| 00:00:01 |  Q1,00 | PCWP | |
    --------------------------------------------------------------------------------------------------------------
    ......

    从上面输出内容可以看出,现在是对表DEPT做的并行全表扫描,说明组合Hint中的两个都生效了,这个Hint的含义是既要全表扫描又要并行访问表DEPT,两者不矛盾,因为全表扫描可以并行执行。再看如下的SQL:

    scott@TEST>select /*+ index(dept pk_dept) parallel(dept 2) */ deptno from dept;
    
    4 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2913917002
    
    ----------------------------------------------------------------------------
    | Id  | Operation	 | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | |  1000 | 13000 |	26   (0)| 00:00:01 |
    |   1 |  INDEX FULL SCAN | PK_DEPT |  1000 | 13000 |	26   (0)| 00:00:01 |
    ----------------------------------------------------------------------------
    ......

    现在SQL走的是对索引PK_DEPT的索引全扫描,但是串行的,说明Hint中的parallel(dept 2)失效了,因为表DEPT上的主键索引PK_DEPT不是分区索引,而对于非分区索引而言,索引范围扫描或索引全扫描并不能并行执行,所以上述组合Hint中忽略了parallel(dept 2)。

    再看一个HASH JOIN的例子:

    下面的SQL中use_hash的Hint是生效的:

    scott@TEST>select /*+ use_hash(t1) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno and t2.loc='CHICAGO';
    
    6 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 615168685
    
    ---------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   | | 5 |   185 | 7  (15)| 00:00:01 |
    |*  1 |  HASH JOIN | | 5 |   185 | 7  (15)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| DEPT | 1 | 11 | 3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| EMP  | 14 |   364 | 3   (0)| 00:00:01 |
    --------------------------------------------------------------------------
    -

    但是如果把SQL修改为如下则use_hash的Hint就会被忽略

    scott@TEST>select /*+ use_hash(t1) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno>t2.deptno and t2.loc='CHICAGO';
    
    no rows selected
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 4192419542
    
    ---------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   | | 1 | 37 | 6   (0)| 00:00:01 |
    |   1 |  NESTED LOOPS | | 1 | 37 | 6   (0)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| DEPT | 1 | 11 | 3   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| EMP  | 1 | 26 | 3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------

    从上面的执行计划中看出use_hash确实是被Oracle忽略了,这是因为哈希连接只适用于等值连接条件,不等值的连接条件对哈希连接而言是没有意义的,所以上述Hint就被Oracle忽略了。

    3 使用的Hint自相矛盾

    如果使用的组合Hint是自相矛盾的,则这些自相矛盾的Hint都会被Oracle忽略。但Oracle只会将自相矛盾的Hint全部忽略掉,但如果使用的组合Hint中还有其他有效的Hint,则这些有效Hint不受影响。

    看一个使用自相矛盾Hint的实例,先执行单个Hint的SQL

    scott@TEST>select /*+ index_ffs(dept pk_dept)*/ deptno from dept;
    
    4 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2578398298
    
    --------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | | 4 | 12 | 2	 (0)| 00:00:01 |
    |   1 |  INDEX FAST FULL SCAN| PK_DEPT | 4 | 12 | 2	 (0)| 00:00:01 |
    --------------------------------------------------------------------------------
    ......
    scott@TEST>select /*+ full(dept)*/ deptno from dept;
    
    4 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3383998547
    
    --------------------------------------------------------------------------
    | Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
    --------------------------------------------------------------------------
    |   0 | SELECT STATEMENT  |	 | 4 | 12 | 3   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS FULL| DEPT | 4 | 12 | 3   (0)| 00:00:01 |
    --------------------------------------------------------------------------

    从上面的输出可以看出单独使用上面的两个Hint都能被Oracle生效,但如果这两个Hint合并到一起使用就不是那么回事了:

    scott@TEST>select /*+ index_ffs(dept pk_dept) full(dept)*/ deptno from dept;
    
    4 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2913917002
    
    ----------------------------------------------------------------------------
    | Id  | Operation	 | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | |	 4 |	12 |	 1   (0)| 00:00:01 |
    |   1 |  INDEX FULL SCAN | PK_DEPT |	 4 |	12 |	 1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------

    从上面的输出可以看出执行计划没有走Hint中指定的执行计划,而是对主键索引PK_DEPT做的是INDEX FULL SCAN这说明Hint中的两个都失效了。

    再来看下面的例子:

    scott@TEST>select /*+ index_ffs(dept pk_dept) full(dept) cardinality(dept 1000) */ deptno from dept;
    
    4 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2913917002
    
    ----------------------------------------------------------------------------
    | Id  | Operation	 | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | |  1000 |  3000 |	 1   (0)| 00:00:01 |
    |   1 |  INDEX FULL SCAN | PK_DEPT |  1000 |  3000 |	 1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------

    从上面的输出可以看出执行计划走的仍然是对主键索引PK_DEPT做的是INDEX FULL SCAN,但是做INDEX FULL SCAN反回结果集的cardinality从原来的4变为了1000,说明cardinality(dept 1000)生效了,也验证了如果使用的组合Hint中还有其他有效的Hint,则这些有效Hint不受影响。

    4 使用的Hint受到了查询转换的干扰

    有时候,查询转换也会导致相关的Hint失效,即Hint被Oracle忽略还可能是因为受到了查询转换的干扰。

    下面来看一个因为使用了查询转换而导致相关Hint被Oracle忽略掉的实例。

    创建一个测试表jobs

    scott@TEST>create table jobs as select empno,job from emp;
    
    Table created.

    构造一个SQL

    select /*+ ordered cardinality(e 100) */
     e.ename, j.job, e.sal, v.avg_sal
      from emp e, jobs j, (select /*+ merge */ e.deptno, avg(e.sal) avg_sal from emp e, dept d where d.loc = 'chicago' and d.deptno = e.deptno group by e.deptno) v
     where e.empno = j.empno and e.deptno = v.deptno and e.sal > v.avg_sal
     order by e.ename;

    上面的SQL是两个表(EMP和JOBS)和内嵌视图V关联的SQL,其中内嵌视图V又是由表EMP和DEPT关联后得到的。在此SQL中使用了三个Hint,其中merge用于让内嵌视图V做视图合并,ordered表示上述SQL在执行时表EMP、JOBS和内嵌视图V的连接顺序应该和它们在该SQL的SQL文本中出现的顺序一致,即它们应该是按照从左至右的顺序依次做表连接。

    如果上述三个Hint都生效的话,那目标SQL的执行计划中应该不会出现关键字“VIEW”(表示做了视图合并,体现了Merge Hint的作用),表EMP、JOBS和内嵌视图V的连接应该会变成表EMP、JOBS和内嵌视图V所对应的基表EMP和DEPT的连接,且连接的先后顺序应该是EMP->JOBS->内嵌视图V所对应的基表EMP和DEPT(体现了Ordered Hint的作用),外围查询中表EMP的扫描结果所对应的Cardinality的值应该是100(体现了Cardinality Hint的作用)。

    现在看一下实际情况,执行上面的SQL:

    scott@TEST>select /*+ ordered cardinality(e 100) */
      2   e.ename, j.job, e.sal, v.avg_sal
      3 from emp e,
      4 jobs j,
      5 (select /*+ merge */
      6 e.deptno, avg(e.sal) avg_sal
      7 from emp e, dept d
      8 where d.loc = 'chicago'
      9 and d.deptno = e.deptno
     10 group by e.deptno) v
     11   where e.empno = j.empno
     12 and e.deptno = v.deptno
     13 and e.sal > v.avg_sal
     14   order by e.ename;
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 930847561
    
    -------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | |   156 | 19656 | 15  (20)| 00:00:01 |
    |*  1 |  FILTER | | | | | |
    |   2 |   SORT GROUP BY | |   156 | 19656 | 15  (20)| 00:00:01 |
    |*  3 | HASH JOIN | |   156 | 19656 | 14  (15)| 00:00:01 |
    |*  4 | TABLE ACCESS FULL  | DEPT | 1 | 11 | 3   (0)| 00:00:01 |
    |*  5 | HASH JOIN | |   467 | 53705 | 10  (10)| 00:00:01 |
    |   6 | TABLE ACCESS FULL | EMP  | 14 |   364 | 3   (0)| 00:00:01 |
    |*  7 | HASH JOIN | |   100 |  8900 | 7  (15)| 00:00:01 |
    |   8 | TABLE ACCESS FULL| EMP  |   100 |  5800 | 3   (0)| 00:00:01 |
    |   9 | TABLE ACCESS FULL| JOBS | 14 |   434 | 3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------

    从上面的执行计划可以看出,确实没有出现关键字“VIEW”,表EMP的扫描结果所对应的Cardinality的值确实是100,但连接顺序不是上面提到的顺序,而是先选择的表DEPT。这说明上述三个Hint中的Merge Hint和Cardinality Hint生效了,但Ordered Hint被Oracle忽略了。这是因为受到了查询转换的干扰(对内嵌视图V做视图合并是一种查询转换)。

    为了证明上述SQL的Ordered Hint被Oracle忽略是因为受到了查询转换的干扰,现在将内嵌视图V中的merge替换为no_merge(不让内嵌视图做视图合并),再次执行该SQL:

    scott@TEST>select /*+ ordered cardinality(e 100) */
      2   e.ename, j.job, e.sal, v.avg_sal
      3 from emp e,
      4 jobs j,
      5 (select /*+ no_merge */
      6 e.deptno, avg(e.sal) avg_sal
      7 from emp e, dept d
      8 where d.loc = 'chicago'
      9 and d.deptno = e.deptno
     10 group by e.deptno) v
     11   where e.empno = j.empno
     12 and e.deptno = v.deptno
     13 and e.sal > v.avg_sal
     14   order by e.ename;
    
    no rows selected
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2898000699
    
    --------------------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    --------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | | 8 |   728 | 14  (22)| 00:00:01 |
    |   1 |  SORT ORDER BY | | 8 |   728 | 14  (22)| 00:00:01 |
    |*  2 |   HASH JOIN | | 8 |   728 | 13  (16)| 00:00:01 |
    |*  3 | HASH JOIN | |   100 |  6500 | 7  (15)| 00:00:01 |
    |   4 | TABLE ACCESS FULL | EMP |   100 |  4600 | 3   (0)| 00:00:01 |
    |   5 | TABLE ACCESS FULL | JOBS | 14 |   266 | 3   (0)| 00:00:01 |
    |   6 | VIEW | | 5 |   130 | 6  (17)| 00:00:01 |
    |   7 | HASH GROUP BY | | 5 |   185 | 6  (17)| 00:00:01 |
    |   8 | MERGE JOIN | | 5 |   185 | 6  (17)| 00:00:01 |
    |*  9 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 11 | 2   (0)| 00:00:01 |
    |  10 | INDEX FULL SCAN | PK_DEPT | 4 | | 1   (0)| 00:00:01 |
    |* 11 | SORT JOIN | | 14 |   364 | 4  (25)| 00:00:01 |
    |  12 | TABLE ACCESS FULL | EMP | 14 |   364 | 3   (0)| 00:00:01 |
    --------------------------------------------------------------------------------------------

    从上面的执行计划中可以看出,出现了“VIEW”关键字,说明没有做视图合并,表EMP对就的Cardinality为100,连接顺序与前面预想的一致,这说明在禁掉了查询转换后之前被忽略的Ordered Hint又生效了。

    5 使用的Hint受到了保留关键字的干扰

    Oracle在解析Hint时,是按照从左到右的顺序进行的,如果遇到的词是Oracle的保留关键字,则Oracle将忽略这个词以及之后的所有词;如果遇到词既不是关键字也不是Hint,就忽略该词;如果遇到的词是有效的Hint,那么Oracle就会保留该Hing。

    正是由于上述Oracle解析Hint的原则,保留关键字也可能导致相关的Hint失效。

    Oracle的保留关键字可以从视图V$RESERVED_WORDS中查到,从下面的查询结果可以看到','、'COMMENT'、'IS'都是保留关键字,但“THIS”不是

    scott@TEST>select keyword,length from v$reserved_words where keyword in (',','THIS','IS','COMMENT');
    
    KEYWORD LENGTH
    ---------- ----------
    , 1
    COMMENT 7
    IS 2

    下面来看一个保留关键字导致Hint失效的实例,执行下面的SQL

    scott@TEST>select t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno;
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 844388907
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | | 14 |   518 | 6  (17)| 00:00:01 |
    |   1 |  MERGE JOIN | | 14 |   518 | 6  (17)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2   (0)| 00:00:01 |
    |   3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1   (0)| 00:00:01 |
    |*  4 |   SORT JOIN | | 14 |   364 | 4  (25)| 00:00:01 |
    |   5 | TABLE ACCESS FULL | EMP | 14 |   364 | 3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------

    从执行计划上看走的是MERGE SORT JOIN,对SQL加入如下Hint并执行:

    scott@TEST>select /*+ use_hash(t1) index(t2 pk_dept) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno;
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2622742753
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | | 14 |   518 | 6  (17)| 00:00:01 |
    |*  1 |  HASH JOIN | | 14 |   518 | 6  (17)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2   (0)| 00:00:01 |
    |   3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1   (0)| 00:00:01 |
    |   4 |   TABLE ACCESS FULL | EMP | 14 |   364 | 3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------

    从上面的执行计划中可以看出Hint中的两个都生效了,emp做HASH JOIN的被驱动表,对DEPT表做使用索引PK_DEPT。现在对Hint加入',',查看执行情况:

    scott@TEST>select /*+ use_hash(t1) , index(t2 pk_dept) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno;
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 615168685
    
    ---------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   | | 14 |   518 | 7  (15)| 00:00:01 |
    |*  1 |  HASH JOIN | | 14 |   518 | 7  (15)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| DEPT | 4 | 44 | 3   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| EMP  | 14 |   364 | 3   (0)| 00:00:01 |
    ---------------------------------------------------------------------------

    从执行计划中可以看出,仍然走的是HASH JOIN但是index(t2 pk_dept)失效了。因为','是Oracle的保留关键字,所以','后面的index(t2 pk_dept)失效了,再修改Hint如下并执行SQL:

    scott@TEST>select /*+ comment use_hash(t1) index(t2 pk_dept) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno;
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 844388907
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | | 14 |   518 | 6  (17)| 00:00:01 |
    |   1 |  MERGE JOIN | | 14 |   518 | 6  (17)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2   (0)| 00:00:01 |
    |   3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1   (0)| 00:00:01 |
    |*  4 |   SORT JOIN | | 14 |   364 | 4  (25)| 00:00:01 |
    |   5 | TABLE ACCESS FULL | EMP | 14 |   364 | 3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------

    从执行计划中看出,现在走的是跟一开始的执行计划一样,说明Hint中的两个都失效了,因为这两个都在Oracle保留关键字comment后面。再修改Hint如下再次执行SQL:

    scott@TEST>select /*+ this use_hash(t1) index(t2 pk_dept) */ t1.empno,t1.empno,t2.loc from emp t1,dept t2 where t1.deptno=t2.deptno;
    
    14 rows selected.
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2622742753
    
    ----------------------------------------------------------------------------------------
    | Id  | Operation | Name | Rows  | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT | | 14 |   518 | 6  (17)| 00:00:01 |
    |*  1 |  HASH JOIN | | 14 |   518 | 6  (17)| 00:00:01 |
    |   2 |   TABLE ACCESS BY INDEX ROWID| DEPT | 4 | 44 | 2   (0)| 00:00:01 |
    |   3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1   (0)| 00:00:01 |
    |   4 |   TABLE ACCESS FULL | EMP | 14 |   364 | 3   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------

    现在执行计划又走出了Hint指定的样子,说明两个都生效了,这是因为this不是Oracle保留关键字。

    以上介绍了5种Hint被Oracle忽略的情况,在实例使用过程中一定要注意使用方法,使用正确有效的Hint来提升SQL执行效率,避免Hint被Oracle忽略。

    参考《基于Oracle的SQL优化》

  • 相关阅读:
    sw
    ++1
    test
    为了
    发送邮件
    新建121212
    29012
    pthread_create/join函数
    recv函数学习
    socketpair用法学习
  • 原文地址:https://www.cnblogs.com/yaoyangding/p/15077516.html
Copyright © 2020-2023  润新知