索引优化系列二 回表

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

drop table t purge;

create table t as select * from dba_objects;

create index idx1_object_id on t(object_id);

--试验1

set autotrace traceonly

set linesize 1000

set timing on

select * from t where object_id<=5;

执行计划

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

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

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

统计信息

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

          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;

执行计划

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

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

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

统计信息

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

          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

--试验2：通过构造联合索引，再观察一个消除TABLE ACCESS BY INDEX ROWID的例子

set autotrace traceonly

set linesize 1000

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

执行计划

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

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

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

统计信息

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

          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

--准备工作，对t表建联合索引

create index idx_un_objid_objname on t(object_id,object_name);

--该联合索引建完后，产生功效了！消除了TABLE ACCESS BY INDEX ROWID

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

执行计划

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

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

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

统计信息

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

          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