运用HINT提高SQL语句的执行效率

运用HINT提高SQL语句的执行效率

hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标，是all_rows还是first_rows。
3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度

1、写HINT目的
手工指定SQL语句的执行计划
hints是oracle提供的一种机制，用来告诉优化器按照我们的告诉它的方式生成执行计划。我们可以用hints来实现：
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标，是all_rows还是first_rows。
3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度

2、HINT可以基于以下规则产生作用
表连接的顺序、表连接的方法、访问路径、并行度

3、HINT应用范围
dml语句
查询语句

4、语法
/*+ hint [text] [hint[text]]... */
or
--+ hint [text] [hint[text]]...
如果语（句）法不对，则ORACLE会自动忽略所写的HINT，不报错

5、指定优化器模式的HINT
RULE：不管是否有统计信息，都将采用基于规则进行优化；
CHOOSE：只要被访问的数据中有一个表有统计信息，就将采用基于代价的方式进行优化；
FIRST_ROWS：不管是否有统计信息，都将采用基于代价的方式进行优化，其优化目标是最快响应时间；
ALL_ROWS：不管是否有统计信息，都将采用基于代价的方式进行优化，其优化目标是最大吞吐量；
例子：
尽快地显示前10行记录
select /*+ first_rows(10) */ * from emp where deptno=10;

6、指定访问路径的HINT
FULL: 执行全表扫描
/*+ FULL ( table ) */
ROID: 根据ROWID进行扫描
/*+ ROWID ( table ) */
INDEX: 根据某个索引进行扫描
/*+ INDEX ( table [index [index]...] ) */
select /*+ index(emp ind_emp_sal)*/ * from emp where deptno=200 and sal>300;
如果写了多个，则ORACLE自动选择最优的哪个
select /*+ index(emp ind_emp_sal ind_emp_deptno)*/ * from emp where deptno=200 and sal>300;
INDEX_JOIN: 如果所选的字段都是索引字段(是几个索引的)，那么可以通过索引连接就可访问到数据，而不需要访问表的数据。
/*+ INDEX_JOIN ( table [index [index ...]] ) */
select /*+ index_join(emp ind_emp_sal ind_emp_deptno)*/ deptno,sal from emp where deptno=20;
INDEX_FFS: 执行快速全索引扫描
/*+ INDEX_FFS ( table [index [index]...] ) */
select /*+ index_ffs(emp pk_emp)*/ count(*) from emp;
NO_INDEX: 指定不使用哪些索引
/*+ NO_INDEX ( table [index [index]...] ) */
select /*+ no_index(emp ind_emp_sal ind_emp_deptno)*/ * from emp where deptno=200 and sal>300;
AND_EQUAL: 指定合并两个或以上索引检索的结果(交集)，最多不能超过5个
/*+ AND_EQUAL ( table index index [index] [index] [index] ) */

7、指定表的连接顺序
ORDERED: 按表出现的顺序进行连接
/*+ ORDERED */
select /*+ordered*/ emp.ename,dept.dname from dept,emp where emp.deptno=dept.deptno;
select /*+ordered*/ emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno;
8、指定表的连接操作
USE_NL: 按nested loops方式连接
--默认hash join，获取所有数据的最快返回时间
select emp.ename,dept.dname from dept,emp where emp.deptno=dept.deptno;
--指定emp作为inner table ,以获取最快的响应时间
select /*+ordered use_nl(emp) to get first row faster */ emp.ename,dept.dname from dept,emp where emp.deptno=dept.deptno;
select /*+ordered use_nl(emp dept)*/ emp.ename,dept.dname from dept,emp where emp.deptno=dept.deptno;

在优化调整数据库的SQL时候,经常会用到HINT提示.目前ORACLE支持的HINT如下:

在SQL语句优化过程中，我们经常会用到hint,现总结一下在SQL优化过程中常见Oracle HINT的用法：

1. /*+ALL_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳吞吐量,使资源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2. /*+FIRST_ROWS*/
表明对语句块选择基于开销的优化方法,并获得最佳响应时间,使资源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

3. /*+CHOOSE*/
表明如果数据字典中有访问表的统计信息,将基于开销的优化方法,并获得最佳的吞吐量;
表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

4. /*+RULE*/
表明对语句块选择基于规则的优化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+FULL(TABLE)*/
表明对表选择全局扫描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

6. /*+ROWID(TABLE)*/
提示明确表明对指定表根据ROWID进行访问.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';

7. /*+CLUSTER(TABLE)*/
提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/
表明对表选择索引的扫描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明对表选择索引升序的扫描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

10. /*+INDEX_COMBINE*/
为指定表选择位图访问路经,如果INDEX_COMBINE中没有提供作为参数的索引,将选择出位图索引的布尔组合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE<SYSDATE;

11. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
提示明确命令优化器使用索引作为访问路径.
例如:
SELECT /*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
FROM BSEMPMS WHERE SAL<60000;

12. /*+INDEX_DESC(TABLE INDEX_NAME)*/
表明对表选择索引降序的扫描方法.
例如:
SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

13. /*+INDEX_FFS(TABLE INDEX_NAME)*/
对指定的表执行快速全索引扫描,而不是全表扫描的办法.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

14. /*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,...*/
提示明确进行执行规划的选择,将几个单列索引的扫描合起来.
例如:
SELECT /*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*/ * FROM BSEMPMS WHERE EMP_NO='SCOTT' AND DPT_NO='TDC306';

15. /*+USE_CONCAT*/
对查询中的WHERE后面的OR条件进行转换为UNION ALL的组合查询.
例如:
SELECT /*+USE_CONCAT*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

16. /*+NO_EXPAND*/
对于WHERE后面的OR 或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展.
例如:
SELECT /*+NO_EXPAND*/ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

17. /*+NOWRITE*/
禁止对查询块的查询重写操作.

18. /*+REWRITE*/
可以将视图作为参数.

19. /*+MERGE(TABLE)*/
能够对视图的各个查询进行相应的合并.
例如:
SELECT /*+MERGE(V) */ A.EMP_NO,A.E