【读书笔记】【收获，不止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 1000
SYS@ orcl>set autotrace traceonly
SYS@ 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.索引组织表一般用于很少更新，频繁读的应用场合。用于数据很少变动，却大量读取的场合。（如地区配置表）

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