簇表实验

最新推荐文章于 2024-01-22 09:43:32 发布

cuanyileng9226

最新推荐文章于 2024-01-22 09:43:32 发布

阅读量86

点赞数

＝＝建立簇表
create cluster emp_dept_cluster
( deptno number(5) )
size 1024
/

＝＝设置每个键值大小为1024,那么8K的块最多存入8个KEY

注意，这个1024是每个键值的大小

不是总大小

＝＝建立簇索引

create index emp_dept_cluster_idx
on cluster emp_dept_cluster
/

＝＝建立簇基表(dept)

create table dept
( deptno number(5) primary key,
dname varchar2(14),
loc varchar2(13)
)
cluster emp_dept_cluster(deptno)
/

＝＝建立一个普通堆表

create table dept_heap
( deptno number(5) primary key,
dname varchar2(14),
loc varchar2(13)
)
/

＝＝同样的道理,建立跟dept关联的emp的簇表,簇基表,簇索引和普通堆表

create table emp 　　　　　
( empno    number primary key,
  ename    varchar2(10), job      varchar2(9),
  mgr      number,       hiredate date,
  sal      number,       comm     number,
  deptno   number(5) references dept(deptno)
)
cluster emp_dept_cluster(deptno);

create table emp_heap
( empno    number primary key,
  ename    varchar2(10), job      varchar2(9),
  mgr      number,       hiredate date,
  sal      number,       comm     number,
  deptno   number(5) references dept_heap(deptno)
);
create index emp_heap_deptno_idx on emp_heap(deptno);

＝＝往簇基表插入10000条记录

insert into dept (deptno, dname, loc )
select rownum,
       substr( object_name, 1, dbms_random.value( 5, 14 ) ),
       substr( owner, 1, dbms_random.value( 8, 13 ) )
  from dba_objects
where rownum <= 10000
/

＝＝往普通堆表里插入10000条记录

insert into dept_heap (deptno, dname, loc )
select rownum,
       substr( object_name, 1, dbms_random.value( 5, 14 ) ),
       substr( owner, 1, dbms_random.value( 8, 13 ) )
  from dba_objects
where rownum <= 10000
/

＝＝往簇表emp插入记录

insert into emp (empno, ename, job, mgr, hiredate, sal, comm, deptno )
select object_id empno,
       substr(object_name,1,10) ename, substr(object_name,1,9) job,
       object_id mgr, created hiredate, data_object_id sal,
       data_object_id comm,
       mod(rownum,10000)+1 deptno
  from dba_objects
/

＝＝往普通堆表emp_heap插入记录

insert into emp_heap (empno, ename, job, mgr, hiredate, sal, comm, deptno )
select object_id empno,
       substr(object_name,1,10) ename, substr(object_name,1,9) job,
       object_id mgr, created hiredate, data_object_id sal,
       data_object_id comm,
       mod(rownum,10000)+1 deptno
  from dba_objects
/

＝＝分析所有的堆表,簇基表和簇索引

exec dbms_stats.gather_table_stats( user, 'EMP' );
exec dbms_stats.gather_table_stats( user, 'EMP_HEAP' );
exec dbms_stats.gather_table_stats( user, 'DEPT' );
exec dbms_stats.gather_table_stats( user, 'DEPT_HEAP' );
exec dbms_stats.gather_index_stats( user, 'EMP_DEPT_CLUSTER_IDX' );

column PLAN_TABLE_OUTPUT format a72 truncate

delete from plan_table;

＝＝查看簇基表进行关联的效率

explain plan for
select *
from emp, dept
where emp.deptno = dept.deptno
and dept.deptno = 10
/

select * from table(dbms_xplan.display);

delete from plan_table;

＝＝查看普通堆表关联的效率

explain plan for
select *
from emp_heap, dept_heap
where emp_heap.deptno = dept_heap.deptno
and dept_heap.deptno = 10
/

select * from table(dbms_xplan.display);

簇表效率更高

set termout off
commit;

＝＝查看簇基表关联是不是同一行记录在同一块

select dbms_rowid.rowid_block_number( emp.rowid ) erid,
       dbms_rowid.rowid_block_number( dept.rowid ) drid
  from emp, dept
where emp.deptno = dept.deptno
   and dept.deptno = 10
/

     ERID       DRID
---------- ----------
     64059      64059
     64059      64059
     64059      64059
     64059      64059
     64059      64059
     64059      64059

6 rows selected.

＝＝查看普通堆表关联是不是同一行记录在同一块

select dbms_rowid.rowid_block_number( emp_heap.rowid ) erid,
       dbms_rowid.rowid_block_number( dept_heap.rowid ) drid
  from emp_heap, dept_heap
where emp_heap.deptno = dept_heap.deptno
   and dept_heap.deptno = 10
/
      ERID       DRID
---------- ----------
     64106      64082
     64861      64082
     65042      64082
     65111      64082
     65308      64082
     65378      64082