聚簇表

2011年1月26日 16:35:06

聚簇表介绍 

参考http://www.soft6.com/tech/16/163666.html
聚簇原理：
    聚簇是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中;聚簇还表示把相关的数据存储在同一个块上。利用聚簇，
一个块可能包含多个表的数据。概念上就是如果两个或多个表经常做链接操作，那么可以把需要的数据预先存储在一起。聚簇还可以用于单个表，
可以按某个列将数据分组存储。
    更加简单的说，比如说，EMP表和DEPT表，这两个表存储在不同的segment中，甚至有可能存储在不同的TABLESPACE中，因此，他们的数据一定不会
在同一个BLOCK里。而我们有会经常对这两个表做关联查询，比如说：select * from emp,dept where emp.deptno = dept.deptno .仔细想想，查询
主要是对BLOCK的操作，查询的BLOCK越多，系统IO就消耗越大。如果我把这两个表的数据聚集在少量的BLOCK里，查询效率一定会提高不少。
　　比如我现在将值deptno=10的所有员工抽取出来，并且把对应的部门信息也存储在这个BLOCK里(如果存不下了，可以为原来的块串联另外的块)。
这就是索引聚簇表的工作原理。

聚簇的具体使用：
--创建聚簇
create cluster emp_dept_cluster ( deptno number(2) )size 1024;
--创建聚簇索引
create index emp_dept_cluster_idx on cluster emp_dept_cluster;
--创建聚簇索引表(dept)
create table dept
( deptno number(2) primary key, dname varchar2(14),loc varchar2(13)) cluster emp_dept_cluster(deptno);
--创建聚簇索引表(emp)
create table emp
( empno    number primary key,ename varchar2(10),job varchar2(9),mgr number,hiredate date,sal number,comm number,
deptno number(2) constraint emp_fk references dept(deptno)) cluster emp_dept_cluster(deptno);
--查看创建的聚簇索引表
select cluster_name, table_name from user_tables where cluster_name is not null order by 1;

--加载数据前必须先创建聚簇索引，否则会提示“ORA-02032:聚簇表无法在簇索引建立之前使用”
begin
  for x in (select * from dept) loop
    insert into dept values (x.deptno, x.dname, x.loc);
    insert into emp
      select * from emp where deptno = x.deptno;
  end loop;
end;

--取消外键
alter table emp disable constraint emp_fk;
--截断簇（在截断簇之前必须先关闭外键引用，否则提示“ORA-02266:表中的唯一/主键被启用的外键引用”）
truncate cluster emp_dept_cluster;
--激活外键
alter table emp enable constraint emp_fk;
/*增加一个很大的列char(1000),加这个列是为了让EMP行远远大于现在的大小。使得一个1024的聚簇无法存储一行记录。不能加 varchar2(1000),
因为ORACLE对varchar2存储的原则是能省就省，如果数据数据不到1000，不会分配1000的空间。 char则是分配多少用多少*/
alter table emp add data char(1000);

--第一种方式插入数据
insert into dept select * from lttfm.dept;
insert into emp select emp.*,'*'from lttfm.emp;
--有两条记录两个表的块不同
select dept_blk,
       emp_blk,
       case
         when dept_blk <> emp_blk then
          '*'
       end flag,--如果两个表的块不同则用*表示
       deptno
  from (select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
               dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
               dept.deptno
          from emp, dept
         where emp.deptno = dept.deptno)
 order by deptno;

--第二种方式插入数据
begin
  for x in (select * from lttfm.dept) loop
    insert into dept values (x.deptno, x.dname, x.loc);
    insert into emp
      select emp.*, 'x' from lttfm.emp where deptno = x.deptno;
  end loop;
end;
--再次查看(两个表的块基本相同)
select dept_blk,
       emp_blk,
       case
         when dept_blk <> emp_blk then
          '*'
       end flag,
       deptno
  from (select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
               dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
               dept.deptno
          from emp, dept
         where emp.deptno = dept.deptno)
 order by deptno;
注：插入数据之前先做截断簇

总结：
 当我们通过第一种方法时，有一个问题，由于dept表的行在聚簇中占用空间很小，但是剩余的空间确不能存一条dept的数据(应为我们添加了
 char(1000)了)。这样就会在那些聚簇 键块上导致过度的串链。Oracle会把包含这些信息的一组块串链或链接起来。但是如第二种方法一样，如果同时
 加载对应一个给定聚簇键的所有数据，就能尽可能紧地塞满块，等空间用完时再开始一个新块。
 
什么时候不应该使用聚簇?
  1) 如果预料到聚簇中的表会大量修改：必须知道，索引聚簇会对DML的性能产生某种负面影响(特别是INSERT语句)。管理聚簇中的数据需要做更多的
     工作。
  2) 如果需要对聚簇中的表执行全表扫描：不只是必须对你的表中的数据执行全面扫描，还必须对(可能的)多个表中的数据进行全面扫描。由于需要
     扫描更多的数据，所以全表扫描耗时更久。
  3) 如果你认为需要频繁地TRUNCATE和加载表：聚簇中的表不能截除。这是显然的，因为聚簇在一个块上存储了多个表，必须删除聚簇表中的行。
  因此，如果数据主要用于读(这并不表示“从来不写”;聚簇表完全可以修改)，而且要通过索引来读(可以是聚簇键索引，也可以是聚簇表上的其他索引)
  ，另外会频繁地把这些信息联结在一起，此时聚簇就很适合。