在SQL优化过程中常见HINT的用法

在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 CWHERE 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) */

对指定的表执行快速全索引扫描,而不是全表扫描的办法.要求要检索的列都在索引里,如果表有很多列时特别适用该提示.

例如:SELECT /*+ INDEX_FFS(BSEMPMS IN_EMPNAM) */ * FROM BSEMPMS WHERE DPT_NO='TEC305';

19. /*+ NO_EXPAND */

对于WHERE后面的OR或者IN-LIST的查询语句,NO_EXPAND将阻止其基于优化器对其进行扩展,缩短解析时间.

例如:SELECT /*+ NO_EXPAND */ * FROM BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';

20. /*+ DRIVING_SITE(TABLE) */

强制与ORACLE所选择的位置不同的表进行查询执行.特别适用于通过dblink连接的远程表.

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

21. /*+ CACHE(TABLE) */和/*+ NOCACHE(TABLE) */

当进行全表扫描时,CACHE提示能够将表全部缓存到内存中,这样访问同一个表的用户可直接在内存中查找数据.比较适合数据量小但常被访问的表,也可以建表时指定cache选项这样在第一次访问时就可以对其缓存. NOCACHE则表示对已经指定了CACHE选项的表不进行缓存.

例如:SELECT /*+ FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

22. /*+ PUSH_SUBQ */

当SQL里用到了子查询且返回相对少的行时,该提示可以尽可能早对子查询进行评估从而改善性能,不适用于合并连接或带远程表的连接.

例如:select /*+ PUSH_SUBQ */ emp.empno, emp.ename, itemno from emp, orders where emp.empno = orders.empno and emp.deptno = (select deptno from dept where loc='XXX');

远程连接其他数据库，注意判断数据库是否启动，或者是否有需要的表，否则会出错

23. /*+ INDEX_SS(TABLE INDEX1,INDEX2) */