oracle中hint使用小结

在阅读本文之前,建议先阅读下《Oracle RBO、CBO小结》这篇文章,阅读理解了那篇文章以后,就可以明白为什么我们要使用hint,使用hint能带来那些好处以及坏处。hint是oracle提供的一种SQL语法,它允许用户在SQL语句中插入相关的语法,从而影响SQL的执行方式。由于hint的特殊性,所以对于开发人员来说不应该在代码中使用它,hint更像是oracle提供给DBA用来分析问题的工具。在SQL代码中使用hint,可能导致非常严重的后果,因为数据库的数据是变化的,在某一时刻这个执行计划可能是最优的,但是在另一个时刻,却可能是最差的,这也是CBO取代RBO的原因之一,规则是死的,而数据是时刻变化的,为了获得最正确的执行计划,只有知道表中数据的实际情况,通过计算各种执行计划的成本,则其最优,才是最科学的,这也是CBO的工作机制。在SQL代码中加入hint,特别是性能相关的hint是很危险的做法。

hint是oracle提供的一种机制,用来告诉优化器按照我们告诉它的方式生成执行计划,我们可以用hint来实现:

(1)使用的优化器的类型。

(2)基于代价的优化器的优化目标,是all_rows还是first_rows。

(3)表的访问路径,是全表扫描,还是索引扫描,还是直接利用rowid。

(4)表之间的连接类型。

(5)表之间的连接顺序。

(6)语句的并行程度。

如果hint在书写的时候有问题,那么oracle会自动忽略缩写的hint,不报错。一般常用的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)*/
  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;

(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.emp_nam,
 b.dpt_no
  FROM bsempms a(selet dpt_no, AVG(sal) AS avg_sal FROM bsempms b GROUP BY dpt_no) v
 WHERE a.dpt_no = v.dpt_no
   AND a.sal > v.avg_sal;

(20)/*+NO_MERGE(TABLE)*/

对于有可合并的视图不再合并。例如:

SELECT /*+NO_MERGE(V) */
 a.emp_no,
 a.emp_nam,
 b.dpt_no
  FROM bsempms a (SELECT dpt_no,
                         AVG(sal) AS avg_sal
                    FROM bsempms b
                   GROUP BY dpt_no) v
 WHERE a.dpt_no = v.dpt_no
   AND a.sal > v.avg_sal;

(21)/*+ORDERED*/

根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接。例如:

SELECT /*+ORDERED*/
 a.col1,
 b.col2,
 c.col3
  FROM table1 a,
       table2 b,
       table3 c
 WHERE a.col1 = b.col1
   AND b.col1 = c.col1;

(22)/*+USE_NL(TABLE)*/

将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表。例如:

SELECT /*+ORDERED USE_NL(BSEMPMS)*/
 bsdptms.dpt_no,
 bsempms.emp_no,
 bsempms.emp_nam
  FROM bsempms,
       bsdptms
 WHERE bsempms.dpt_no = bsdptms.dpt_no;

(23)/*+USE_MERGE(TABLE)*/

将指定的表与其他行源通过合并排序连接方式连接起来。例如:

SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/
 *
  FROM bsempms,
       bsdptms
 WHERE bsempms.dpt_no = bsdptms.dpt_no;

(24)/*+USE_HASH(TABLE)*/

将指定的表与其他行源通过哈希连接方式连接起来。例如:

SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/
 *
  FROM bsempms,
       bsdptms
 WHERE bsempms.dpt_no = bsdptms.dpt_no;

(25)/*+DRIVING_SITE(TABLE)*/

强制与ORACLE所选择的位置不同的表进行查询执行。例如:

SELECT /*+DRIVING_SITE(DEPT)*/
 *
  FROM bsempms,
       dept@bsdptms
 WHERE bsempms.dpt_no = dept.dpt_no;

(26)/*+LEADING(TABLE)*/

将指定的表作为连接次序中的首表。

(27)/*+CACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端。例如:

SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */
 emp_nam
  FROM bsempms;

(28)/*+NOCACHE(TABLE)*/

当进行全表扫描时,CACHE提示能够将表的检索块放置在缓冲区缓存中最近最少列表LRU的最近使用端。例如:

SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */
 emp_nam
  FROM bsempms;

(29)/*+APPEND*/

直接插入到表的最后,可以提高速度。

INSERT /*+append*/
INTO test1
  SELECT *
    FROM test4;

(30)/*+NOAPPEND*/

通过在插入语句生存期内停止并行模式来启动常规插入。

INSERT /*+noappend*/
INTO test1
  SELECT *
    FROM test4;

(31)/*+NO_INDEX(TABLE_NAME INDEX_NAME)*/

指定不使用哪些索引。

SELECT /*+ no_index(emp ind_emp_sal ind_emp_deptno)*/
 *
  FROM emp
 WHERE deptno = 200
   AND sal > 300;

(32)/*+PARALLEL*/

SELECT /*+ parallel(emp,4)*/
 *
  FROM emp
 WHERE deptno = 200
   AND sal > 300;

每个SELECT/INSERT/UPDATE/DELETE命令后只能有一个/*+ */,但提示内容可以有多个,可以用逗号分开,空格也可以。例如:

/*+ ordered index() use_nl() */

参考资料:

1. ORACLE的HINT详解

本文标题:oracle中hint使用小结

本文链接:http://yedward.net/?id=170

本文版权归作者所有,欢迎转载,转载请以文字链接的形式注明文章出处。

相关文章