簇表初探

总结：1.簇表的数据是放在簇里
      2.簇表上要建索引，否则会报错
      3.顺序的插入能让簇表的效率更高

以下是具体实验过程
实验1：
conn scott/tiger

desc dept
SQL> desc dept
 名称                                      是否为空? 类型
 ----------------------------------------- -------- -------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

create cluster emp_dept_cluster
   (deptno number(2))
   size 1024;

create index emp_dept_cluster_idx
    on cluster emp_dept_cluster

create table dept_t
(deptno number(2) primary key,  dname varchar2(14),
loc varchar2(13)
)
cluster emp_dept_cluster(deptno)

create table emp_t
(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;

begin
for x in ( select * from scott.dept )
 loop
insert into dept_t
 values ( x.deptno, x.dname, x.loc );
 insert into emp_t
 select *
 from scott.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_t.rowid) dept_blk,
 dbms_rowid.rowid_block_number(emp_t.rowid) emp_blk,
dept_t.deptno
 from emp_t, dept_t
where emp_t.deptno = dept_t.deptno
 )
 order by deptno
这里可以看到都在一个块里

alter table emp_t disable constraint emp_fk;

truncate cluster emp_dept_cluster;

alter table emp_t enable constraint emp_fk;

SQL> SELECT COUNT(*) FROM DEPT_T;

  COUNT(*)
----------
         0

SQL> SELECT COUNT(*) FROM EMP_T;

  COUNT(*)
----------
         0
此处说明：簇表的数据是存放在簇里
进一步验证
select * from dba_SEGMENTS WHERE SEGMENT_NAME='EMP_DEPT_CLUSTER'
有数据
select * from dba_SEGMENTS WHERE SEGMENT_NAME='DEPT_T'
没有数据

实验二：
alter table emp_T add data char(1000);

insert into dept_t
  select * from dept;

insert into emp_t
 select emp.*,'*' from emp;


select dept_blk, emp_blk,
case when dept_blk <> emp_blk then '*' end flag,
deptno
 from (
select dbms_rowid.rowid_block_number(dept_t.rowid) dept_blk,
 dbms_rowid.rowid_block_number(emp_t.rowid) emp_blk,
dept_t.deptno
 from emp_t, dept_t
where emp_t.deptno = dept_t.deptno
 )
 order by deptno

实验3：有更多的行在一个块上
alter table emp_t disable constraint emp_fk;

truncate cluster emp_dept_cluster;

alter table emp_t enable constraint emp_fk;

begin
 for x in ( select * from scott.dept )
 loop
insert into dept_t
 values ( x.deptno, x.dname, x.loc );
 insert into emp_t
 select emp.*, 'x'
  from scott.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_t.rowid) dept_blk,
 dbms_rowid.rowid_block_number(emp_t.rowid) emp_blk,
dept_t.deptno
 from emp_t, dept_t
where emp_t.deptno = dept_t.deptno
 )
 order by deptno