demo07.sql

Expert Oracle Database Architecture 9i and 10g sql源码

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;
/


alter table emp disable constraint emp_fk;
truncate cluster emp_dept_cluster;
alter table emp enable constraint emp_fk;
alter table emp add data char(1000);


insert into dept
select * from scott.dept;
insert into emp
select emp.*, '*' from scott.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
/

alter table emp disable constraint emp_fk;
truncate cluster emp_dept_cluster;
alter table emp enable constraint emp_fk;

begin
    for x in ( select * from scott.dept )
    loop
        insert into dept
        values ( x.deptno, x.dname, x.loc );
        insert into emp
        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.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
/


select rowid from emp
intersect
select rowid from dept;


connect sys

select cluster_name, table_name
from user_tables
 where cluster_name is not null
 order by 1;