Oracle的簇与簇表
--========================================
-- 簇表及簇表管理(Index clustered tables)
--========================================
簇表是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(sid number)
- pctused 40
- pctfree 10
- size 1024
- storage
- (initial 128k
- next 128k
- minextents 2
- maxextents 20
- )tablespace huizhi;
创建簇表
- 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);
- --表已创建
- insert into student values(1,'小明',24);
创建簇索引
- create index stu_ach_index
- on cluster stu_ach
- tablespace huizhi;
管理簇
修改簇
删除簇
- drop cluster stu_ach including tables;
- 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)如果空间不够，并且不能为将要插入的新记录分配额外的空间，那么不要使用聚簇
About Me
...............................................................................................................................
● 本文整理自网络
● 本文在itpub（http://blog.itpub.net/26736162）、博客园（http://www.cnblogs.com/lhrbest）和个人微信公众号（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文博客园地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麦苗云盘地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 数据库笔试面试题库及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 联系我请加QQ好友(646634621)，注明添加缘由
● 于 2017-06-02 09:00 ~ 2017-06-30 22:00 在魔都完成
● 文章内容来源于小麦苗的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
...............................................................................................................................
拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号：xiaomaimiaolhr，扫描右边的二维码加入小麦苗的QQ群，学习最实用的数据库技术。
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26736162/viewspace-2140737/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/26736162/viewspace-2140737/
扫一扫
1017
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
