这所提到的提示，都是Oracle9i的提示。一个语句中，可以含有一个或多个提示，当含有多个提示时，某些或者全部提示会失效。
提示中表名可以是别名。
为了更好的理解这些提示，先建立以下一些表。为了更加容易理解，会对一些常用提示给出例子，并给出对比，加深对提示的理解。
create table t_huang (f1_num number(10) not null,
f2_char varchar2(20) not null,
f3_numnull number(10) null,
f4_num number(10),
f5_char varchar2(20));
alter table t_huang
add constraint PK_T_HUANG primary key (f1_num)
using index;
create index ix_huang_f2_char on t_huang (
f2_char ASC
);
create index ix_huang_f23_char on t_huang (
f2_char, f3_numnull ASC
);
create index ix_huang_f4_num on t_huang (
f4_num DESC
);
begin
for i in 1..1000000 loop
insert into t_huang values(
i, to_char(dbms_random.random()),
dbms_random.random(), dbms_random.random(),
to_char(dbms_random.random()));
end loop;
commit;
end;
/
create table t_wei (f1_num2 number(10) not null,
f2_char2 varchar2(20) not null,
f3_numnull2 number(10) null,
f4_num2 number(10),
f5_char2 varchar2(20));
alter table t_wei
add constraint PK_T_WEI primary key (f1_num2)
using index;
create index ix_wei_f234_char on t_wei (
f2_char2, f3_numnull2, f4_num2 ASC
);
begin
for i in 1..10000 loop
insert into t_wei values(
i, to_char(dbms_random.random()),
dbms_random.random(), dbms_random.random(),
to_char(dbms_random.random()));
end loop;
commit;
end;
/
create table t_fuyuncat (f1_num3 number(10) not null,
f2_char3 varchar2(20) not null,
f3_numnull3 number(10) null,
f4_num3 number(10),
f5_char3 varchar2(20));
alter table t_fuyuncat
add constraint PK_T_FUYUNCAT primary key (f1_num3)
using index;
create index ix_fuyuncat_f23_char on t_fuyuncat (
f2_char3, f3_numnull3 ASC
);
begin
for i in 1..100000 loop
insert into t_fuyuncat values(
i, to_char(dbms_random.random()),
dbms_random.random(), dbms_random.random(),
to_char(dbms_random.random()));
end loop;
commit;
end;
/
并且做好以下准备工作：
$sqlplus “/as sysdba”
SQL>@/opt/oracle/product/9.2/sqlplus/admin/plustrce
SQL>grant plustrace to hw;
SQL>conn hw/hw
SQL>@/opt/oracle/product/9.2/rdbms/admin/utlxplan
SQL>set timing on
SQL>set autot trace
常用提示
CHOOSE
作用：强制使用CHOOSE为优化器规则；
例子：
SQL>select /*+choose*/ f1_num from t_huang;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T_HUANG'
RULE
作用：强制使用RULE为优化器规则；
例子：
SQL>select /*+rule*/ f1_num from t_huang;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: RULE
1 0 TABLE ACCESS (FULL) OF 'T_HUANG'
FIRST_ROWS[(n)]
作用：强制使用FIRST_ROW为优化器规则，以最快速度检索第一行，以优化查询。(n)没有则默认为1。这个提示在系统用户通过Oracle Form查询单条或少量数据时特别有用。
注意：在使用update、delete、group by、intersect、minus、union时，first_row提示无效。
例子：
SQL>select /*+first_rows(10000)*/ * from t_huang where f1_num < 10000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: FIRST_ROWS (Cost=1 Card=4 B
ytes=252)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Card=4
Bytes=252)
2 1 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card
=1)
ALL_ROWS
作用：强制使用ALL_ROW为优化器规则，以最快速度检索所有行，以优化查询。当处理大批量数据时，这个提示特别有用。
例子：
SQL>select /*+all_rows*/ * from t_huang where f1_num < 100000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=HINT: ALL_ROWS (Cost=1 Card=4 Byt
es=252)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Card=4
Bytes=252)
2 1 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card
=1)
FULL
作用：对所指定的表进行全表扫描。在查询表的大部分数据时使用该索引，将不会扫描索引，而直接进行全表扫描。
例子：
SQL>select /*+full(t_wei)*/ * from t_wei where f1_num2 < 9000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=252)
1 0 TABLE ACCESS (FULL) OF 'T_WEI' (Cost=2 Card=4 Bytes=252)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
663 consistent gets
0 physical reads
0 redo size
541708 bytes sent via SQL*Net to client
7982 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
对比：
SQL>select * from t_wei where f1_num2 < 9000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
2 1 INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1270 consistent gets
0 physical reads
0 redo size
541708 bytes sent via SQL*Net to client
7982 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
返回的数据为表的大部分数据，在没有使用强制全表扫描的情况下会先扫描索引，比使用全表扫描的方式多出了近一半的consistent gets
INDEX[(table index1, index2…)]
作用：强制使用一个或多个索引。在某些情况下（特别是在使用基于成本的优化规则下），Oracle优化器不能正确选择所有，可以通过使用这个提示强制指定使用某一个或多个索引。
例子：
SQL>select /*+index(t_huang PK_T_HUANG)*/ * from t_huang where f1_num < 100 and f2_char > ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=66 Bytes=415
8)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card=6
6 Bytes=4158)
2 1 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card
=4764)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
2009 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
对比：
SQL>select * from t_huang where f1_num < 100 and f2_char > ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
2 1 INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
251405 consistent gets
0 physical reads
0 redo size
2009 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
在强制使用了正确索引后，效果非常明显。
NO_INDEX(table index1, index2 …)
作用：强制使某一个或多个索引失效。
例子：
SQL>select /*+no_index(t_wei PK_T_WEI)*/ * from t_wei where f1_num2 < 9000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=2 Card=4 Bytes=252)
1 0 TABLE ACCESS (FULL) OF 'T_WEI' (Cost=2 Card=4 Bytes=252)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
663 consistent gets
1 physical reads
0 redo size
487612 bytes sent via SQL*Net to client
7245 bytes received via SQL*Net from client
601 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8999 rows processed
对比：
SQL>select * from t_wei where f1_num2 < 9000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
2 1 INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1270 consistent gets
0 physical reads
0 redo size
487612 bytes sent via SQL*Net to client
7245 bytes received via SQL*Net from client
601 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8999 rows processed
INDEX_JOIN(table index1, index2)
作用：将同一个表的不同索引合并，这样就只需要访问这些索引就行了。
例子：
SQL> analyze table t_huang compute statistics;
SQL>select /*+index_join(t_huang PK_T_HUANG, ix_huang_f23_char)*/ f1_num, f2_char, f3_numnull from t_huang where f1_num < 100 and f2_char > ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=4392 Card=33 Bytes=6
93)
1 0 VIEW OF 'index$_join$_001' (Cost=4392 Card=33 Bytes=693)
2 1 HASH JOIN
3 2 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=4290
3 Card=33 Bytes=693)
4 2 INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
(Cost=42903 Card=33 Bytes=693)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2590 consistent gets
0 physical reads
0 redo size
1514 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
27 rows processed
对比：
SQL>select f1_num, f2_char, f3_numnull from t_huang where f1_num < 100 and f2_char > ‘10000’ and f3_numnull >1000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
2 1 INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
251405 consistent gets
0 physical reads
0 redo size
1449 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
24 rows processed
注意：index_join提示只有在基于成本的优化器规则下才有意义。
请对比在基于规则和基于成本优化器下的physical reads
SQL> analyze table t_huang delete statistics;
SQL>select /*+index_join(t_huang PK_T_HUANG, ix_huang_f23_char)*/ f1_num, f2_char, f3_numnull from t_huang where f1_num < 100 and f2_char > ‘10000’ and f3_numnull >1000;
Statistics
----------------------------------------------------------
62 recursive calls
0 db block gets
2595 consistent gets
1890 physical reads
0 redo size
1514 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
27 rows processed
AND_EQUAL(table index1, index2)
作用：指定多个索引，让优化器使用所指定的索引。它与INDEX_JOIN的区别在于：AND_EQUAL将指定索引合并后再访问表，而INDEX_JOIN提示则只访问索引。
注意：对于位图索引，应该使用INDEX_COMBINE。
SQL> analyze table t_huang compute statistics;
SQL>select /*+and_equal(t_huang ix_huang_f23_char, ix_huang_f4_num)*/ f4_num, f2_char, f3_numnull from t_huang where f2_char > ‘1’ and f3_numnull >1 and f4_num > 100000000000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=66 Card=66 Bytes=250
8)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=66 Card=6
6 Bytes=2508)
2 1 INDEX (RANGE SCAN) OF 'IX_HUANG_F4_NUM' (NON-UNIQUE) (Cost=26 Card=26464)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
403 bytes sent via SQL*Net to client
460 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
对比：
SQL>select f4_num, f2_char, f3_numnull from t_huang where f2_char > ‘1’ and f3_numnull >1 and f4_num > 100000000000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
2 1 INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
252349 consistent gets
0 physical reads
0 redo size
403 bytes sent via SQL*Net to client
460 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
注意：如果WHERE自己中访问了主键，则该提示将不能正常运行
SQL>select /*+and_equal(t_huang ix_huang_f23_char, ix_huang_f4_num)*/ f4_num, f2_char, f3_numnull from t_huang where f1_num < 1000 and f3_numnull >1 and f4_num > 100000000000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=66 Bytes=336
6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card=6
6 Bytes=3366)
2 1 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Card
=4764)
INDEX_COMPILE
作用：与INDEX相同，但是只用于位图索引
例子：略
INDEX_ASC
作用：与INDEX类似，只不过对索引按升序扫描。
例子：略
INDEX_DESC
作用：与INDEX类似，只不过对索引按降序扫描。
例子：略
INDEX_FFS
作用：执行一次索引的快速全局扫描。这个提示只访问索引，而不访问表。只有当要查询的内容都属于索引时，这个提示才有意义。
例子：
SQL>select /*+index_ffs(t_wei pk_t_wei)*/ f1_num2 from t_wei;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=5 Card=5310 Bytes=69
030)
1 0 INDEX (FAST FULL SCAN) OF 'PK_T_WEI' (UNIQUE) (Cost=5 Card
=5310 Bytes=69030)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
172965 bytes sent via SQL*Net to client
7981 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
对比：
SQL>select f1_num2 from t_wei;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'T_WEI'
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
730 consistent gets
0 physical reads
0 redo size
172965 bytes sent via SQL*Net to client
7981 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed
ORDERED
作用：按照From子句中的表的顺序来访问表。Oracle会将表按照它们各自要连接的顺序排列，然后将已经排序的源表合并。
提示：在Oracle 8 release 8.0.5中引入了两个参数OPTIMIZER_MAX_PERMUTATIONS 和 OPTIMIZER_SEARCH_LIMIT。
optimizer_search_limit 参数指定了在决定连接多个数据表的最好方式时，CBO需要衡量的数据表连接组合的最大数目。该参数的缺省值是5。如果连接表的数目小于 optimizer_search_limit 参数，那么Oracle会执行所有可能的连接。可能连接的组合数目是数据表数目的阶乘。
假如我们有7张表，那么有7!（5040）种组合。
optimizer_max_permutations参数定义了CBO所考虑的连接排列的最大数目的上限。当我们给这个参数设置很小的一个值的时候，Oracle的计算比较很快就可以被遏制。然后执行计划，给出结果。
optimizer_search_limit参数和optimizer_max_permutations参数和Ordered参数不相容，如果定义了ordered提示，那么optimizer_max_permutations参数将会失效。
实际上，当你定义了ordered提示时，oracle已经无需计算了。
注意：如果WHERE子句后面的条件中含有有索引的列，则该提示将不能正常运行
注意：使用ORDERED提示需要临时的内存块，因此SORT_AREA_SIZE必须足够大。
技巧：在基于成本的优化器规则下，效果更好。
例子：
SQL>select /*+ordered*/ a.f1_num2, b.f2_char from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f5_char2 > ‘99’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=278 Card=26464 Bytes
=1323200)
1 0 NESTED LOOPS (Cost=278 Card=26464 Bytes=1323200)
2 1 TABLE ACCESS (FULL) OF 'T_WEI' (Cost=12 Card=266 Bytes=6
650)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Card=
100 Bytes=2500)
4 3 INDEX (UNIQUE SCAN) OF 'PK_T_HUANG' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
120 consistent gets
0 physical reads
0 redo size
1070 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
对比：
SQL>select a.f1_num2, b.f2_char from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f5_char2 > ‘99’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (FULL) OF 'T_HUANG'
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
4 3 INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1016495 consistent gets
0 physical reads
0 redo size
1070 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
使用了提示，则from后面的第一个表t_wei是驱动表，没有使用提示，则按照PL/SQL的编译顺序，以后一张表t_huang作为驱动表。
LEADING(table)
作用：当查询复杂程度增加时，ORDERED按照FROM面的顺序指定访问顺序，即排在第一位作为驱动表。LEADING可以从中间指定某张表作为第一个访问的表。
例子：
SQL>select /*+leading(a)*/ a.f1_num2, b.f2_char, c.f3_numnull3 from t_fuyuncat c, t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2=c.f1_num3 and a.f5_char2 > ‘99’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=354 Card=99329 Bytes
=7549004)
1 0 HASH JOIN (Cost=354 Card=99329 Bytes=7549004)
2 1 NESTED LOOPS (Cost=278 Card=26464 Bytes=1323200)
3 2 TABLE ACCESS (FULL) OF 'T_WEI' (Cost=12 Card=266 Bytes
=6650)
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=1 Car
d=100 Bytes=2500)
5 4 INDEX (UNIQUE SCAN) OF 'PK_T_HUANG' (UNIQUE)
6 1 TABLE ACCESS (FULL) OF 'T_FUYUNCAT' (Cost=39 Card=19930
Bytes=518180)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
809 consistent gets
0 physical reads
0 redo size
1256 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
对比：
SQL>select a.f1_num2, b.f2_char, c.f3_numnull3 from t_fuyuncat c, t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2=c.f1_num3 and a.f5_char2 > ‘99’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'T_HUANG'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
5 4 INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT'
7 6 INDEX (UNIQUE SCAN) OF 'PK_T_FUYUNCAT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1016530 consistent gets
0 physical reads
68 redo size
1256 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
ORDERED_PREDICATES
作用：指示优化器按照WHERE子句的顺序来评估查询。
注意：如果WHERE子句后面的条件中含有有索引的列，则会先评估索引。
例子：略
ROWID(table)
作用：使Oracle通过rowid来访问确切的物理位置。
例子：
SQL>select /*+rowid(t_fuyuncat)*/* from t_fuyuncat where rowid not in (select a.rowid from t_fuyuncat a, t_wei b where a.f1_num3=b.f1_num2);
Elapsed: 00:00:01.41
Statistics
----------------------------------------------------------
22 recursive calls
0 db block gets
10717 consistent gets
0 physical reads
0 redo size
4959939 bytes sent via SQL*Net to client
66644 bytes received via SQL*Net from client
6001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90000 rows processed
对比：
SQL>select * from t_fuyuncat where rowid not in (select a.rowid from t_fuyuncat a, t_wei b where a.f1_num3=b.f1_num2);
Elapsed: 01:22:44.38
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2179704387 consistent gets
0 physical reads
0 redo size
773976 bytes sent via SQL*Net to client
10940 bytes received via SQL*Net from client
937 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14025 rows processed
效果及其明显！
NO_EXPAND
作用：禁止优化器使用OR扩展。如果不使用NO_EXPAND，优化器会产生很长的执行计划。
例子：
SQL>select /*+no_expand*/* from t_fuyuncat where f1_num3 < 100 or f2_char3 < ‘100’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=39 Card=1943 Bytes=1
22409)
1 0 TABLE ACCESS (FULL) OF 'T_FUYUNCAT' (Cost=39 Card=1943 Byt
es=122409)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4009 consistent gets
0 physical reads
0 redo size
2773060 bytes sent via SQL*Net to client
37285 bytes received via SQL*Net from client
3332 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49953 rows processed
对比：
SQL>select * from t_fuyuncat where f1_num3 < 100 or f2_char3 < ‘100’;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 CONCATENATION
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT'
3 2 INDEX (RANGE SCAN) OF 'IX_FUYUNCAT_F23_CHAR' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT'
5 4 INDEX (RANGE SCAN) OF 'PK_T_FUYUNCAT' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
53427 consistent gets
0 physical reads
0 redo size
2773060 bytes sent via SQL*Net to client
37285 bytes received via SQL*Net from client
3332 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
49953 rows processed
DRIVING_SITE
作用：DRIVING_SITE作用和ORDERED类似。DRIVING_SITE通常在分布式查询中使用。如果没有这个提示，Oracle会先从远程点检索，并将它们连接到本地站点中。通过使用DRIVING_SITE，我们可以先在本地进行检索，将检索后的数据发送到远程节点进行连接。
提示：合理使用DRIVING_SITE，可以在分布式查询中大大减少网络流量。
例子：略
USE_MERGE(table1, table2…)
作用：使用Merge Join方式进行连接。先对指定的表进行排序，然后再和其他表合并在一起组成结果集。Merger Join再合并的表的所返回数据量差不多的时候比较有效。
例子：
SQL>select /*+use_merge(a, c)*/ a.f1_num2, c.f3_numnull3 from t_wei a, t_fuyuncat c where a.f1_num2=c.f1_num3 and a.f5_char2 > ‘99’ and c.f1_num3 < 10000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=26 Card=14 Bytes=714
)
1 0 MERGE JOIN (Cost=26 Card=14 Bytes=714)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT' (Cost=10 C
ard=997 Bytes=25922)
3 2 INDEX (RANGE SCAN) OF 'PK_T_FUYUNCAT' (UNIQUE) (Cost=2
Card=179)
4 1 SORT (JOIN) (Cost=16 Card=13 Bytes=325)
5 4 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI' (Cost=10 Card
=13 Bytes=325)
6 5 INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE) (Cost=2 Ca
rd=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
160 consistent gets
0 physical reads
0 redo size
1019 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
16 rows processed
对比：
SQL> select a.f1_num2, c.f3_numnull3 from t_wei a, t_fuyuncat c where a.f1_num2=c.f1_num3 and a.f5_char2 > ‘99’ and c.f1_num3 < 10000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_FUYUNCAT'
3 2 INDEX (RANGE SCAN) OF 'PK_T_FUYUNCAT' (UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
5 4 INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
20089 consistent gets
0 physical reads
0 redo size
1019 bytes sent via SQL*Net to client
666 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
16 rows processed
USE_NL(table)
作用：使用Nested Loop方式进行连接。以指定的表为驱动表进行嵌套循环查询。Nested Loop对于嵌套查询一张大表和一张小表时比较有效，指定小表为驱动表。
例子：
SQL>select /*+use_nl(a)*/ a.f1_num2, b.f3_numnull from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2 < 1000 and b.f3_numnull < 10000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=10 Card=300 Bytes=11
700)
1 0 NESTED LOOPS (Cost=10 Card=300 Bytes=11700)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card
=1323 Bytes=34398)
3 2 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Ca
rd=4764)
4 1 INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
608 consistent gets
0 physical reads
0 redo size
13072 bytes sent via SQL*Net to client
1018 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
496 rows processed
对比：
SQL>select a.f1_num2, b.f3_numnull from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2 < 1000 and b.f3_numnull < 10000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
4 3 INDEX (UNIQUE SCAN) OF 'PK_T_HUANG' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3069 consistent gets
0 physical reads
0 redo size
13072 bytes sent via SQL*Net to client
1018 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
496 rows processed
USE_HASH(table)
作用：将指定表保存在内存hash表，快速的与其他表连接在一起。
注意：要使用USE_HASH，必须保证HASH_AREA_SIZE和PGA_AGGREGATE_TARGET足够大。
例子：
SQL>select /*+use_hash(a)*/ a.f1_num2, b.f3_numnull from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2 < 1000 and b.f3_numnull < 10000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=13 Card=300 Bytes=11
700)
1 0 HASH JOIN (Cost=13 Card=300 Bytes=11700)
2 1 INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE) (Cost=2 Card=2
66 Bytes=3458)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card
=1323 Bytes=34398)
4 3 INDEX (RANGE SCAN) OF 'PK_T_HUANG' (UNIQUE) (Cost=2 Ca
rd=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
13072 bytes sent via SQL*Net to client
1018 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
496 rows processed
对比：
SQL>select a.f1_num2, b.f3_numnull from t_wei a, t_huang b where a.f1_num2=b.f1_num and a.f1_num2 < 1000 and b.f3_numnull < 10000;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 INDEX (RANGE SCAN) OF 'PK_T_WEI' (UNIQUE)
3 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
4 3 INDEX (UNIQUE SCAN) OF 'PK_T_HUANG' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3069 consistent gets
0 physical reads
0 redo size
13072 bytes sent via SQL*Net to client
1018 bytes received via SQL*Net from client
35 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
496 rows processed
PUSH_SUBQ
作用：可以尽可能早的评估子查询。当子查询返回比较少行时，这个提示比较有用。
注意：当查询使用合并连接和远程表连接时，这个提示无效。
例子：
SQL>select /*+push_subq*/ * from t_huang where f2_char < ‘1000’ and f1_num in (select f1_num2 from t_wei where f2_char2 < ‘1’);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=21 Card=1331 Bytes=1
17128)
1 0 HASH JOIN (Cost=21 Card=1331 Bytes=117128)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI' (Cost=10 Card=2
66 Bytes=6650)
3 2 INDEX (RANGE SCAN) OF 'IX_WEI_F234_CHAR' (NON-UNIQUE)
(Cost=2 Card=1)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG' (Cost=10 Card
=26464 Bytes=1667232)
5 4 INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE) (Cost=2 Card=4764)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
508038 consistent gets
33 physical reads
2044 redo size
136343 bytes sent via SQL*Net to client
2470 bytes received via SQL*Net from client
167 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2482 rows processed
对比：
SQL>select * from t_huang where f2_char < ‘1000’ and f1_num in (select f1_num2 from t_wei where f2_char2 < ‘1’);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 NESTED LOOPS
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_HUANG'
3 2 INDEX (RANGE SCAN) OF 'IX_HUANG_F23_CHAR' (NON-UNIQUE)
4 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_WEI'
5 4 INDEX (UNIQUE SCAN) OF 'PK_T_WEI' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1008640 consistent gets
0 physical reads
0 redo size
136343 bytes sent via SQL*Net to client
2470 bytes received via SQL*Net from client
167 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2482 rows processed
PARALLEL(table[, degree[, instances]])
作用：将全表扫描的查询分成多个部分，在不同进程中处理。
注意：该提示只对分区表有效。该提示对delete、update、insert同样有效
例子：略
NOPARALLEL(table)
作用：指定不使用并行查询。
注意：该提示只对分区表有效
例子：略
APPEND
作用：不检查是否有插入所需要的足够空间，直接添加到新块中。
注意：使用该提示必须保证有足够的空间
提示： 在insert中使用parallel提示默认会使用append
例子：略
NOAPPEND
作用：指定不使用append方式。当使用parallel提示时，会默认使用append，可以使用该提示使append无效
例子：略
CACHE(table)
作用：对指定进行全表扫描的表固定到内存中。对于经常要查询的小表可以使用CACHE提示。
提示： 在建表时使用cache子句，可以直接将表中数据存入内存
alter table t_wei cache;
例子：略
NOCACHE(table)
作用：对与已经指定CACHE将数据固定到内存中表，可以用NOCACHE从内存移出。
例子：略
CLUSTER
作用：强制使用聚簇扫描访问表。如果经常访问连接表，但又很少修改它，可以使用聚簇表。
例子：略
HASH(table)
作用：强制使用HASH聚簇。
注意：只有基于代价的优化器规则才能使用HASH聚簇。并且HASH_JOIN_ENABLED要为TRUE，PGAA_GGREGATE_TARGET、HASH_AREA_SIZE要足够大。
例子：略
CURSOR_SHAREING_EXACT
作用：强制使参数CURSOR_SHARING为FORCE或SIMILAR。
例子：略
RICHS_SECRET_HINT
作用：强制只访问内存中的数据。对于不使用索引的全表扫描有效。
例子：略
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zgeyzq/archive/2008/05/10/2430177.aspx
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22664653/viewspace-663256/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/22664653/viewspace-663256/
876
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
