HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

Oracle的语句中的提示(2)

[English]

作者: fuyuncat

来源: www.HelloDBA.com

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

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

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)

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat