关于聚簇

Oracle聚簇表就其原理就是按键将相关的块信息存储在一起，这样在做交叉查询的时候，就可以需要比较少量的I/O了

以下是一个对比测试

建立聚簇
create cluster emp_dept_cluster
( deptno number(2) )
size 1024

create index emp_dept_cluster_idx
on cluster emp_dept_cluster

建立聚簇表

create table dept
( deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13)
)
cluster emp_dept_cluster(deptno)
/
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)
/

在聚簇表中填入数据

begin
for x in ( select * from scott.dept )
loop
insert into dept
values ( x.deptno, x.dname, x.loc );
insert into emp
select *
from scott.emp
where deptno = x.deptno;
end loop;
end;
/

注：聚簇表填入数据最好的作法是一个"簇"一个"簇"的填入，这样每个块里面就可以用满，且减少串链的情况


以下是普通表情况

create table dept_1
( deptno number(2) primary key,
dname varchar2(14),
loc varchar2(13)
)
/
create table emp_1
( empno number primary key,
ename varchar2(10),
job varchar2(9),
mgr number,
hiredate date,
sal number,
comm number,
deptno number(2) constraint emp_fk_1 references dept_1(deptno)
)
/

填入数据

begin
for x in ( select * from scott.dept )
loop
insert into dept
values ( x.deptno, x.dname, x.loc );
insert into emp
select *
from scott.emp
where deptno = x.deptno;
end loop;
end;
/


打开autotrace

set autotrace on

聚簇表查询

sql> select a.ename from emp a,dept b where a.deptno=b.deptno and b.dname='ACCOUNTING';

ENAME
----------
CLARK
KING
MILLER


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 table ACCESS (FULL) OF 'DEPT'
3 1 table ACCESS (CLUSTER) OF 'EMP'



Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
455 bytes sent via sql*Net to client
503 bytes received via sql*Net from client
2 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

普通表查询

sql> select a.ename from emp_1 a,dept_1 b where a.deptno=b.deptno and b.dname='ACCOUNTING';

ENAME
----------
CLARK
KING
MILLER


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 table ACCESS (FULL) OF 'EMP_1'
3 1 table ACCESS (BY INDEX ROWID) OF 'DEPT_1'
4 3 INDEX (UNIQUE SCAN) OF 'SYS_C001968' (UNIQUE)



Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
32 consistent gets
0 physical reads
0 redo size
455 bytes sent via sql*Net to client
503 bytes received via sql*Net from client
2 sql*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

通过对比可以看出，采用聚簇表的I/O(18)，少于普通表I/O（32），在大并发高业务量的时候就很可观了。


总结：如果读比写多，要通过索引来读，还会频繁和其它系统聚合在一起，就非常适合使用聚簇表