Hint可以影响优化器对于执行计划的选择，但这种影响不是强制性的，优化器在某些情况下可能会忽略目标sql中的Hint。由于各种原因导致Hint被Oracle忽略后，Oracle并不会给出任何提示或者警告，更不会报错，目标sql依然可以正常运行，这也符合Hint实际上是一种特殊注释的身份。注释本来就是可有可无的东西，不应该因为它的存在而而导致原先在没有Hint时可以正常执行的sql因为加了Hint后而变得不能正常执行。
下面来看几种Hint被Oracle忽略的常见情形。
1 使用的Hint有语法或者拼写错误
一旦使用的Hint中有语法或者拼写错误，Oracle就会忽略该Hint，看几个示例sql：
select /*+ ind(emp pk_emp) */* from emp;
select /*+ index(emp pk_emp */* from emp;
select /* + index(emp pk_emp) */* from emp;
select */*+ index(emp pk_emp) */ from emp;
select /*+ index(scott.emp pk_emp) */* from emp;
select /*+ index(emp pk_emp) */* from emp e;
select /*+ index(emp emp_pk) */* from emp;
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>setautotracetraceonly scott@TEST>select/*+index(deptidx_dept_loc)*/deptno,dnamefromdeptwhereloc='CHICAGO'; ExecutionPlan ---------------------------------------------------------- Planhashvalue:492093765 -------------------------------------------------------------------------------------------- |Id|Operation |Name |Rows|Bytes|Cost(%cpu)|Time | -------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT | | 10|300| 2(0)|00:00:01| |1|TABLEACCESSBYINDEXROWID|DEPT | 10|300| 2(0)|00:00:01| |*2|INDEXRANGESCAN |IDX_DEPT_LOC| 4| | 1(0)|00:00:01| -------------------------------------------------------------------------------------------- ......
从上面的输出可以看出，上面的sql的执行计划走的是对索引IDX_DEPT_LOC的索引范围扫描，说明Hint生效了，但是如果把where条件替换为与索引IDX_DEPT_LOC毫不相关的deptno=30，再来看执行情况
scott@TEST>select/*+index(deptidx_dept_loc)*/deptno,dnamefromdeptwheredeptno=30; ExecutionPlan ---------------------------------------------------------- Planhashvalue:2852011669 --------------------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time| --------------------------------------------------------------------------------------- |0|SELECTSTATEMENT | |1|22|2 (0)|00:00:01| |1|TABLEACCESSBYINDEXROWID|DEPT|1|22|2 (0)|00:00:01| |*2|INDEXUNIQUESCAN |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>dropindexidx_dept_loc; Indexdropped. scott@TEST>select/*+index(deptidx_dept_loc)*/deptno,dnamefromdeptwhereloc='CHICAGO'; ExecutionPlan ---------------------------------------------------------- Planhashvalue:3383998547 -------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time | -------------------------------------------------------------------------- |0|SELECTSTATEMENT| |10|300|29(0)|00:00:01| |*1|TABLEACCESSFULL|DEPT|10|300|29(0)|00:00:01| --------------------------------------------------------------------------
从上面的执行计划可以看出走的是对表DEPT的TABLE ACCESS FULL，Hint也是失效的。
再来看一个使用组合Hint的例子，先看如下sql的执行计划
scott@TEST>select/*+full(dept)parallel(dept2)*/deptnofromdept; ExecutionPlan ---------------------------------------------------------- Planhashvalue:587379989 -------------------------------------------------------------------------------------------------------------- |Id|Operation |Name |Rows |Bytes|Cost(%cpu)|Time |TQ|IN-OUT|PQDistrib| -------------------------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT| |1000|13000|16(0)|00:00:01| | | | |1|PXCOORDINATOR| | | | | | | | | |2|PXSENDQC(RANDOM)|:TQ10000|1000|13000|16(0)|00:00:01|Q1,00|P->S|QC(RAND)| |3|PXBLOCKITERATOR| |1000|13000|16(0)|00:00:01|Q1,00|PCWC| | |4|TABLEACCESSFULL|DEPT |1000|13000|16(0)|00:00:01|Q1,00|PCWP| | -------------------------------------------------------------------------------------------------------------- ......
从上面输出内容可以看出，现在是对表DEPT做的并行全表扫描，说明组合Hint中的两个都生效了，这个Hint的含义是既要全表扫描又要并行访问表DEPT，两者不矛盾，因为全表扫描可以并行执行。再看如下的sql：
scott@TEST>select/*+index(deptpk_dept)parallel(dept2)*/deptnofromdept; 4rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2913917002 ---------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time | ---------------------------------------------------------------------------- |0|SELECTSTATEMENT| |1000|13000| 26(0)|00:00:01| |1|INDEXFULLSCAN|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.locfromempt1,deptt2wheret1.deptno=t2.deptnoandt2.loc='CHICAGO'; 6rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:615168685 --------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| --------------------------------------------------------------------------- |0|SELECTSTATEMENT||5|185|7(15)|00:00:01| |*1|HASHJOIN||5|185|7(15)|00:00:01| |*2|TABLEACCESSFULL|DEPT|1|11|3(0)|00:00:01| |3|TABLEACCESSFULL|EMP|14|364|3(0)|00:00:01| -------------------------------------------------------------------------- -
但是如果把sql修改为如下则use_hash的Hint就会被忽略
scott@TEST>select/*+use_hash(t1)*/t1.empno,deptt2wheret1.deptno>t2.deptnoandt2.loc='CHICAGO'; norowsselected ExecutionPlan ---------------------------------------------------------- Planhashvalue:4192419542 --------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| --------------------------------------------------------------------------- |0|SELECTSTATEMENT||1|37|6(0)|00:00:01| |1|NESTEDLOOPS||1|37|6(0)|00:00:01| |*2|TABLEACCESSFULL|DEPT|1|11|3(0)|00:00:01| |*3|TABLEACCESSFULL|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(deptpk_dept)*/deptnofromdept; 4rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2578398298 -------------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time| -------------------------------------------------------------------------------- |0|SELECTSTATEMENT| |4|12|2 (0)|00:00:01| |1|INDEXFASTFULLSCAN|PK_DEPT|4|12|2 (0)|00:00:01| -------------------------------------------------------------------------------- ...... scott@TEST>select/*+full(dept)*/deptnofromdept; 4rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:3383998547 -------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time | -------------------------------------------------------------------------- |0|SELECTSTATEMENT| |4|12|3(0)|00:00:01| |1|TABLEACCESSFULL|DEPT|4|12|3(0)|00:00:01| --------------------------------------------------------------------------
从上面的输出可以看出单独使用上面的两个Hint都能被Oracle生效，但如果这两个Hint合并到一起使用就不是那么回事了：
scott@TEST>select/*+index_ffs(deptpk_dept)full(dept)*/deptnofromdept; 4rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2913917002 ---------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time | ---------------------------------------------------------------------------- |0|SELECTSTATEMENT| | 4| 12| 1(0)|00:00:01| |1|INDEXFULLSCAN|PK_DEPT| 4| 12| 1(0)|00:00:01| ----------------------------------------------------------------------------
从上面的输出可以看出执行计划没有走Hint中指定的执行计划，而是对主键索引PK_DEPT做的是INDEX FULL SCAN这说明Hint中的两个都失效了。
再来看下面的例子：
scott@TEST>select/*+index_ffs(deptpk_dept)full(dept)cardinality(dept1000)*/deptnofromdept; 4rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2913917002 ---------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time | ---------------------------------------------------------------------------- |0|SELECTSTATEMENT| |1000|3000| 1(0)|00:00:01| |1|INDEXFULLSCAN|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>createtablejobsasselectempno,jobfromemp; Tablecreated.
构造一个sql
select/*+orderedcardinality(e100)*/ e.ename,j.job,e.sal,v.avg_sal fromempe,jobsj,(select/*+merge*/ e.deptno,avg(e.sal)avg_sal fromempe,deptd whered.loc='chicago' andd.deptno=e.deptno groupbye.deptno)v wheree.empno=j.empno ande.deptno=v.deptno ande.sal>v.avg_sal orderbye.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/*+orderedcardinality(e100)*/ 2e.ename,v.avg_sal 3fromempe,4jobsj,5(select/*+merge*/ 6e.deptno,avg(e.sal)avg_sal 7fromempe,deptd 8whered.loc='chicago' 9andd.deptno=e.deptno 10groupbye.deptno)v 11wheree.empno=j.empno 12ande.deptno=v.deptno 13ande.sal>v.avg_sal 14orderbye.ename; norowsselected ExecutionPlan ---------------------------------------------------------- Planhashvalue:930847561 ------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| ------------------------------------------------------------------------------- |0|SELECTSTATEMENT||156|19656|15(20)|00:00:01| |*1|FILTER|||||| |2|SORTGROUPBY||156|19656|15(20)|00:00:01| |*3|HASHJOIN||156|19656|14(15)|00:00:01| |*4|TABLEACCESSFULL|DEPT|1|11|3(0)|00:00:01| |*5|HASHJOIN||467|53705|10(10)|00:00:01| |6|TABLEACCESSFULL|EMP|14|364|3(0)|00:00:01| |*7|HASHJOIN||100|8900|7(15)|00:00:01| |8|TABLEACCESSFULL|EMP|100|5800|3(0)|00:00:01| |9|TABLEACCESSFULL|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/*+orderedcardinality(e100)*/ 2e.ename,5(select/*+no_merge*/ 6e.deptno,deptd 8whered.loc='chicago' 9andd.deptno=e.deptno 10groupbye.deptno)v 11wheree.empno=j.empno 12ande.deptno=v.deptno 13ande.sal>v.avg_sal 14orderbye.ename; norowsselected ExecutionPlan ---------------------------------------------------------- Planhashvalue:2898000699 -------------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| -------------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||8|728|14(22)|00:00:01| |1|SORTORDERBY||8|728|14(22)|00:00:01| |*2|HASHJOIN||8|728|13(16)|00:00:01| |*3|HASHJOIN||100|6500|7(15)|00:00:01| |4|TABLEACCESSFULL|EMP|100|4600|3(0)|00:00:01| |5|TABLEACCESSFULL|JOBS|14|266|3(0)|00:00:01| |6|VIEW||5|130|6(17)|00:00:01| |7|HASHGROUPBY||5|185|6(17)|00:00:01| |8|MERGEJOIN||5|185|6(17)|00:00:01| |*9|TABLEACCESSBYINDEXROWID|DEPT|1|11|2(0)|00:00:01| |10|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01| |*11|SORTJOIN||14|364|4(25)|00:00:01| |12|TABLEACCESSFULL|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>selectkeyword,lengthfromv$reserved_wordswherekeywordin(',','THIS','IS','COMMENT');
KEYWORDLENGTH
--------------------,1
COMMENT7
IS2
下面来看一个保留关键字导致Hint失效的实例，执行下面的sql
scott@TEST>selectt1.empno,deptt2wheret1.deptno=t2.deptno; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:844388907 ---------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| ---------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||14|518|6(17)|00:00:01| |1|MERGEJOIN||14|518|6(17)|00:00:01| |2|TABLEACCESSBYINDEXROWID|DEPT|4|44|2(0)|00:00:01| |3|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01| |*4|SORTJOIN||14|364|4(25)|00:00:01| |5|TABLEACCESSFULL|EMP|14|364|3(0)|00:00:01| ----------------------------------------------------------------------------------------
从执行计划上看走的是MERGE SORT JOIN，对sql加入如下Hint并执行：
scott@TEST>select/*+use_hash(t1)index(t2pk_dept)*/t1.empno,deptt2wheret1.deptno=t2.deptno; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2622742753 ---------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| ---------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||14|518|6(17)|00:00:01| |*1|HASHJOIN||14|518|6(17)|00:00:01| |2|TABLEACCESSBYINDEXROWID|DEPT|4|44|2(0)|00:00:01| |3|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01| |4|TABLEACCESSFULL|EMP|14|364|3(0)|00:00:01| ----------------------------------------------------------------------------------------
从上面的执行计划中可以看出Hint中的两个都生效了，emp做HASH JOIN的被驱动表，对DEPT表做使用索引PK_DEPT。现在对Hint加入','，查看执行情况:
scott@TEST>select/*+use_hash(t1),index(t2pk_dept)*/t1.empno,deptt2wheret1.deptno=t2.deptno; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:615168685 --------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| --------------------------------------------------------------------------- |0|SELECTSTATEMENT||14|518|7(15)|00:00:01| |*1|HASHJOIN||14|518|7(15)|00:00:01| |2|TABLEACCESSFULL|DEPT|4|44|3(0)|00:00:01| |3|TABLEACCESSFULL|EMP|14|364|3(0)|00:00:01| ---------------------------------------------------------------------------
从执行计划中可以看出，仍然走的是HASH JOIN但是index(t2 pk_dept)失效了。因为','是Oracle的保留关键字，所以','后面的index(t2 pk_dept)失效了，再修改Hint如下并执行sql：
scott@TEST>select/*+commentuse_hash(t1)index(t2pk_dept)*/t1.empno,deptt2wheret1.deptno=t2.deptno; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:844388907 ---------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| ---------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||14|518|6(17)|00:00:01| |1|MERGEJOIN||14|518|6(17)|00:00:01| |2|TABLEACCESSBYINDEXROWID|DEPT|4|44|2(0)|00:00:01| |3|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01| |*4|SORTJOIN||14|364|4(25)|00:00:01| |5|TABLEACCESSFULL|EMP|14|364|3(0)|00:00:01| ----------------------------------------------------------------------------------------
从执行计划中看出，现在走的是跟一开始的执行计划一样，说明Hint中的两个都失效了，因为这两个都在Oracle保留关键字comment后面。再修改Hint如下再次执行sql:
scott@TEST>select/*+thisuse_hash(t1)index(t2pk_dept)*/t1.empno,deptt2wheret1.deptno=t2.deptno; 14rowsselected. ExecutionPlan ---------------------------------------------------------- Planhashvalue:2622742753 ---------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| ---------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||14|518|6(17)|00:00:01| |*1|HASHJOIN||14|518|6(17)|00:00:01| |2|TABLEACCESSBYINDEXROWID|DEPT|4|44|2(0)|00:00:01| |3|INDEXFULLSCAN|PK_DEPT|4||1(0)|00:00:01| |4|TABLEACCESSFULL|EMP|14|364|3(0)|00:00:01| ----------------------------------------------------------------------------------------
现在执行计划又走出了Hint指定的样子，说明两个都生效了，这是因为this不是Oracle保留关键字。
以上介绍了5种Hint被Oracle忽略的情况，在实例使用过程中一定要注意使用方法，使用正确有效的Hint来提升sql执行效率，避免Hint被Oracle忽略。
参考《基于Oracle的sql优化》
原文链接：https://www.f2er.com/oracle/209999.html