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 ，索引组织表没有。
索引组织表的最大特性，就是表就是索引，索引就是表，是一种很特别的设计，无须访问表。
不过表的更新要比普通表开销更大。因为表要和索引一样有序地排列，更新负担将会非常严重。这种设计使用再很少更新、频繁读的应用场合。
