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

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