Index-Organized Table和Clustered Table

一、 索引组织表：
         

数据按主码存储和排序，同索引结构一样，不过数据直接存储于主码后面。适用于信息
检索、空间和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、 如果大部分是读取操作，且通过聚簇码索引或聚簇表中其他索引来读取的话，聚簇将会比较比较适用。
[php]
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只是在单个段中是唯一的。

...
[/php]

三、 散列（哈希）聚簇表：

    概念上同索引聚簇表一样，不同的是用哈希函数代替了索引聚簇
码来进行数据的分配。
事实上在散列聚簇中数据就是索引，因为数据决定行的物理位置。散列聚簇表中ORACLE根据行的码值，利用内部函数或提供的函
    数对聚簇码值进行运算，以决定数据的物理存储位置。散列聚簇
通常意味着如果通过聚码访问的话，一个IO就能够提取到所需的
数据。
   创建散列聚簇时，必须指定散列码值的数目。由于使用哈希函数
来确定数据的分布，对散列聚簇表不能使用范围扫描。全扫描散
列聚簇表时，不论表是否为空，ORACLE将全扫描所有块，这是
因为散列聚簇中数据块都是预先分配的。散列聚簇的初始数据装
载将会比较慢。自定义的散列函数限定只能使用表中可用的列和ORACLE内置函数。
散列聚簇要点：
1、 散列聚簇通过散列码查询的时候需要的IO很少。几乎一个IO就可以提取到所需的数据，除非发生了行溢出。而传统索
引至少需要2个IO才能得到数据。
2、 散列聚簇查询CPU开销大。散列聚簇是CPU密集型的，而索引是IO密集型的。
3、 对表中数据量比较有把握，如行数，每行占用空间，有合理的上限，正确设置好HASHKYES和SIZE参数，那么散列聚簇将比较适用。
4、 散列聚簇降低DML性能。
5、 总是经常通过HASHKEY等值访问数据。

SQL> create cluster hash_cluster
2 (hash_key number)
3 hashkeys 100 ——用以标识有多少个聚簇码值，不可更改，除非重新构建
4 size 8192 ——每个聚簇码对应的数据大概有多少字节
5 /
Cluster created.
散列聚簇表的大小由hashkeys关键字决定，ORACLE将用
hashkeys附近最接近的素数的值（大于该数的最小素数？）
为空间分配的块的数目。散列聚簇根据这些东西来预先分配
空间，分配的大小为（HASHKES/trunk(blocksize/SIZE)）块数。

SQL> exec show_space('hash_cluster',user,'cluster');
Free Blocks.............................0
Total Blocks............................104
Total Bytes.............................851968
Unused Blocks...........................2
Unused Bytes............................16384
Last Used Ext FileId....................3
Last Used Ext BlockId...................3097
Last Used Block.........................6

PL/SQL procedure successfully completed.

可以看到占用空间一共为102个块，一个块是块头用来保存
段的相关信息，这样一共分配了101个块，符合(100/trunc(8192/8192))最近的一个素数的要求。

下面通过一个例子说明散列聚簇表随机读取和常规表随机读取
的效率对比：

SQL> create cluster hash_cluster
2 (hash_key number)
3 hashkeys 50000
4 size 40
5 /

Cluster created.

SQL> create table emp
2 cluster hash_cluster(empno)
3 as
4 select rownum empno,ename,job,mgr,hiredate,sal,comm,deptno
5 from scott.emp
6 where 1=0
7 /

Table created.

SQL> declare
2 l_cnt number;
3 l_empno number default 1;
4 begin
5 select count(*) into l_cnt from scott.emp;
6
7 for x in ( select * from scott.emp )
8 loop
9 for i in 1 .. trunc(50000/l_cnt)+1
10 loop
11 insert into emp values
12 ( l_empno, x.ename, x.job, x.mgr, x.hiredate, x.sal,
13 x.comm, x.deptno );
14 l_empno := l_empno+1;
15 end loop;
16 end loop;
17 commit;
18 end;
19 /

PL/SQL procedure successfully completed.

SQL> create table emp_reg as select * from emp;

Table created.

SQL> alter table emp_reg add constraint pk_emp_reg primary key(empno);

Table altered.

SQL> create table random (x int);

Table created.

1 begin
2 for i in 1..100000
3 loop
4 insert into random values
5 (mod(abs(dbms_random.random),50000)+1);
6 end loop;
7* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> select count(ename)
2 from emp,random
3 where emp.empno=random.x
4 /

COUNT(ENAME)
------------
100000

SQL> select count(ename)
2 from emp_reg,random
3 where emp_reg.empno=random.x
4 /

COUNT(ENAME)
------------
100000

select count(ename)
from emp,random
where emp.empno=random.x

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.10 0 17 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.33 0.56 323 623 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.33 0.66 323 640 0 1

select count(ename)
from emp_reg,random
where emp_reg.empno=random.x

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.80 2.81 404 200158 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.80 2.81 404 200159 0 1

可以看出散列聚簇随机读取的性能是非常好的。