Oracle的语句中的提示(3)

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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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 Optimizer=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

效果及其明显！

[@more@]

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

转载于:http://blog.itpub.net/9650775/viewspace-920364/