Oracle中常见的Hint

Oracle中的Hint可以用来调整SQL的执行计划,提高SQL执行效率。下面分类介绍Oracle数据库中常见的Hint。这里描述的是Oracle11gR2中的常见Hint,Oracle数据库中各个版本中的Hint都不尽相同,所以这里讲述的的Hint可能并不适用于Oracle早期的版本。

一、与优化器模式相关的Hint

1、ALL_ROWS

ALL_ROWS是针对整个目标SQL的Hint,它的含义是让优化器启用CBO,而且在得到目标SQL的执行计划时会选择那些吞吐量最佳的执行路径。这里的“吞吐量最佳”是指资源消耗量(即对I/O、CPU等硬件资源的消耗量)最小,也就是说在ALL_ROWS Hint生效的情况下,优化器会启用CBO而且会依据各个执行路径的资源消耗量来计算它们各自的成本。

ALL_ROWS Hint的格式如下:

/*+ ALL_ROWS */

使用范例:

select /*+ all_rows */ empno,ename,sal,job
  from emp
 where empno=7396;

从Oracle10g开始,ALL_ROWS就是默认的优化器模式,启用的就是CBO。

scott@TEST>show parameter optimizer_mode
 
NAME                    TYPE                VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_mode              string                  ALL_ROWS

如果目标SQL中除了ALL_ROWS之外还使用了其他与执行路径、表连接相关的Hint,优化器会优先考虑ALL_ROWS。

2、FIRST_ROWS(n)

FIRST_ROWS(n)是针对整个目标SQL的Hint,它的含义是让优化器启用CBO模式,而且在得到目标SQL的执行计划时会选择那些能以最快的响应时间返回头n条记录的执行路径,也就是说在FIRST_ROWS(n) Hint生效的情况下,优化器会启用CBO,而且会依据返回头n条记录的响应时间来决定目标SQL的执行计划。

FIRST_ROWS(n)格式如下:

/*+ FIRST_ROWS(n) */

使用范例

select /*+ first_rows(10) */ empno,ename,sal,job
  from emp
 where deptno=30;

上述SQL中使用了/*+ first_rows(10) */,其含义是告诉优化器我们想以最短的响应时间返回满足条件"deptno=30"的前10条记录。

注意,FIRST_ROWS(n) Hint和优化器模式FIRST_ROWS_n不是一一对应的。优化器模式FIRST_ROWS_n中的n只能是1、10、100、1000。但FIRST_ROWS(n) Hint中的n还可以是其他值。

scott@TEST>alter session set optimizer_mode=first_rows_9;
ERROR:
ORA-00096: invalid value FIRST_ROWS_9 for parameter optimizer_mode, must be from among first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows, choose,rule
 
scott@TEST>set autotrace traceonly
scott@TEST>select /*+ first_rows(9) */ empno from emp;
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
 
---------------------------------------------------------------------------
| Id  | Operation    | Name  | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|  0 | SELECT STATEMENT |    |  9 |    36 |  1  (0)| 00:00:01 |
|  1 |  INDEX FULL SCAN | PK_EMP |  9 |    36 |  1  (0)| 00:00:01 |
---------------------------------------------------------------------------

如果在UPDATE、DELETE或者含如下内容的查询语句中使用了FIRST_ROWS(n) Hint,则该Hint会被忽略:

集合运算(如UNION,INTERSACT,MINUS,UNION ALL等)

GROUP BY

FOR UPDATE

聚合函数(比如SUM等)

DISTINCT

ORDER BY(对应的排序列上没有索引)
这里优化器会忽略FIRST_ROWS(n) Hint是因为对于上述类型的SQL而言,Oracle必须访问所有的行记录后才能返回满足条件的头n行记录,即在上述情况下,使用该Hint是没有意义的。

3、RULE

RULE是针对整个目标SQL的Hint,它表示对目标SQL启用RBO。

格式如下:

/*+ RULE */

使用范例:

select /*+ rule */ empno,ename,sal,job
  from emp
 where deptno=30;

RULE不能与除DRIVING_SITE以外的Hint联用,当RULE与除DRIVING_SITE以外的Hint联用时,其他Hint可能会失效;当RULE与DRIVING_SITE联用时,它自身可能会失效,所以RULE Hint最好是单独使用。

一般情况下,并不推荐使用RULE Hint。一来是因为Oracle早就不支持RBO了,二来启用RBO后优化器在执行目标SQL时可选择的执行路径将大大减少,很多执行路径RBO根本就不支持(比如哈希连接),就也就意味着启用RBO后目标SQL跑出正确执行计划的概率将大大降低。

因为很多执行路径RBO根本就不支持,所以即使在目标SQL中使用了RULE Hint,如果出现了如下这些情况(包括但不限于),RULE Hint依然会被Oracle忽略。

目标SQL除RULE之外还联合使用了其他Hint(如DRIVING_SITE)。

目标SQL使用了并行执行

目标SQL所涉及的对象有IOT

目标SQL所涉及的对象有分区表
......

二、与表访问相关的Hint

1、FULL

FULL是针对单个目标表的Hint,它的含义是让优化器对目标表执行全表扫描。

格式如下:

/*+ FULL(目标表) */

使用范例:

select /*+ full(emp) */ empno,ename,sal,job
  from emp
 where deptno=30;

上述SQL中Hint的含义是让优化器对目标表EMP执行全表扫描操作,而不考虑走表EMP上的任何索引(即使列EMPNO上有主键索引)。

2、ROIWD

ROIWD是针对单个目标表的Hint,它的含义是让优化器对目标表执行RWOID扫描。只有目标SQL中使用了含ROWID的where条件时ROWID Hint才有意义。

格式如下:

/*+ ROWID(目标表) */

使用范例:

select /*+ rowid(emp) */ empno,ename,sal,job
  from emp
 where rowid='AAAR3xAAEAAAACXAAA';

Oracle 11gR2中即使使用了ROWID Hint,Oracle还是会将读到的块缓存在Buffer Cache中。

三、与索引访问相关的Hint

1、INDEX

INDEX是针对单个目标表的Hint,它的含义是让优化器对目标表的的目标索引执行索引扫描操作。

INDEX Hint中的目标索引几乎可以是Oracle数据库中所有类型的索引(包括B树索引、位图索引、函数索引等)。

INDEX Hint的模式有四种:

格式1 /*+ INDEX(目标表 目标索引) */

格式2 /*+ INDEX(目标表 目标索引1 目标索引2 …… 目标索引n) */

格式3 /*+ INDEX(目标表 (目标索引1的索引列名) (目标索引2的索引列名) …… (目标索引n的索引列名)) */

格式4 /*+ INDEX(目标表) */

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/96cde8cf0e53ab4350c556b7dfbc7aca.html