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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

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

原文链接：https://www.f2er.com/oracle/207067.html

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

1. 索引回表情况

猜你在找的Oracle相关文章

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