oracle--如何干预执行计划（使用hints提示）

时间：2011-03-08 来源：小良cardon

如何干预执行计划 - - 使用hints提示
指示存储路径的hints：
FULL          /*+ FULL ( table ) */
ROWID       /*+ ROWID ( table ) */
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 */
STAR    /*+ STAR */
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_MERGE    /*+ USE_MERGE ( table [,table, ...]) */
USE_HASH    /*+ USE_HASH ( table [,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
----------------------------------------------------------
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
----------------------------------------------------------
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
----------------------------------------------------------
创建索引：
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
----------------------------------------------------------
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
----------------------------------------------------------
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
----------------------------------------------------------
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
----------------------------------------------------------
对对象进行分析后：
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
----------------------------------------------------------
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
----------------------------------------------------------
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
----------------------------------------------------------
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
----------------------------------------------------------
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
----------------------------------------------------------
对于这个查询我无论如何也没有得到类似下面这样的执行计划：
Execution Plan
----------------------------------------------------------