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

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 ，索引组织表没有。

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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!