Oracle的语句中的提示(3)

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2006-07-07 14:50:22

分享到 

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        效果及其明显！