索引组织表与簇---来自互联网

索引组织表
· 前面五种索引的基表数据存储是随机的，这种表称为堆表；
·索引组织表的数据存储在二叉树索引中，所以，如果通过主键来存取数据，索引组织表能更快地返回数据，因为不需要先取索引块再取数据块，可以直接从索引中返回数据；
· 创建索引组织表时必须指定一个主键栏位，用这个作为索引；
· 索引组织表上不能建唯一约束，也不能将索引组织表建立在簇上面；
· 索引组织表的语法要点：
Ø 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…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。

[php]

索引组织表创建语法：
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.
[/php]

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> selectdept.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> selectdept.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等值访问数据。

[php]

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 primarykey(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

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