hints的详细用法(转)

 

基于代价的优化器是很聪明的，在绝大多数情况下它会选择正确的优化器，减轻了DBA的负担。但有时它也聪明反被聪明误，选择了很差的执行计划，使某个语句的执行变得奇慢无比。此时就需要DBA进行人为的干预，告诉优化器使用我们指定的存取路径或连接类型生成执行计划，从而使语句高效的运行。例如，如果我们认为对于一个特定的语句，执行全表扫描要比执行索引扫描更有效，则我们就可以指示优化器使用全表扫描。在ORACLE中，是通过为语句添加hints(提示)来实现干预优化器优化的目的。

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

除了”RULE”提示外，一旦使用的别的提示，语句就会自动的改为使用CBO优化器，此时如果你的数据字典中没有统计数据，就会使用缺省的统计数据。所以建议大家如果使用CBO或HINTS提示，则最好对表和索引进行定期的分析。

　　如何使用hints:

　　Hints只应用在它们所在sql语句块(statement block，由select、update、delete关键字标识)上，对其它SQL语句或语句的其它部分没有影响。如：对于使用union操作的2个sql语句，如果只在一个sql语句上有hints，则该hints不会影响另一个sql语句。

　　我们可以使用注释(comment)来为一个语句添加hints，一个语句块只能有一个注释，而且注释只能放在SELECT, UPDATE, or DELETE关键字的后面

　　使用hints的语法：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */
or
{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

　　注解：
　　1) DELETE、INSERT、SELECT和UPDATE是标识一个语句块开始的关键字，包含提示的注释只能出现在这些关键字的后面，否则提示无效。
　　2) “+”号表示该注释是一个hints，该加号必须立即跟在”/*”的后面，中间不能有空格。
　　3) hint是下面介绍的具体提示之一，如果包含多个提示，则每个提示之间需要用一个或多个空格隔开。
　　4) text 是其它说明hint的注释性文本

　　如果你没有正确的指定hints，Oracle将忽略该hints，并且不会给出任何错误。

　　使用全套的hints：

　　当使用hints时，在某些情况下，为了确保让优化器产生最优的执行计划，我们可能指定全套的hints。例如，如果有一个复杂的查询，包含多个表连接，如果你只为某个表指定了INDEX提示(指示存取路径在该表上使用索引)，优化器需要来决定其它应该使用的访问路径和相应的连接方法。因此，即使你给出了一个INDEX提示，优化器可能觉得没有必要使用该提示。这是由于我们让优化器选择了其它连接方法和存取路径，而基于这些连接方法和存取路径，优化器认为用户给出的INDEX提示无用。为了防止这种情况，我们要使用全套的hints，如：不但指定要使用的索引，而且也指定连接的方法与连接的顺序等。

下面是一个使用全套hints的例子，ORDERED提示指出了连接的顺序，而且为不同的表指定了连接方法：
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 ...

　　指示优化器的方法与目标的hints：

ALL_ROWS -- 基于代价的优化器，以吞吐量为目标
FIRST_ROWS(n) -- 基于代价的优化器，以响应时间为目标
CHOOSE -- 根据是否有统计信息，选择不同的优化器
RULE -- 使用基于规则的优化器

例子：
SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary, job_id
FROM employees
WHERE department_id = 20;

SELECT /*+ CHOOSE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

SELECT /*+ RULE */ employee_id, last_name, salary, job_id
FROM employees
WHERE employee_id = 7566;

　　指示存储路径的hints：
FULL /*+ FULL ( table ) */
指定该表使用全表扫描
ROWID /*+ ROWID ( table ) */
指定对该表使用rowid存取方法，该提示用的较少
INDEX /*+ INDEX ( table [index]) */
使用该表上指定的索引对表进行索引扫描
INDEX_FFS /*+ INDEX_FFS ( table [index]) */
使用快速全表扫描
NO_INDEX /*+ NO_INDEX ( table [index]) */
不使用该表上指定的索引进行存取，仍然可以使用其它的索引进行索引扫描

SELECT /*+ FULL(e) */ employee_id, last_name
FROM employees e
WHERE last_name LIKE :b1;

SELECT /*+ROWID(employees)*/ *
FROM employees
WHERE rowid > 'AAAAtkAABAAAFNTAAA' AND employee_id = 155;

SELECT /*+ INDEX(A sex_index) use sex_index because there are few
male patients */ A.name, A.height, A.weight
FROM patients A
WHERE A.sex = ’m’;

SELECT /*+NO_INDEX(employees emp_empid)*/ employee_id
FROM employees
WHERE employee_id > 200;

　　指示连接顺序的hints:
ORDERED /*+ ORDERED */
按from 字句中表的顺序从左到右的连接
STAR /*+ STAR */
指示优化器使用星型查询

SELECT /*+ORDERED */ o.order_id, c.customer_id, l.unit_price * l.quantity
FROM customers c, order_items l, orders o
WHERE c.cust_last_name = :b1
AND o.customer_id = c.customer_id
AND o.order_id = l.order_id;

/*+ ORDERED USE_NL(FACTS) INDEX(facts fact_concat) */

指示连接类型的hints：
USE_NL /*+ USE_NL ( table [,table, ...] ) */
使用嵌套连接
USE_MERGE /*+ USE_MERGE ( table [,table, ...]) */
使用排序- -合并连接
USE_HASH /*+ USE_HASH ( table [,table, ...]) */
使用HASH连接
注意：如果表有alias(别名)，则上面的table指的是表的别名，而不是真实的表名

　　具体的测试实例：
create table A(col1 number(4,0),col2 number(4,0), col4 char(30));
create table B(col1 number(4,0),col3 number(4,0), name_b char(30));
create table C(col2 number(4,0),col3 number(4,0), name_c char(30));

select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 MERGE JOIN
4 3 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'B'
6 3 SORT (JOIN)
7 6 TABLE ACCESS (FULL) OF 'A'
8 1 SORT (JOIN)
9 8 TABLE ACCESS (FULL) OF 'C'

select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=5 Card=1 Bytes=110)
2 1 HASH JOIN (Cost=3 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

创建索引：
create index inx_col12A on a(col1,col2);
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'B'
5 3 TABLE ACCESS (BY INDEX ROWID) OF 'A'
6 5 INDEX (RANGE SCAN) OF 'INX_COL12A' (NON-UNIQUE)
7 1 SORT (JOIN)
8 7 TABLE ACCESS (FULL) OF 'C'

select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=5 Card=1 Bytes=110)
2 1 HASH JOIN (Cost=3 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4 Card=1 Bytes=110)
1 0 HASH JOIN (Cost=4 Card=1 Bytes=110)
2 1 NESTED LOOPS (Cost=2 Card=1 Bytes=84)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)

select /*+ USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;

　　我们这个查询的意思是让A、C表做NL连接，并且让A表作为内表，但是从执行计划来看，没有达到我们的目的。
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=3 Card=1 Bytes=110)
1 0 NESTED LOOPS (Cost=3 Card=1 Bytes=110)
2 1 MERGE JOIN (CARTESIAN) (Cost=2 Card=1 Bytes=52)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=1 Bytes=26)
4 2 SORT (JOIN) (Cost=1 Card=1 Bytes=26)
5 4 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=1 Bytes=26)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=82 Bytes=4756)

对对象进行分析后：
analyze table a compute statistics;
analyze table b compute statistics;
analyze table c compute statistics;
analyze index inx_col12A compute statistics;
select A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=8 Bytes=336)
1 0 HASH JOIN (Cost=5 Card=8 Bytes=336)
2 1 MERGE JOIN (CARTESIAN) (Cost=3 Card=8 Bytes=64)
3 2 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
4 2 SORT (JOIN) (Cost=2 Card=4 Bytes=16)
5 4 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
6 1 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)

select /*+ ORDERED */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=5 Card=9 Bytes=378)
2 1 HASH JOIN (Cost=3 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

select /*+ ORDERED USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=7 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

select /*+ USE_NL (A C)*/ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=7 Card=9 Bytes=378)
1 0 HASH JOIN (Cost=7 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

select /*+ ORDERED USE_NL (A B C) */ A.col4
from C , A , B
where C.col3 = 5 and A.col1 = B.col1 and A.col2 = C.col2
and B.col3 = 10;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=35 Card=9 Bytes=378)
1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)
2 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
3 2 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
4 2 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)
5 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)

　　对于这个查询我无论如何也没有得到类似下面这样的执行计划：
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=35 Card=9 Bytes=378)
1 0 NESTED LOOPS (Cost=35 Card=9 Bytes=378)
2 1 TABLE ACCESS (FULL) OF 'B' (Cost=1 Card=2 Bytes=8)
3 1 NESTED LOOPS (Cost=5 Card=30 Bytes=1140)
4 3 TABLE ACCESS (FULL) OF 'C' (Cost=1 Card=4 Bytes=16)
5 3 TABLE ACCESS (FULL) OF 'A' (Cost=1 Card=30 Bytes=1020)

从上面的这些例子我们可以看出：通过给语句添加HINTS，让其按照我们的意愿执行，有时是一件很困难的事情，需要不断的尝试各种不同的hints。对于USE_NL与USE_HASH提示，建议同ORDERED提示一起使用，否则不容易指定那个表为驱动表。

/*************************************************/

sql tunning hint使用方法30例

SQL语句优化方法30例

在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

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 ;

Hint Syntax Description
Optimization Approaches and Goals
/*+ ALL_ROWS */ Explicitly chooses the cost-based approach to optimize a statement block with a goal of best throughput (that is, minimum total resource consumption).
/*+ CHOOSE */ Causes the optimizer to choose between the rule-based approach and the cost-based approach for a SQL statement based on the presence of statistics for the tables accessed by the statement.
/*+ FIRST_ROWS */ Explicitly chooses the cost-based approach to optimize a statement block with a goal of best response time (minimum resource usage to return first row).
/*+ RULE */ Explicitly chooses rule-based optimization for a statement block.
Access Methods
/*+ AND_EQUAL(table index) */ Explicitly chooses an execution plan that uses an access path that merges the scans on several single-column indexes.
/*+ CLUSTER(table) */ Explicitly chooses a cluster scan to access the specified table.
/*+ FULL(table) */ Explicitly chooses a full table scan for the specified table.
/*+ HASH(table) */ Explicitly chooses a hash scan to access the specified table.
/*+ HASH_AJ(table) */ Transforms a NOT IN subquery into a hash anti-join to access the specified table.
/*+ HASH_SJ(table) */ Transforms a NOT IN subquery into a hash semi-join to access the specified table.
/*+ INDEX(table index) */ Explicitly chooses an index scan for the specified table.
/*+ INDEX_ASC(table index) */ Explicitly chooses an ascending-range index scan for the specified table.
/*+ INDEX_COMBINE(table index) */ If no indexes are given as arguments for the INDEX_COMBINE hint, the optimizer uses whatever Boolean combination of bitmap indexes has the best cost estimate. If particular indexes are given as arguments, the optimizer tries to use some Boolean combination of those particular bitmap indexes.
/*+ INDEX_DESC(table index) */ Explicitly chooses a descending-range index scan for the specified table.
/*+ INDEX_FFS(table index) */ Causes a fast full index scan to be performed rather than a full table scan.
/*+ MERGE_AJ(table) */ Transforms a NOT IN subquery into a merge anti-join to access the specified table.
/*+ MERGE_SJ(table) */ Transforms a correlated EXISTS subquery into a merge semi-join to access the specified table.
/*+ NO_EXPAND */ Prevents the optimizer from considering OR expansion for queries having OR or IN conditions in the WHERE clause.
/*+ NO_INDEX(table index) */ Instructs the optimizer not to consider a scan on the specified index or indexes. If no indexes are specified, the optimizer does not consider a scan on any index defined on the table.
/*+ NOREWRITE */ Disables query rewrite for the query block, overriding a TRUE setting of the QUERY_REWRITE_ENABLED parameter.
/*+ ORDERED_PREDICATES */ Forces the optimizer to preserve the order of predicate evaluation (except predicates used in index keys), as specified in the WHERE clause of SELECT statements.
/*+ REWRITE (view [,...]) */ Enforces query rewrite. If you specify a view list and the list contains an eligible materialized view, Oracle will use that view regardless of the cost. No views outside of the list are considered. If you do not specify a view list, Oracle will search for an eligible materialized view and always use it regardless of the cost.
/*+ ROWID(table) */ Explicitly chooses a table scan by rowid for the specified table.
/*+ USE_CONCAT */ Forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator.
Join Orders
/*+ ORDERED */ Causes Oracle to join tables in the order in which they appear in the FROM clause.
/*+ STAR */ Forces the large table to be joined last using a nested-loops join on the index.
Join Operations
/*+ DRIVING_SITE(table) */ Forces query execution to be done at a different site from that selected by Oracle.
/*+ USE_HASH(table) */ Causes Oracle to join each specified table with another row source with a hash join.
/*+ USE_MERGE(table) */ Causes Oracle to join each specified table with another row source with a sort-merge join.
/*+ USE_NL(table) */ Causes Oracle to join each specified table to another row source with a nested-loops join using the specified table as the inner table.
Parallel Execution
Note: Oracle ignores parallel hints on a temporary table. For more information on temporary tables, see "CREATE TABLE" and Oracle8i Concepts.

/*+ APPEND */
/*+ NOAPPEND */ Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.
/*+ NOPARALLEL(table) */ Disables parallel scanning of a table, even if the table was created with a PARALLEL clause.
Restriction: You cannot parallelize a query involving a nested table.
/*+ PARALLEL(table)
/*+ PARALLEL(table, integer) */ Lets you specify parallel execution of DML and queries on the table; integer specifies the desired degree of parallelism, which is the number of parallel threads that can be used for the operation. Each parallel thread may use one or two parallel execution servers. If you do not specify integer, Oracle computes a value using the PARALLEL_THREADS_PER_CPU parameter. If no parallel hint is specified, Oracle uses the existing degree of parallelism for the table.
DELETE, INSERT, and UPDATE operations are considered for parallelization only if the session is in a PARALLEL DML enabled mode. (Use ALTER SESSION ENABLE PARALLEL DML to enter this mode.)
/*+ PARALLEL_INDEX Allows you to parallelize fast full index scans for partitioned and nonpartitioned indexes that have the PARALLEL attribute.
/*+ PQ_DISTRIBUTE
(table, outer_distribution, inner_distribution) */ Specifies how rows of joined tables should be distributed between producer and consumer query servers. The four possible distribution methods are NONE, HASH, BROADCAST, and PARTITION. However, only a subset of the combinations of outer and inner distributions are valid. For the permitted combinations of distributions for the outer and inner join tables, see Oracle8i Tuning.

/*+ NOPARALLEL_INDEX */ Overrides a PARALLEL attribute setting on an index.
Other Hints
/*+ CACHE */ Specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed.
/*+ NOCACHE */ Specifies that the blocks retrieved for this table are placed at the least recently used end of the LRU list in the buffer cache when a full table scan is performed.
/*+ MERGE(table) */ Causes Oracle to evaluate complex views or subqueries before the surrounding query.
/*+ NO_MERGE(table) */ Causes Oracle not to merge mergeable views.
/*+ PUSH_JOIN_PRED(table) */ Causes the optimizer to evaluate, on a cost basis, whether to push individual join predicates into the view.
/*+ NO_PUSH_JOIN_PRED(table) */ Prevents pushing of a join predicate into the view.
/*+ PUSH_SUBQ */ Causes nonmerged subqueries to be evaluated at the earliest possible place in the execution plan.
/*+ STAR_TRANSFORMATION */ Makes the optimizer use the best plan in which the transformation has been used.

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

转载于:http://blog.itpub.net/14864453/viewspace-421544/