索引優化系列二 回表

--索引回表讀（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