Oracle的簇与簇表

簇表是Oracle中一种可选、的存储表数据的方法。使用簇表可以减少磁盘I/O，改善访问簇表的联结所带来的资源开销，本文讲述了簇表的原理、创建以及管理簇表等。

一、什么是簇表及簇表的特性

1.簇表

由共享相同数据块的一组表组成。在堆表的管理过程中，对于某些表的某些列和另外的表的某些列经常被用来联结使用，可以将这些表的联结列作为共享的公共列而将这些表组合在一起。这就是簇表形成的原因。例如，scott模式中，有emp表，dept表，两个表经常使用 deptno列来进行联结，为此，我们共享deptno列，将emp和dept表组成簇表。组成簇表后，Oracle物理上将emp和dept表中有关每个部门所有行存储到相同的数据块中。

簇表不能等同于SQL server中的簇索引，两者并不是一回事。SQL server中的簇索引是使得行的存储按索引键来存储，类似于IOT表。

2.簇键

簇键是列或多列的组合，为簇表所共有

在创建簇时指定簇键的列，以后在创建增加的簇中的每个表时，指定相同的列即可

每个簇键值在簇和簇索引中仅仅存储一次，与不同表中有有多少这样的行无关

3.使用簇表的好处。

减少磁盘I/O，减少了因使用联结所带来的系统开销

节省了磁盘存储空间，因为原来需要单独存放多张表，现在可以将联结的部分作为共享列的存储。

4.何时创建簇表

对于经常查询、当DML较少的表

表中的记录经常使用到联结查询

5.创建簇表的步骤

创建簇

创建簇索引

创建簇表

6.创建簇、簇键、簇表时考虑的问题

哪些表适用于创建簇

对于创建簇的表哪些列用作簇列

创建簇时数据块空间如何使用(pctfree,pctused)

平均簇键及相关行所需的空间大小

簇索引的位置(比如存放到不同的表空间)

预估簇的大小

二、创建簇及簇表

在创建簇时，如果未指定索引列，则默认地创建一个索引簇。

如果指定了散列参数，如hashkeys,hashis 或single table hashkeys，则可以创建散列簇

SQL> show user;

USER is "ROBINSON"

SQL> create cluster emp_dept_cluster(deptno number(2))

2 pctused 80

3 pctfree 15

4 size 1024

5 tablespace users;

Cluster created.

在上面创建的簇中，一个最重要的参数就是size，需要为size 指定合适的大小，如果size 指定的太大，则每个块仅仅能存放

少量的簇，容易引起空间的浪费，如果指定的太小，则容易产生过多的数据链

创建簇索引的条件

模式中必须包含簇

必须具有create any index的权限

簇索引的作用

用于一个簇键值并返回的包含该簇键值的地址块

SQL> create index emp_dept_cluster_idx

2 on cluster emp_dept_cluster;

Index created.

创建簇表

SQL> create table dept

2 (deptno number(2) primary key,

3 dname varchar2(14),

4 loc varchar2(13)

5 )

6 cluster emp_dept_cluster(deptno); --使用了cluster关键字后面跟簇名、簇列

Table created.

SQL> create table emp

2 (empno number primary key,

3 ename varchar2(10),

4 job varchar2(9),

5 mgr number,

6 hiredate date,

7 sal number,

8 comm number,

9 deptno number(2) references dept(deptno)

10 )

11 cluster emp_dept_cluster(deptno); --使用了cluster关键字后面跟簇名、簇列

Table created.

对于创建的簇表,与普通表的唯一差别是使用了cluster关键字，即告诉oracle 基表的哪一列将映射到簇表中

查看刚刚创建的簇对象

SQL> select object_name,object_type,status from user_objects order by object_name ;

OBJECT_NAME OBJECT_TYPE STATUS

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

DEPT TABLE VALID --簇表dept

EMP TABLE VALID --簇表emp

EMP_DEPT_CLUSTER CLUSTER VALID --簇emp_dept_cluster

EMP_DEPT_CLUSTER_IDX INDEX VALID --簇索引

SYS_C005422 INDEX VALID

SYS_C005423 INDEX VALID

SQL> select table_name,tablespace_name,cluster_name,status,pct_free from

2 dba_tables where owner = 'ROBINSON';

TABLE_NAME TABLESPACE_NAME CLUSTER_NAME STATUS PCT_FREE

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

EMP USERS EMP_DEPT_CLUSTER VALID 0

DEPT USERS EMP_DEPT_CLUSTER VALID 0

下面开始对簇表填充数据

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 * from scott.emp

8 where deptno = x.deptno;

9 end loop;

10 end;

11 /

PL/SQL procedure successfully completed.

三、更改簇

对于已经创建的簇，我们可以修改簇的相关属性，比如

修改簇的物理属性(pctfree,pctused,initrans,maxtrans等)

存储簇键值的所有行所需空间的平均值(size)

默认的并行度

alter cluster emp_dpet_cluster

pctfree 20

initrans 3;

四、删除簇、簇表

1.删除簇

可以删除不再需要的簇，删除簇时，簇中对应的表及对应的簇索引都将被删除

簇数据段占用的盘区以及簇索引段占用的盘区将被释放返还给各自所在的表空间

删除不包含表及索引的簇

drop cluster emp_dept_cluster;

对于包含簇表的簇，可以使用including talbes选项,如果簇中包含表但未使用including tables子句，将收到错误信息

drop cluster emp_dept_cluster including tables;

对于包含簇之外的foreign key 约束说参照的主键，需要使用cascade constraints子句

drop cluster emp_dept including tables cascade constraints

2.删除簇表

对于不再使用的簇表可以直接使用drop table table_name命令来删除

drop table emp;

drop table dept;

3.删除簇索引

簇索引可以被删除而不影响簇或它的簇表

若不存在簇索引则簇表也无法使用

对于簇的访问，则需要重建簇索引

drop index emp_dept_cluster_idx;

五、簇的相关视图

dba_clusters

all_clusters

user_clusters

dba_clu_columns

user_clu_columns

六、演示相关操作

查看dba_clusters视图获得所创建的簇

SQL> select cluster_name,tablespace_name,pct_free,pct_used,ini_trans

2 from dba_clusters where owner = 'ROBINSON';

CLUSTER_NAME TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS

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

EMP_DEPT_CLUSTER USERS 15 2

查看簇列

SQL> select * from user_clu_columns;

CLUSTER_NAME CLU_COLUMN_NAME TABLE_NAME TAB_COLUMN_NAME

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

EMP_DEPT_CLUSTER DEPTNO DEPT DEPTNO

EMP_DEPT_CLUSTER DEPTNO EMP DEPTNO

修改簇的相关属性

SQL> alter cluster emp_dept_cluster

2 pctfree 20

3 initrans 3;

Cluster altered.

SQL> select cluster_name,tablespace_name,pct_free,pct_used,ini_trans

2 from dba_clusters where owner = 'ROBINSON';

CLUSTER_NAME TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS

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

EMP_DEPT_CLUSTER USERS 20 3

从dba_segments可以看到簇产生了簇段，簇索引产生的为索引段

SQL> select segment_name,tablespace_name,segment_type from dba_segments where owner = 'ROBINSON';

SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE

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

EMP_DEPT_CLUSTER USERS CLUSTER

EMP_DEPT_CLUSTER_IDX USERS INDEX

SYS_C005422 USERS INDEX

SYS_C005423 USERS INDEX

删除簇，簇为非空时收到错误提示

SQL> drop cluster emp_dept_cluster;

drop cluster emp_dept_cluster

ERROR at line 1:

ORA-00951: cluster not empty

使用including tables 删除簇及簇表、簇索引

SQL> drop cluster emp_dept_cluster including tables;

Cluster dropped.

SQL> select segment_name,tablespace_name from dba_segments where owner = 'ROBINSON';

no rows selected

簇由一组共享多个数据块的多个表组成，它将这些表的相关行一起存储到相同数据块中，这样可以减少查询数据所需的磁盘读取量。创建簇后，用户可以在簇中创建表，这些表称为簇表。

例如有如下两个表：student和achievement.。其中，student表存储学生信息，需要使用SID字段（存储学生ID）；achievement表存储学生成绩信息，也需要使用SID字段。也就是说，student和achievement需要共享学生ID数据块。

注意：如果用户在自己的模式中创建簇，则必须具有create cluster权限和unlimited tablespace系统权限；如果想在其他模式中创建簇，则还必须具有create any cluster系统权限

创建簇和簇表

创建簇

创建簇，需要使用create cluster语句，例如创建一个名为stu_ach的簇，如下：

[sql] view plain copy

create cluster stu_ach(sid number)
pctused 40
pctfree 10
size 1024
storage
(initial 128k
next 128k
minextents 2
maxextents 20
)tablespace huizhi;

上面创建簇stu_ach时，指定通过SID字段来对簇中的表进行聚簇存储，这个SID字段就可以称之为聚簇字段。

注意：size子句用来为聚簇字段提供指定的数据块数量。例如，将size设置为1024，即表明簇中的聚簇字段记录只能存储在1024个数据块中。

创建簇表

创建簇表，需要使用cluster子句指定所使用的簇和簇字段。

例如，在stu_ach簇中创建两个簇表：student和achievement。如下：

[sql] view plain copy

create table student(
sid number,
sname varchar2(8),
sage number
)
cluster stu_ach(sid);
--表已创建
create table achievement(
aid number,
score number,
sid number
)
cluster stu_ach(sid);
--表已创建

上例在创建student和achievement表时，使用cluster子句指定它们所使用的簇为stu_ach，所使用的簇字段为SID。

提示：将student和achievement两个表组成一个簇后，在物理上oracle会将这两个表中每个学生的学生信息和该学生的所有成绩信息存储到相同的数据块中。

现在向student表中添加记录，如下：

[sql] view plain copy

insert into student values(1,'小明',24);

发现还无法向簇表中添加记录。

注意：为了能够向簇表中添加记录，还需要首先为簇建立索引。

创建簇索引

簇索引与簇表不同，它并不存在于簇中，而是与普通索引一样需要具有独立的存储空间。

例如，为簇stu_ach建立一个簇索引，如下：

[sql] view plain copy

create index stu_ach_index
on cluster stu_ach
tablespace huizhi;

上例为簇stu_ach建立了一个名为stu_ach_index的簇索引。创建簇索引后，就可以向簇表中添加记录了。

管理簇

对一个簇的管理主要是修改簇和删除簇。如果用户想要管理簇，则必须具有alter any cluster系统权限。

修改簇

修改一个簇，主要是修改簇的如下属性值:

1.物理存储属性，包括pctfree、pctused、initrans、maxtrans和storage。

2.为了存储簇键值的所有行所需空间的平均值size。

3.默认的并行度。

删除簇

1.删除一个空簇：当一个簇中不包含簇表时，删除该簇可以使用drop cluster cluster_name语句。

2.删除一个含有簇表的簇：需要使用drop cluster...including tables语句，如下

[sql] view plain copy

drop cluster stu_ach including tables;

另外，如果某个簇含有簇表，并且有外键约束，则需要使用drop cluster...including tables cascade constraints语句删除该簇。如下：

[sql] view plain copy

drop cluster stu_ach including tables cascade constraints;

簇其实就是一组表，由一组共享相同数据块的多个表组成，将经常一起使用的表组合在一起成簇可以提高处理效率；在一个簇中的表就叫做簇表。
建立顺序是：簇→簇表→簇索引→数据
创建簇的格式
CREATE CLUSTER cluster_name
(column date_type [,column datatype]...)
[PCTUSED 40 | integer] [PCTFREE 10 | integer]
[SIZE integer]
[INITRANS 1 | integer] [MAXTRANS 255 | integer]
[TABLESPACE tablespace]
[STORAGE storage]
SIZE：指定估计平均簇键，以及与其相关的行所需的字节数。
1、创建簇

复制代码代码如下:

    create cluster my_clu (deptno number )
    pctused 60
    pctfree 10
    size 1024
    tablespace users
    storage (
    initial 128 k
    next 128 k
    minextents 2
    maxextents 20
    );

2、创建簇表

复制代码代码如下:

    create table t1_dept(
    deptno number ,
    dname varchar2 ( 20 )
    )
    cluster my_clu(deptno);
    create table t1_emp(
    empno number ,
    ename varchar2 ( 20 ),
    birth_date date ,
    deptno number
    )
    cluster my_clu(deptno);

3、为簇创建索引

复制代码代码如下:

create index clu_index on cluster my_clu;

注：若不创建簇索引，则在插入数据时报错：ORA-02032: clustered tables cannot be used before the cluster index is built
管理簇
使用ALTER修改簇属性(必须拥有ALTER ANY CLUSTER的权限)
1、修改簇属性
可以修改的簇属性包括：
* PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE
* 为了存储簇键值所有行所需空间的平均值SIZE
* 默认并行度
注：
* 不能修改INITIAL和MINEXTENTS的值
* PCTFREE、PCTUSED、SIZE参数修改后适用于所有数据块
* INITRANS、MAXTRANS仅适用于以后分配的数据块
* STORAGE参数修改后仅影响以后分配给簇的盘区
例：

复制代码代码如下:

alter cluster my_clu
pctused 40

2、删除簇

复制代码代码如下:

    drop cluster my_clu; -- 仅适用于删除空簇
    drop cluster my_clu including tables ; -- 删除簇和簇表
    drop cluster my_clu including tables cascade constraints ;--同时删除外键约束

注：簇表可以像普通表一样删除。
3、清空簇

复制代码代码如下:

truncate cluster my_clu;

注：所有在此簇上的表的数据全部被清空
散列聚簇表
在簇表中，Oracle使用存储在索引中的键值来定位表中的行，而在散列聚簇表中，使用了散列函数代替了簇索引，先通过内部函数或者自定义的函数进行散列计算，然后再将计算得到的码值用于定位表中的行。创建散列簇需要用到HASHKEYS子句。
1、创建散列簇

复制代码代码如下:

    create cluster my_clu_two(empno number(10) )
    pctused 70
    pctfree 10
    tablespace users
    hash is empno
    hashkeys 150 ;

说明：
* hash is 子句指明了进行散列的列，如果列是唯一的标示行，就可以将列指定为散列值
* hashkeys 指定和限制散列函数可以产生的唯一的散列值的数量
2、创建散列表

复制代码代码如下:

    create table t2_emp (
    empno number ( 10 ),
    ename varchar2 ( 20 ),
    birth_date date ,
    deptno number )
    cluster my_clu_two(empno);

注意：
* 必须设置数值的精度
* 散列簇不能也不用创建索引
* 散列簇不能ALTER:size、hashkeys、hash is参数
不宜用聚簇表的情况
1)如果预料到聚簇中的表会大量修改,聚簇表会对DML的性能产生负面影响
2)非常不适合对单表的全表扫描,因为只能引起对其它表的全表扫描
3)频繁对表进行TRUNCATE和加载,因为聚簇中的表是不能TRUNCATE的，只能TRUNCATE簇
4)如果表只是偶尔被连接或者它们的公共列经常被修改，则不要聚簇表
5)如果经常从所有有相同聚簇键值的表查询出的结果数据超过一个或两个Oracle块，则不要聚簇表
6)如果空间不够，并且不能为将要插入的新记录分配额外的空间，那么不要使用聚簇

Oracle的簇与簇表

Oracle的簇与簇表

创建簇和簇表

创建簇

创建簇表

创建簇索引

管理簇

修改簇

删除簇

更多相关文章

随机推荐