12.读书笔记收获不止Oracle之 索引分区表

12.读书笔记收获不止Oracle之 索引分区表

普通堆表操作的不足之处：执行select * from t where id=1.

先从索引中获取rowid，然后定位到表中，获取id以外的其他列的动作，这就是回表。

           如何不进行回表？

           采用索引组织表。

来看个例子，创建堆表：

create table heap_addresses                   

    (empno number(10),

    addr_type varchar2(10),

    street varchar2(10),

    city varchar2(10),

    state varchar2(2),

    zip number,

    primary key(empno)

    );

创建索引组织表：

create table iot_addresses                   

    (empno number(10),

    addr_type varchar2(10),

    street varchar2(10),

    city varchar2(10),

    state varchar2(2),

    zip number,

    primary key(empno)

)

Organization index;

往堆表插入：

SQL> insert into heap_addresses selectobject_id,'WORK','123street','washington','DC',20123 from all_objects;

 

89752 rows created.

往索引组织表插入：

SQL> insert into iot_addresses selectobject_id,'WORK','123street','washington','DC',20123 from all_objects;

然后commit;

进行性能测试对比：

Set linesize 1000

Set autotrace traceonly

Select * from heap_addresses where empno=22;

Execution Plan

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

Plan hash value: 2900288143

 

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

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

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

|   0| SELECT STATEMENT         |                        |         1 |          50 |           1  (0)| 00:00:01 |

|   1|  TABLE ACCESS BY INDEX ROWID|HEAP_ADDRESSES |          1 |          50 |           1  (0)| 00:00:01 |

|*  2|   INDEX UNIQUE SCAN           | SYS_C0010415   |            1 |             |         1  (0)| 00:00:01 |

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

 

Predicate Information (identified byoperation id):

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

 

   2- access("EMPNO"=22)

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

             4 consistent gets

             0 physical reads

           132  redo size

           770  bytes sent via SQL*Net to client

           540  bytes received via SQL*Net from client

             1 SQL*Net roundtrips to/from client

             0 sorts (memory)

             0 sorts (disk)

             1  rowsprocessed

 

再执行索引组织表：

Select * from iot_addresseswhere empno=22;

Execution Plan

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

Plan hash value: 826554505

 

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

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

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

|   0| SELECT STATEMENT  |                         |    1 |    50 |     1    (0)|00:00:01 |

|*  1|  INDEX UNIQUE SCAN| SYS_IOT_TOP_93653|     1 |    50 |    1   (0)| 00:00:01 |

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

 

Predicate Information (identified byoperation id):

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

 

   1- access("EMPNO"=22)

 

 

Statistics

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

             1 recursive calls

             0  dbblock gets

             2 consistent gets

             0 physical reads

             0  redosize

           910  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)

             1  rowsprocessed

 

普通表有：TABLE ACCESS BY INDEX ROWID ，索引组织表没有。

索引组织表的最大特性，就是表就是索引，索引就是表，是一种很特别的设计，无须访问表。

不过表的更新要比普通表开销更大。因为表要和索引一样有序地排列，更新负担将会非常严重。这种设计使用再很少更新、频繁读的应用场合。