--索引回表讀（TABLE ACCESS BY INDEX ROWID）的例子

創(chuàng)新互聯(lián)憑借在網(wǎng)站建設(shè)、網(wǎng)站推廣領(lǐng)域領(lǐng)先的技術(shù)能力和多年的行業(yè)經(jīng)驗(yàn)，為客戶提供超值的營銷型網(wǎng)站建設(shè)服務(wù)，我們始終認(rèn)為：好的營銷型網(wǎng)站就是好的業(yè)務(wù)員。我們已成功為企業(yè)單位、個(gè)人等客戶提供了網(wǎng)站設(shè)計(jì)、成都網(wǎng)站制作服務(wù)，以良好的商業(yè)信譽(yù)，完善的服務(wù)及深厚的技術(shù)力量處于同行領(lǐng)先地位。

drop table t purge;

create table t as select * from dba_objects;

create index idx1_object_id on t(object_id);

--試驗(yàn)1

set autotrace traceonly

set linesize 1000

set timing on

select * from t where object_id<=5;

執(zhí)行計(jì)劃

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     4 |   828 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     4 |   828 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX1_OBJECT_ID |     4 |       |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

統(tǒng)計(jì)信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          5  consistent gets

          0  physical reads

          0  redo size

       1666  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          4  rows processed

--比較消除TABLE ACCESS BY INDEX ROWID回表后的性能，將select * from改為select object_id from 

set autotrace traceonly

set linesize 1000

set timing on

select object_id from t where object_id<=5;

執(zhí)行計(jì)劃

-----------------------------------------------------------------------------------

| Id  | Operation        | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                |     4 |    52 |     2   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX1_OBJECT_ID |     4 |    52 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------

統(tǒng)計(jì)信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        478  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          4  rows processed

--試驗(yàn)2：通過構(gòu)造聯(lián)合索引，再觀察一個(gè)消除TABLE ACCESS BY INDEX ROWID的例子

set autotrace traceonly

set linesize 1000

select object_id,object_name from t where object_id<=5;

執(zhí)行計(jì)劃

----------------------------------------------------------------------------------------------

| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                |     4 |   316 |     3   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T              |     4 |   316 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX1_OBJECT_ID |     4 |       |     2   (0)| 00:00:01 |

----------------------------------------------------------------------------------------------

統(tǒng)計(jì)信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          5  consistent gets

          0  physical reads

          0  redo size

        567  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          4  rows processed

--準(zhǔn)備工作，對(duì)t表建聯(lián)合索引

create index idx_un_objid_objname on t(object_id,object_name);

--該聯(lián)合索引建完后，產(chǎn)生功效了！消除了TABLE ACCESS BY INDEX ROWID

select object_id,object_name from t where object_id<=5;

執(zhí)行計(jì)劃

-----------------------------------------------------------------------------------------

| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT |                      |    12 |   948 |     2   (0)| 00:00:01 |

|*  1 |  INDEX RANGE SCAN| IDX_UN_OBJID_OBJNAME |    12 |   948 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

統(tǒng)計(jì)信息

----------------------------------------------------------

          0  recursive calls

          0  db block gets

          3  consistent gets

          0  physical reads

          0  redo size

        567  bytes sent via SQL*Net to client

        415  bytes received via SQL*Net from client

          2  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

          4  rows processed