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
5 consistent gets
9 physical reads
0 redosize
2188 bytes sent via SQL*Net toclient
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (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
----------------------------------------------------------
1 recursive calls
0 dbblock gets
3 consistent gets
0 physical reads
0 redosize
613 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (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
----------------------------------------------------------
1 recursive calls
0 dbblock gets
5 consistent gets
0 physical reads
0 redosize
710 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (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
----------------------------------------------------------
1 recursive calls
0 dbblock gets
3 consistent gets
4 physical reads
0 redosize
710 bytes sent via SQL*Net to client
551 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rowsprocessed
发现没有TABLE ACCESS BY INDEX ROWID BATCHED了。
当然如果联合索引太逗,导致索引过大,虽然消减了回表动作,但是所以块变多,索引中查询可能就要遍历更多的BLOCK了。