20.读书笔记收获不止Oracle之 索引回表和优化

1. 索引回表情况

看下索引回表。

drop table t purge;

SQL> create table t as select * from dba_objects;

SQL> create index inx1_object_id on t (object_id);

SQL> set autotrace traceonly

SQL> set linesize 1000

SQL> set timing on

SQL> select * from t where object_id<=5;

Elapsed: 00:00:00.04

Execution Plan

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

Plan hash value: 2752956059

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

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

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

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

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

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

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

Predicate Information (identified byoperation id):

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

2- access("OBJECT_ID"<=5)

Statistics

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

1 recursivecalls

0 dbblock gets

5consistent gets

9physical reads

0 redosize

2188 bytes sent via SQL*Net toclient

551 bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0 sorts(disk)

4 rowsprocessed

索引中可以读到索引列的信息，但是不能读到该列以外的其他列信息。

改成如下：

SQL> select object_id from t where object_id<=5;

Elapsed: 00:00:00.01

Execution Plan

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

Plan hash value: 4108188601

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

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

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

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

|* 1| INDEX RANGE SCAN| INX1_OBJECT_ID | 4 |20 | 2 (0)| 00:00:01 |

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

Predicate Information (identified byoperation id):

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

1- access("OBJECT_ID"<=5)

Statistics

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

1recursive calls

0 dbblock gets

3consistent gets

0physical reads

0 redosize

613 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

4 rowsprocessed

没有了TABLE ACCESS BY INDEX ROWID的动作。因为没有了回表的动作，可以直接从索引中获取到需要的列信息。

此外如果还需要 object_name 的列，可以创建一个组合索引。

如果不做这个组合索引，执行如下：

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

Elapsed: 00:00:00.00

Execution Plan

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

Plan hash value: 2752956059

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

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

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

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

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

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

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

Predicate Information (identified byoperation id):

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

2- access("OBJECT_ID"<=5)

Statistics

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

1recursive calls

0 dbblock gets

5consistent gets

0physical reads

0 redosize

710 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

4 rowsprocessed

还是有TABLE ACCESS BY INDEX ROWID BATCHED的。

创建组合索引如下：

Create index idx_un_objid_objname on t (object_id,object_name);

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

Elapsed: 00:00:00.03

Execution Plan

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

Plan hash value: 2827629532

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

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

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

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

|* 1| INDEX RANGE SCAN| IDX_UN_OBJID_OBJNAME| 4 | 120 |2 (0)| 00:00:01 |

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

Predicate Information (identified byoperation id):

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

1- access("OBJECT_ID"<=5)

Statistics

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

1recursive calls

0 dbblock gets

3consistent gets

4physical reads

0 redosize

710 bytes sent via SQL*Net to client

551 bytes received via SQL*Net from client

2SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

4 rowsprocessed

发现没有TABLE ACCESS BY INDEX ROWID BATCHED了。

当然如果联合索引太逗，导致索引过大，虽然消减了回表动作，但是所以块变多，索引中查询可能就要遍历更多的BLOCK了。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!