索引优化系列二 回表

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