索引组织表

转自：http://hi.baidu.com/james_yulei/blog/item/d49a33f60f1830e07609d79b.html

索引组织表
· 前面五种索引的基表数据存储是随机的，这种表称为堆表；
· 索引组织表的数据存储在二叉树索引中，所以，如果通过主键来存取数据，索引组织表能更快地返回数据，因为不需要先取索引块再取数据块，可以直接从索引中返回数据；
· 创建索引组织表时必须指定一个主键栏位，用这个作为索引；
· 索引组织表上不能建唯一约束，也不能将索引组织表建立在簇上面；
· 索引组织表的语法要点：
ORGANIZATION INDEX 指明当前表是索引组织表；
PCTTHRESHOLD 后跟一个0到50的数，默认值为50，指明容纳一行数据可用使块空间的百分比；
INCLUDING 后跟一个字段，如果数据行的长度超过了PCTTHRESHOLD指定的可用空间，从这个字段之后将数据行分为两段，后面的部分放入溢出段中；
OVERFLOW TABLESPACE 指定溢出段所在的表空间；
MAPPING TABLE 当在索引组织表上创建位图索引时创建关联的映射表；堆表位图索引的每个位对应到表的一个ROWID，索引组织表的ROWID会随着索引的分裂而发生改变，如果和堆表一样处理，位图索引很容易就失效或者维护成本很高，映射表就是为解决这个问题而引入的，映射表存放索引组织表的ROWID和逻辑行间的对应关系，索引位图中的位对应到逻辑行；dba_indexes.pct_direct_access可以用来指示映射表GUESS的效率，这个值大于30时推荐重建位图索引；一个索引组织表只有一个映射表。
· 通过dba_tables.iot_name， dba_tables.iot_type 可以查看到索引组织表的溢出表段，映射表段；
· 索引组织表的相关段的段名都是由系统生成的，这些段名的共同特性是SYS_IOT_XXX_YYY， XXX in （TOP 索引段， OVER 溢出表段， MAP 映射表段），YYY是索引组织表的OBJECT_ID。

簇
· 簇是一个或多个表的组合，这些表的数据存储在相同的数据块中，当通过簇键查询这些表时，只需读一个数据块就能返回连接的多个表的数据；
· 有两种簇，索引簇和哈希簇；
· 符合下面的条件时适合使用簇：
表初始装载以后，很少或者没有DML发生；
针对每一条主表记录，从表中的记录数大致相同并且范围可知；
更多地是对簇中的表一起查询，较少单独查询（适于索引簇）；
查询WHERE条件中使用针对簇键的相等匹配（适于哈希簇）。
· 先创建簇，建簇时系统分配簇段，再在表创建时将表指定到簇，建表时不能指定表空间，所需空间从簇段中分配；
· 建簇时需指定簇键，不管有多个表或者多少条记录，同一键值在簇中只存储一次；
· 建簇时指定SIZE关键值，这个值定义与一个簇键关联的记录占用的空间之和；
· 索引簇的关键字是INDEX（这是默认值），哈希簇的关键字是HASHKEYS（这个子句后跟一个整数指定簇的哈希值个数，哈希值是簇键值通过簇哈希函数计算后的返回值，哈希值相同的行会一起存储）；
· 索引簇在建簇后必须再建簇键栏位的索引，这时系统再分配一个索引段（不明白为什么系统不把这两步联起来，而要这样多此一举？）；哈希簇上不能建针对簇键的索引；
· 针对索引簇表的查询和普通表相似，可能也是先索引再簇；针对哈希簇表的查询，如果WHERE条件中用到键栏位的相等匹配，用与插入时相同的哈希算法处理这个键值可以得出查询数据的位置，只需一个IO即可取得需要的数据；
· 使用簇时必须小心规划和测试，否则反而会给性能带来负面影响

四、 索引组织表：
数据按主码存储和排序，同索引结构一样，不过数据直接存储于主码后面。适用于信息
检索、空间和OLAP程序。索引组织表的适用情况：
1、 代码查找表。
2、 经常通过主码访问的表。
3、 构建自己的索引结构。
4、 加强数据的共同定位，要数据按特定顺序物理存储。
5、经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。

代码:
索引组织表创建语法：
SQL> create table t2
2 (x int primary key,
3 y char(2000) default rpad('a',2000,'d'),
4 z date
5 )
6 organization index ——表示创建的表类型是IOT
7 nocompress ——同索引的压缩选项一样，表示是否对相同索引条目值进行压缩存储
8 pctthreshold 50 ——当行的大小超过块大小的百分比时，超过列数据存储至溢出段
9 including y ——IOT中每行including指定列前边的列都存储到索引块中，其余列存储到溢出块中
10 overflow ——IOT中行太大时允许设置另一溢出段来保存溢出的数据，同行迁移相似
11 /
Table created.
2、 IOT溢出段管理：如果应用程序基本上总是使用IOT中的前N列，很少访问后面的列，那么INCLUDING将会比较适用；如果不能确定经常访问的列，那么设置PCTTHRESHOLD会更适合些。

五、 索引聚簇表：
索引聚簇表是表相关的表共享同一数据块中的相同列，并把相关数据存储中同一个数据块上。创建索引聚簇表中最重要的是对SIZE参数有很好的估量，否则聚簇将会降低空间利用，降低效率。
使用索引聚簇表的注意点：
1、如果表中数据有大量DML操作的话，那么聚簇将不适用，因为会消极地影响到DML性能。
2、聚簇中，全表扫描将受到影响。这是因为将扫描聚簇中不同表的数据，额外增加很多无用的数据。
3、如果经常TRUNCATE表和装载表的话，聚簇将不适用。聚簇中的表无法被TRUNCATE的，这是因为每个块中不只是存储一张表的数据。
SQL> truncate table emp;
truncate table emp
*
ERROR at line 1:
ORA-03292: Table to be truncated is part of a cluster
4、如果大部分是读取操作，且通过聚簇码索引或聚簇表中其他索引来读取的话，聚簇将会比较比较适用。

代码:
SQL> create cluster emp_dept_cluster
2 (deptno number(2)) ——用以标识聚簇列
3 size 1024 ——用来指出大约有多少字节的数据和每个聚簇码有关，ORACLE使用这个
4 / 来计算每个块能容纳的最大聚簇码数目，对聚簇的空间分配至关重要
Cluster created.
聚簇索引存储的是每个聚簇码的值以及包含那个码的数据块的块地址。

SQL> create index idx_emp_dept_cluster
2 on cluster emp_dept_cluster;
Index created.
创建聚簇表：

SQL> create table dept
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 )
6 cluster emp_dept_cluster(deptno)
7 /
Table created.
SQL> create table emp
2 (empno number(4) primary key,
3 ename varchar2(10),
4 job varchar2(9),
5 mgr number(4),
6 hiredate date,
7 sal number(7,2),
8 comm number(7,2),
9 deptno number(2)
10 )
11 cluster emp_dept_cluster(deptno)
12 /
Table created.
SQL> alter table emp add constraint fk_emp_dept_deptno
2 foreign key(deptno) references dept(deptno);
Table altered.
装载数据：
SQL> begin
2 for x in (select * from scott.dept)
3 loop
4 insert into dept
5 values(x.deptno,x.dname,x.loc);
6
7 insert into emp
8 select * from scott.emp
9 where deptno=x.deptno;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
索引聚簇加载数据应该是同时装载同一聚簇码的所有数据，而不
是一次装载聚簇中不同表的数据。这是因为如果一次装载单张表
数据的话，很有可能单个码值的数据大于SIZE指定的数据，但是
由于聚簇码已经分配完成，此时将会聚簇码块有许多链接块，影
响性能。通过一次装载每个码值对应的数据，可以更好地利用聚
簇块的空间。
下面测试一下对聚簇中单张表批量载入和单个聚簇码值相应数据
载入的情况。
单张表批量载入：
SQL> drop table emp;
Table dropped.
SQL> create table emp
2 (empno number(4) primary key,
3 ename varchar2(20),
4 job varchar2(10),
5 mgr number(4),
6 hiredate date,
7 sal number(7,2),
8 comm number(7,2),
9 deptno number(2),
10 data char(1000) default rpad('*',1000,'a')
11 )
12 cluster emp_dept_cluster(deptno)
13 /
Table created.
SQL> drop table dept;
Table dropped.
SQL> create table dept
2 (deptno number(2),
3 dname varchar2(14),
4 loc varchar2(13)
5 )
6 cluster emp_dept_cluster(deptno)
7 /
Table created.
SQL> insert into dept select * from scott.dept;
4 rows created.
SQL> insert into emp select emp.*,'*' from scott.emp;
14 rows created.

SQL> select dept.deptno,dbms_rowid.rowid_block_number(dept.rowid) deptrid,
2 dbms_rowid.rowid_block_number(emp.rowid) emprid
3 from emp,dept
4 where emp.deptno=dept.deptno
5 /
DEPTNO DEPTRID EMPRID
---------- ---------- ----------
20 114 114
20 114 114
20 114 116
20 114 115
20 114 115
30 114 114
30 114 114
30 114 114

30 114 114
30 114 115
30 114 115
10 114 116
10 114 115
10 114 115
14 rows selected.
可以看到大多数聚簇码对应的值不在同一块上，这样造成很多链
接块，影响查询效率。
单个码值数据装载：
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL> create table emp
2 (empno number(4) primary key,
3 ename varchar2(20),
4 job varchar2(10),
5 mgr number(4),
6 hiredate date,
7 sal number(7,2),
8 comm number(7,2),
9 deptno number(2),
10 data char(1000) default rpad('*',1000,'a')
11 )
12 cluster emp_dept_cluster(deptno)
13 /
Table created.
SQL> create table dept
2 (deptno number(2),
3 dname varchar2(14),
4 loc varchar2(13)
5 )
6 cluster emp_dept_cluster(deptno)
7 /
Table created.
SQL> begin
2 for x in (select * from scott.dept)
3 loop
4 insert into dept
5 values(x.deptno,x.dname,x.loc);
6
7 insert into emp
8 select emp.*,'*'
9 from scott.emp
10 where emp.deptno=x.deptno;
11 end loop;
12 end;
13 /
PL/SQL procedure successfully completed.
SQL> select dept.deptno,dbms_rowid.rowid_block_number(dept.rowid) deptrid,
2 dbms_rowid.rowid_block_number(emp.rowid) emprid
3 from emp,dept
4 where emp.deptno=dept.deptno
5 /

DEPTNO DEPTRID EMPRID
---------- ---------- ---------
30 115 115
30 115 115
30 115 115
30 115 115
30 115 115
30 115 115
20 116 116
20 116 116
20 116 116
20 116 114
20 116 114
10 116 116
10 116 116
10 116 116
14 rows selected.
可以看出基本上每个码值相应的数据都在同一个数据块上。
SQL> select rowid from emp
2 intersect
3 select rowid from dept
4 /

ROWID

------------------

AAAEtSAADAAAAByAAA

AAAEtSAADAAAAB0AAA

AAAEtSAADAAAAB0AAC

ROWID在数据库中不是唯一用于标识一张表中一行的，可能

会有两张表用到同样的ROWID，ROWID只是在单个段中是唯一的。