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),
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,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
----------------------------------------------------------
1recursive calls
0 dbblock gets
4consistent gets
0physical reads
132 redo size
770 bytes sent via sql*Net to client
540 bytes received via sql*Net from client
1sql*Net roundtrips to/from client
0sorts (memory)
0sorts (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
----------------------------------------------------------
1recursive calls
0 dbblock gets
2consistent gets
0physical reads
0 redosize
910 bytes sent via sql*Net to client
551 bytes received via sql*Net from client
2sql*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
1 rowsprocessed
普通表有：TABLE ACCESS BY INDEX ROWID ，索引组织表没有。
索引组织表的最大特性，就是表就是索引，索引就是表，是一种很特别的设计，无须访问表。
不过表的更新要比普通表开销更大。因为表要和索引一样有序地排列，更新负担将会非常严重。这种设计使用再很少更新、频繁读的应用场合。
原文链接：https://www.f2er.com/oracle/207078.html