聚簇表

聚簇表

 

一 聚簇表的定义

聚簇表可以将多个表捆绑在一起，它是一种数据结构，在这个结构中，聚簇将一个或多表都放到同一个块或段中。使得聚簇中的表在通过聚簇建关联查询时能够减少对块的频繁获取。簇其实就是一组表，是一组共享相同数据块的多个表组成。 将经常一起使用的表组合在一起成簇可以提高处理效率。在一个簇中的表就叫做簇表。

 

二 聚簇表的适用范围

如果两个表经常按照某个列来做连接查询，则可以考虑将这两个表相关的行按照这个连接列聚簇到一个簇中。簇提供一种可选的存储表的数据的方法,一个簇是由共享相同数据块的一组表组成的,因为这些表共享公共的列并且经常一起被使用,所以将这些表组合在一起。

比如：

select * from emp,dept where emp.deptno = dept.deptno;

把EMP表和DEPT表的数据聚集在少量的BLOCK里，减少系统IO，提供查询效率。emp和dept表共享deptno列,当将emp和dept表组成簇,Oracle物理将emp和dept表中有关每个部门的所有行存储到相同的数据块。

OLTP 类的系统用的少。

数据仓库类的可能会使用。

对于经常单独使用的表来说,不应该使用簇。

 

三 聚簇表的优点

因为簇将不同表的相关的行一起存储到相同的数据块,所以合理使用簇的优势：

1)两表连接时磁盘I/O减少

2)连接查询时间减少

3)簇值相同的只存储一次

 

四 聚簇表的分类

单表聚簇：按照某表里的一个字段来集中存储值相同的行在一个数据库块上，这样按照这个字段来扫描数据时，会显著降低i/0;

多表聚簇：例如把经常一起访问的两张表的信息集中存储在一个数据块上，这样，显著提高两表连接的效率，显著降低i/0。

虽然，有这些好处，但是 应用开发过程中，很少使用这两种存储结构。

如果加载数据后，不存在对 聚簇 键修改的情况，可适当使用，否则，严重降低DML 的效率。

 

五 创建簇

 

建立顺序是：簇→簇表→簇索引→加载数据

 

1 创建簇

SQL> create cluster emp_dept_cluster

  2  (deptno number(2))

  3  size 1024;

Cluster created.

 

2 创建簇索引

SQL> create index emp_dept_cluster_idx on cluster emp_dept_cluster;

Index created.

SQL> select dbms_metadata.get_ddl('TABLE','DEPT','SCOTT') from dual;

SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

 

3 创建簇表

-- Create table

SQL>

create table DEPT

(

  DEPTNO NUMBER(2) not null,

  DNAME  VARCHAR2(14),

  LOC    VARCHAR2(13)

)

cluster emp_dept_cluster(deptno)

;

-- Create/Recreate primary, unique and foreign key constraints

SQL>

alter table DEPT add constraint PK_DEPT primary key (DEPTNO);

 

-- Create table

SQL>

create table EMP

(

  EMPNO    NUMBER(4) not null,

  ENAME    VARCHAR2(10),

  JOB      VARCHAR2(9),

  MGR      NUMBER(4),

  HIREDATE DATE,

  SAL      NUMBER(7,2),

  COMM     NUMBER(7,2),

  DEPTNO   NUMBER(2)

)

cluster emp_dept_cluster(deptno)

;

-- Create/Recreate primary, unique and foreign key constraints

SQL> alter table EMP add constraint PK_EMP primary key (EMPNO);

SQL> alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);

 

SQL> col cluster_name for a20

SQL> col table_name for a15

SQL> select cluster_name,table_name from user_tables where cluster_name like 'EMP%';

 

CLUSTER_NAME         TABLE_NAME

-------------------- ---------------

EMP_DEPT_CLUSTER     DEPT

EMP_DEPT_CLUSTER     EMP

 

4 加载数据

加载的方式不同，数据分布也不同。

为了实验更加直观增加一个列char（1000）

 SQL> alter table emp add data char(1000);

Table altered.

 

SQL> begin

  2    for x in (select * from scott.dept)    

  3    loop

  4      insert into dept

  5      values(x.deptno,x.dname,x.loc);

  6      insert into emp

  7      select emp.*,'x' from scott.emp where deptno=x.deptno;

  8    end loop;

  9  end;

 10  /

PL/SQL procedure successfully completed.

 

SQL> commit;

Commit complete.

 

SQL> 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_BLK    EMP_BLK FL     DEPTNO

---------- ---------- -- ----------

     85505      85505            10

     85505      85505            10

     85505      85505            10

     85505      85506 *          20

     85505      85506 *          20

     85505      85505            20

     85505      85505            20

     85505      85505            20

     85506      85507 *          30

     85506      85507 *          30

     85506      85506            30

 

  DEPT_BLK    EMP_BLK FL     DEPTNO

---------- ---------- -- ----------

     85506      85506            30

     85506      85506            30

     85506      85506            30

 

14 rows selected.

 

5 卸载数据

SQL> truncate cluster emp_dept_cluster;

Cluster truncated.

 

SQL> alter table emp enable constraint emp fk_deptno;

alter table emp enable constraint emp fk_deptno

                                      *

ERROR at line 1:

ORA-00933: SQL command not properly ended

 

SQL> alter table emp enable constraint fk_deptno;

Table altered.

 

SQL> select count(*) from dept;

  COUNT(*)

----------

         0

 

SQL> select count(*) from emp;

  COUNT(*)

----------

         0

 

 

来自：http://blog.chinaunix.net/uid-20478213-id-1942124.html

http://blog.itpub.net/17203031/viewspace-774405/

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29785807/viewspace-1733683/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29785807/viewspace-1733683/