【读书笔记】【收获，不止Oracle】索引组织表

本次试验，用于讨论索引组织表与普通表的区别：

1.创建两个表：heap_addresses（普通表），iot_addresses（索引组织表）

SYS@ orcl>drop table heap_addresses purge;Table dropped.SYS@ orcl>drop table iot_addresses purge;Table dropped.SYS@ orcl>create table heap_addresses (  2  empno number (10),  3  addr_type varchar2(10),  4  street varchar2(10),  5  city varchar2(10),  6  state varchar2(2),  7  zip number,  8  primary key(empno)  9  ) 10  /Table created.SYS@ orcl>create table iot_addresses (  2  empno number (10),  3  addr_type varchar2(10),  4  street varchar2(10),  5  city varchar2(10),  6  state varchar2(2),  7  zip number,  8  primary key(empno)  9  ) 10  organization index 11  /Table created.

2.向两个表投入相同的数据

SYS@ orcl>insert into heap_addresses   2  select object_id,'WORK','123street','washington','DC',20123  3  from all_objects;49896 rows created.SYS@ orcl>insert into iot_addresses   2  select object_id,'WORK','123street','washington','DC',20123  3  from all_objects;49896 rows created.SYS@ orcl>commit;Commit complete.

3.执行查看计划，首先查看普通表

SYS@ orcl>set linesize 1000SYS@ orcl>set autotrace traceonlySYS@ orcl>select * from heap_addresses  2  where empno=22;Execution Plan----------------------------------------------------------Plan hash value: 1273877215----------------------------------------------------------------------------------------------| 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_C006637    |     1 |       |     1   (0)| 00:00:01 |----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   2 - access("EMPNO"=22)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          5  consistent gets          0  physical reads        124  redo size        627  bytes sent via SQL*Net to client        370  bytes received via SQL*Net from client          1  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

4.接着查看索引组织表

SYS@ orcl>select * from iot_addresses  2  where empno=22;Execution Plan----------------------------------------------------------Plan hash value: 2496347326---------------------------------------------------------------------------------------| 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_54159 |     1 |    50 |     1   (0)| 00:00:01 |---------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------   1 - access("EMPNO"=22)Statistics----------------------------------------------------------          1  recursive calls          0  db block gets          4  consistent gets          0  physical reads        124  redo size        719  bytes sent via SQL*Net to client        381  bytes received via SQL*Net from client          2  SQL*Net roundtrips to/from client          0  sorts (memory)          0  sorts (disk)          1  rows processed

总结：

普通表 索引组织表
1.consistent gets（逻辑读）  5 4
由此可见，索引组织表比普通表要产生更少的逻辑读。

2.普通表产生 TABLE ACCESS BY INDEX ROWID 来获取列以外的信息
3.索引组织表特点：表 = 索引 ，索引更新会比普通表开销更大。表和索引一样有序的排列，更新负担会加重。
4.索引组织表一般用于很少更新，频繁读的应用场合。用于数据很少变动，却大量读取的场合。（如地区配置表）

以上代码和观点摘自原书,试验由本人试验，所以与原书略有不同。