优化器提示

提示（Hint）概念

一般在优化时，无论采用基于规则的或是基于代价的方法，由Oracle 系统的优化器来决定语

句的执行路径。这样的选择的路径不要见得是最好的。所以，Oracle 提供了一种方法叫提示的

方法。它可以让编程人员按照自己的要求来选择执行路径，即提示优化器该按照什么样的执

行规则来执行当前的语句。这样可以在性能上比起Oracle 优化自主决定要好些。

通常情况下，编程人员可以利用提示来进行优化决策。通过运用提示可以对下面内容进行指

定：

 SQL 语句的优化方法；

 对于某条SQL 语句，基于开销优化程序的目标；

 SQL 语句访问的访问路径；

 连接语句的连接次序；

 连接语句中的连接操作

提示的指定

如果希望优化器按照编程人员的要求执行，则要在语句中给出提示。提示的有效范围有

限制，即有提示的语句块才能按照提示要求执行。下面语句可以指定提示：

 简单的SELECT ,UPDATE ,DELETE 语句；

 复合的主语句或子查询语句；

 组成查询（UNION）的一部分。

提示的指定有原来的注释语句在加“+”构成。语法如下：

[ SELECT | DELETE|UPDATE ] /*+ [hint | text ] */

或

[ SELECT | DELETE|UPDATE ] --+ [hint | text ]

注意在“/*”后不要空就直接加“+”，同样 “--+”也是连着写。

警告：如果该提示语句书写不正确，则 Oracle 就忽略掉该语句。

指定完整的提示：

对于复杂的语句，要用/*+ */来指定，它可以指定多个提示语句。且可以换行。

SELECT /*+ ORDERED INDEX (b, jl_br_balances_n1) USE_NL (j b)

USE_NL (glcc glf) USE_MERGE (gp gsb) */

b.application_id ,

b.set_of_books_id ,

b.personnel_id,

p.vendor_id Personnel,

p.segment1 PersonnelNumber,

p.vendor_name Name

FROM jl_br_journals j,

jl_br_balances b,

gl_code_combinations glcc,

fnd_flex_values_vl glf,

gl_periods gp,

gl_sets_of_books gsb,

po_vendors p

WHERE . . . . . . . . . . . .

提示的指定

使用提示，用户可以在基于开销的优化方法和基于规则的优化方法之间选择，由此可以

对要求“最佳吞吐量”与“最佳响应时间”优化目标进行选择。优化方法如下：

 ALL_ROWS

 FIRST_ROWS

 CHOOSE

 RULE

如果某条语句已经指定了优化方法和优化目标后，则Oracle 的优化器就按照指定的优化方法

和目标进行执行。并且不考虑：

１） 是否存在统计信息；

２） 初始化参数OPTIMIZER_MODE 的取值；

３） ALTER SESSION 语句中OPTIMIZER_MODE 参数值。

ALL_ROWS

ALL_ROWS 表示对语句块选择基于开销的优化方法，并且获得最佳的吞吐量（资源消耗

总量最小）作为目标进行优化。语法如下：

/*+ ALL_ROWS */

例子：在查询EMP 表希望用基于开销的优化方法，并获得最佳吞吐量，则使用下面语句：

SELECT /*+ ALL_ROWS */empno,ename,sal,job

FROM emp WHERE empno=7566;

FIRST_ROWS

FIRST_ROWS 表示对语句块选择基于开销的优化方法，并且获得最佳的响应（返回首行

的资源最小化）作为目标进行优化。

使用FIRST_ROWS 优化方法，优化器可能要进行下面工作：

 如果能利用索引扫描，则不进行全表扫描；

 当关联表是嵌套循环的内部表且能用索引扫描，则优化器先优化嵌套循环联结。

 如果通过order by 使得索引扫描可用，则优化器选择索引扫描以避免排序操作。

语法如下：

/*+ FIRST_ROWS */

例子：选择基于开销的优化方法，并希望获得最佳的响应时间，则：

SELECT /*+ FIRST_ROWS */empno,ename,sal,job

FROM emp

WHERE empno=7566;

CHOOSE

选择CHOOSE 表示告诉优化器要在基于开销和基于规则之间进行选择。优化器的确定要

建立在是否存在访问表的统计信息之上：

 如果数据字典中存在该表的统计数据，则选择基于开销，并以最佳吞吐量作为目标。

 如果数据字典中不存在该表的统计数据，则选择基于规则。

语法为：

/*+ CHOOSE */

例子：

SELECT /*+ CHOOSE */ empno,sal,job FROM emp WHERE empno=7566;

RULE

表示要求优化器对语句块选择基于规则的优化方法。语法如下：

/*+ RULE */

例子：

SELECT --+ RULE empno,ename,sal,job

FROM emp WHERE empno=7655;

或

SELECT /*+ RULE */ empno,ename,sal,job

FROM emp WHERE empno=7655;

访问方法共有：

 FULL

 ROWID

 CLUSTER

 HASH

 INDEX

 INDEX_ASC

 INDEX_COMBINE

 INDEX_JOIN

 INDEX_DESC

 INDEX_FFS

 NO_INDEX

 AND_EQUAL

 USE_CONCAT

 NO_EXPAND

 REWRITE

 NOREWRITE

如果在语句中指定了上面的提示，并且语句所涉及的索引或簇是可用时，优化器就使用所指

定的访问路径。否则，优化器就忽略提示的要求。

FULL

FULL 提示表示对表选择全表扫描的访问方法。语法如下：

/*+ FULL ( [table_name]| [table_aliase] ) */

例：

SELECT /*+ FULL(A) don’t use the index on accno */ accno,bal

FROM accounts a

WHERE accno=7789;

虽然这里使用了带索引的条件句，优化器也得选择全表扫描。

ROWID

ROWID 表示对指定表选择根据rowid 进行表扫描，语法如下：

/*+ ROWID( table_name ) */

例：

SELECT /*+ROWID(emp)*/ * FROM emp

WHERE rowid>’AAAATKAABAAAFNTAAA’ AND empno=155;

CLUSTER

CLUSTER 表示对指定表选择簇扫描的访问方法。它仅对CLUSTER 对象有效。语法如

下：

/*+CLUSTER(table_name) */

例：

SELECT --+CLUSTER emp.ename, deptno

FROM emp,dept

WHERE deptno=10 AND emp.deptno=dept.deptno;

HASH

HASH 表示对指定的表选择HASH 扫描访问方法，它只对CLUSTER 中的表有效。语法

如下：

/*+HASH(table_name)*/

INDEX

INDEX 表示对指定表选择索引扫描的访问方法。用户可以对域、B*树和位图索引应用本

提示。对于建立了位图的索引，建议用INDEX_COMBINE 更为合适。语法如下：

/*+INDEX(table_name [index]) */

例：

SELECT /*+INDEX(patients sex_index)use sex_index because there are few male patients*/

Name,height,weight FROM patients WHERE sex=’m’;

INDEX_ASC

INDEX_ASC 表示对指定的表选择索引的访问方法，并按照升序进行扫描。语法如下：

/*+INDEX_ASC(table_name [index])*/

INDEX_COMBINE

INDEX_COMBINE 表示对指定的表选择位图访问路径。

 如果没有提供可参考的索引，则优化器以最低开销为目标，选择位图索引的布尔组合

方式；

 如果有可参考的索引，则优化器就使用该位图的某写布尔组合。

语法如下：

/*+INDEX_COMBINE(table_name[index])*/

例：

SELECT /*+INDEX_COMBINE(emp sal_bmi hiredate_bmi)*/*

FROM emp

WHERE sal<5000 AND hiredate ,’01-JAN-1990’;

INDEX_JOIN

INDEX_JOIN 表示使用索引连接作为访问路径。语法如下：

/*+INDEX_JOIN(table_name[index])*/

例：

SELECT /*+INDEX_JOIN(emp sal_bmi hiredate_bmi)*/sal,hirdate

FROM emp

WHERE sal<5000 ;

INDEX_DESC

INDEX_DESC 表示对指定表选择索引访问方法。如果使用索引区域扫描，则按照降序进

行扫描。语法如下：

/*+INDEX_DESC(table_name[index])*/

INDEX_FFS

INDEX_DESC 表示对指定表选择快速索引访问方法（不是全表扫描）。语法如下：

/*+INDEX_FFS(table_name[index])*/

例：

SELECT /*+INDEX_FFS(emp emp_empnp)*/ empno

FROM emp

WHERE empno>200;

NO_INDEX

NO_INDEX 表示对指定表禁止选择索引访问方法。语法如下：

/*+NO_INDEX(table_name[index])*/

例：

SELECT /*+NO_INDEX(emp emp_empnp)*/ empno

FROM emp

WHERE empno>200;

AND_EQUAL

AND-EQUAL 表示要进行执行规则的选择。使几个列的索引的扫描合并起来。语法如

下：

/*+AND_EQUAL(table_name[index] [inex]…)*/

USE_CONCAT

USE_CONCAT 提示强制对查询语句中的WHERE 从句的OR 条件进行转换，转化成由

UNION_ALL 集合操作符连接的组合查询。一般来说，如果采用连接查询比不用连接查询低，

则转换为用连接查询。

/*+USE_COMCAT*/

例：

SELECT /*USE_CONCAT*/* FROM emp

WHERE empno>50 OR sal<50000;

NO_EXPAND

NO_EXPAND 对于具有OR 或IN 查询语句，它将阻止基于开销的优化器对其进行OR 扩

展。语法如下：

/*+NO_EXPAND*/

例：

SELECT /*+NO_EXPAND*/*

FROM emp

WHERE empno=50 OR empno=100;

REWRITE

REWRITE 表示可以将视图列表作为参数来看，如果用户使用REWRITE，并且该列表包

含有符合条件的实体化视图，则Oracle 优化器将利用该视图而不用基于开销的方法。而对于

列表以外的视图将不被考虑。如果在REWRITE 中没有给出视图列表，则Oracle 将搜索符合

条件的实体化视图。并且利用该视图。语法如下：

/*+REWRITE(view,[view]…)*/

NOWRITE

NOWRITE 表示禁止对查询块的查询重写操作， 从而避免参数

QUERY_REWRITE_ENABLE 的设置。语法如下：

/*+NOWRITE*/

关于连接次序的提示

ORDERED

根据出现在FROM 中顺序，ORDERED 提示将使得Oracle 依此次序对其进行连接。语法

如下：

/*+ORDERED*/

例：

SELECT /*+ORDERED*/tab1.col1,tabl2.col2,tab3.col3

FROM tab1,tab2,tab3

WHERE tab1.col1=tab2.col1 AND tab2.col1=tab3.col1;

STAR

强行让优化器使用星型查询规划。星型规划拥有查询中最大的一个表，该表位于连接次

序的最后，并与嵌套式循环连接的级联索引连接。如果满足下面３个条件：

１）至少存在３个表；

２）最大表的级联索引至少存在３列；

３）不存在冲突的访问或连接访问提示。

/*+ STAR */

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26015009/viewspace-746386/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26015009/viewspace-746386/