1.全表扫描hint full(table_name)
相对而言，全表扫描hint使用场合较少，但是要知道，全表扫描并不一定比索引效率低，特别是查询表中80%以上的数据库，全表扫描的效率要高于索引扫描。
2.索引hint index(table_name index_name)
这两种hint一个是强制使用索引，另一个是强制执行计划不要走索引，什么用呢？常用于SQL调优过程中对比索引和非索引扫描。
3.索引快速扫描hint index_ffs(table_name index_name)
这种索引称之为索引快速扫描，常用于统计索引列键值的个数，如count(object_id)，跟全表扫描很像，但效率要比全表扫描要高很多，也就是执行计划中看到的FAST FULL SCN。
4.索引跳跃扫描hint index_ss(table_name index_name)
该hint在执行计划中就是传说中的 INDEX SKIP SCAN 这个对新手而言不太好理解，举个例子索引有两个列（A,B)类型组合索引，但是查询中where条件只有B没有A select * from where b=1，此时ORACLE优化器走的索引就是所谓的索引跳跃扫描，只在CBO下适用，在RBO不适用。
5.表关联hint user_nl(table_name1 table_name2)
此hint是表之间关联效率最高的一种，通常用于一大一小两表之间进行关联查询，小表作驱动表进行全表扫描，大表上要求有索引，走索引扫描，代价最低。
6.表关联hint use_hash(table_name1 table_name2)
如果两个表一大一小，但是大表没有索引就会选择HASH，如果两个结果集比较小还可以承受，但是如果两个较大的表HASH的话，会直接将数据库HANG住，最好避免这种算法
7.表关联hint user_merge(table_name1 table_name2)
两个表进行关联，分别对全个表进行全表扫描后排序然后进行合并，排序既消耗内存又消耗CPU，总之代价比较大，常通过在两个表上创建索引避免此类连接的发生。因此对比后发现，只有nested_loop方式进行关联是最优的。
8.表顺序hint leading(table_name1 table_name2)
在RBO模式下，我们常常通过考虑from 后面表的先后顺序来进行SQL优化，但是此方法对RBO模式不再适用，CBO模式下按照顺序选择驱动表
9.数据加载hint append()
直接路径加载，对于大表 *** 作极为有用，原理是什么呢？打个比方，好比两个超市理货员，一人一箱货需要上架到货架上，一个人去找货架中空闲位置去放，可能需要找N个空闲位，另一个人找一个空的货架直接放上去，那个效果最高？当然是第二个，此hint的作用就是让ORACLE找一个大空亲块直接存放新数据，而不是挤空闲位置去放新数据，如果此hint同时加上nologing联合使用效果更高，常用于数据迁移项目中。
10.dblink处理端hint driving_site(table_name)
此hint常用于通过dblink连接处理数据的业务，它的作用是将本地表推送到远端数据库进行关联然后将结果返回，常用于本地表较小，远端表较大的情况，效果很是不错。
11.数据返回模式hint first_rows
该hint是影响数据返回模式hint，添加后ORACLE将边处理边返回，数据仓库中用的比较多，但是在OLTP系统中也常见，上次系统优化就因为一兄弟在添加hint 时，添加后发现执行计划没变，于是将原有的hint first_rows 然后添加hint driving_site()，执行计划是变了，变化是因去去掉first_rows引起的，并且通过dblink远端数据库执行时查询全变成的全表扫描，导致两个业务大表hash，业务高峰直接将数据库宕机，因此该hint添加或删除一定要看远端执行计划有无发生变化，否则后果不开设想（切记）。
特别需要注意的是，使用hint时切记查看表名是否使用了别名，如果使用了别名，记得要在hint中也要使用别名，否则hint是没有作用的（切记）
前面已经对Hint有了大概的介绍，已经了解到Hint可以影响优化器对于执行计划的选择，但这种影响不是强制性的，优化器在某些情况下可能会忽略目标SQL中的Hint：
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>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也会失效，对于这个失效原因，就举这个例子了。
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: 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合并到一起使用就不是那么回事了
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:
1 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：
1 scott@TEST>select /*+ ordered cardinality(e 100) */ e.ename, j.job, e.sal, v.avg_sal
from emp e, jobs j, (select /*+ no_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
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失效。
以上介绍了5种Hint被Oracle忽略的情况，在实例使用过程中一定要注意使用方法，使用正确有效的Hint来提升SQL执行效率，避免Hint被Oracle忽略
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）