Oracle的语句中的提示(2)

[English]

作者： fuyuncat

来源： www.HelloDBA.com

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

分享到 

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