热衷技术，热爱交流
分类： Oracle
2013-10-16 01:34:37
表簇（table cluster）
一、什么是表簇?
表簇就是一组具备公共列的表。通常，一个数据块只能存储一个表的记录，在表簇中，一个数据库块可以存储多个表的记录。表簇中表的公共列叫做簇键(cluster key),簇键的值叫做簇键值。由于簇键是多个表的公共列，所以一个簇键值只存储一次，不论簇键值在各表中出现多少次。
存储结构：
二、表簇优点
（1）对于被聚簇表的联接，如上图所示，他们在存储时就已经‘预联结了’，这样可以减少磁盘 I/O。
（2）对于被聚簇表的联接，可以提高访问速度。
（3）只需更少的空间来存储相关的表和索引数据，因为簇键值不会为每行重复存储。
三、不适合用表簇的情况
(1)会经常被更新的表。
(2)经常需要全表扫描的
(3)需要被截断的表。簇可以截断，但是簇中的表不能截断，因为截断意味着要把表所在块设为可覆盖，这肯定是不行的。
四、表簇相关操作
(1). 建立用户，授权
SYS> create tablespace cluster1 datafile '/u01/oradata2/hx/cluster.dbf' size 50M;
SYS> create user cluster1 identified by cluster1 default tablespace cluster1 quota unlimited on cluster1;
SYS> grant connect,resource,create any cluster,alter any cluster to cluster1;
SYS> conn cluster1/cluster1
2.建立表簇
CREATE CLUSTER emp_dept (deptno NUMBER(3))
SIZE 1024
TABLESPACE cluster1
STORAGE (INITIAL 200K
NEXT 300K
MINEXTENTS 2
PCTINCREASE 33);
SIZE说明：SIZE表示我们希望为每个簇键值关联的字节数，假设块大小为8k,size设置为1024，那么一个块最多可以是7个簇键（当然还要考虑pcfree），插入第八个簇键时需要用新的块。如果这个值过大，单个数据块能存储的簇键变少，如果size值过小，可能还不够存放一条记录，这个时候数据溢出部分需要存储在串联的新块上，所以需要根据实际情况进行设置。需要注意，数据大小个数据插入顺序都会影响块上簇键的个数和数据表之间的聚合度。
3.在表簇上建立表
建立簇表时，一般不用指定表空间和pcfree等信息，因为这些信息在建簇时就已经指定，只需要映射属性到簇键即可
CREATE TABLE dept (
deptno NUMBER(3) PRIMARY KEY,
dname VARCHAR2(15) NOT NULL,
LOC VARCHAR2(15))
CLUSTER emp_dept (deptno);
CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
deptno NUMBER(3) REFERENCES dept)
CLUSTER emp_dept (deptno);
cluster1@HX> insert into dept values(100,'sales','boston');
insert into dept values(100,'sales','boston')
*
ERROR at line 1:
ORA-02032: 聚簇表无法在簇索引建立之前使用
4.使用前需要为簇建立索引：
CREATE INDEX emp_dept_index
ON CLUSTER emp_dept
TABLESPACE cluster1
STORAGE (INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 10
PCTINCREASE 33);
cluster1@HX> SELECT segment_name,SEGMENT_TYPE,SEGMENT_SUBTYPE FROM user_segments;
SEGMENT_NAME |SEGMENT_TYPE |SEGMENT_SU
-------------------------|------------------|----------
EMP_DEPT |CLUSTER |ASSM
SYS_C0037116 |INDEX |ASSM
SYS_C0037118 |INDEX |ASSM
EMP_DEPT_INDEX |INDEX |ASSM
簇索引的作用：插入数据时，数据库在物理上把每个部门的所有行存储在相同的数据块中，数据库以堆形式存储行，并使用索引定位。普通B树索引把键值和rowid关联，而B树簇索引把簇键值与数据所在块的数据库块地址 (DBA) 关联起来。簇索引还可以对应null值
5.插入数据(利用模式hr的数据)：
BEGIN
for x in ( select * from hr.dept )
loop
insert into dept(deptno,dname,loc)
values( x.deptno, x.dname, x.loc );
insert into emp(EMPNO,ENAME,DEPTNO)
select EMPNO,ENAME,DEPTNO from hr.emp
where deptno = x.deptno;
end loop;
END;
/
cluster1@HX> select * from emp;
EMPNO|ENAME | DEPTNO
----------|---------------|----------
7782|CLARK | 10
7839|KING | 10
7934|MILLER | 10
7369|SMITH | 20
7566|JONES | 20
7788|SCOTT | 20
7876|ADAMS | 20
7902|FORD | 20
7499|ALLEN | 30
7521|WARD | 30
7654|MARTIN | 30
7698|BLAKE | 30
7844|TURNER | 30
7900|JAMES | 30
14 rows selected.
cluster1@HX> select * from dept;
DEPTNO|DNAME |LOC
----------|---------------|---------------
10|ACCOUNTING |NEW YORK
20|RESEARCH |DALLAS
30|SALES |CHICAGO
40|OPERATIONS |BOSTON
6.更改簇
可以修改簇的相关属性，比如修改簇的物理属性(pctfree,pctused,initrans,maxtrans等)
The average amount of space required to store all the rows for a cluster key value(size)
The default degree of parallelism
alter cluster emp_dpet_cluster
pctfree 20
initrans 3;
cluster1@HX> select CLUSTER_NAME,KEY_SIZE,PCT_FREE,AVG_BLOCKS_PER_KEY from user_clusters;
CLUSTER_NAME | KEY_SIZE| PCT_FREE|AVG_BLOCKS_PER_KEY
------------------------------|----------|----------|------------------
EMP_DEPT | 300| 10|
cluster1@HX> ALTER CLUSTER emp_dept pctfree 20 size 500
7.删除表簇:
删除簇时，簇索引会被一起删除
cluster1@HX> create table table1(empno number references emp);
cluster1@HX> insert into table1 select empno from emp;
(1)删除表簇：
cluster1@HX> drop cluster emp_dept;
drop cluster emp_dept
*
ERROR at line 1:
ORA-00951: 簇非空
由于表簇包含了表，需要加including tables选项
cluster1@HX> drop cluster emp_dept including tables;
drop cluster emp_dept including tables
*
ERROR at line 1:
ORA-02449: 表中的唯一/主键被外键引用
由于表emp被表table1外键参照，所以不能直接删除.
cluster1@HX> drop cluster emp_dept including tables cascade constraints;
Cluster dropped.
cluster1@HX> select CONSTRAINT_NAME,table_name from user_constraints;
no rows selected
(2)删除表
与一般表的删除方法一样
(3)删除簇索引
删除簇索引后，表会出现异常
cluster1@HX> drop index emp_dept_index;
Index dropped.
cluster1@HX> select * from emp;
select * from emp
*
ERROR at line 1:
ORA-02032: 聚簇表无法在簇索引建立之前使用
建立索引后又正常了
8.簇相关数据字典
DBA_CLUSTERS
ALL_CLUSTERS
USER_CLUSTERS
accessible to the user. USER view is restricted to clusters owned by the user.
Some columns in these views contain statistics that are generated by the
DBMS_STATS package or ANALYZE statement.
DBA_CLU_COLUMNS
USER_CLU_COLUMNS
These views map table columns to cluster col
五、size影响
insert into dept(deptno,dname,loc)
select deptno+r,dname,loc from hr.dept,(select level r from dual connect by level<10)
INSERT INTO emp(empno,ename,deptno)
SELECT rownum,ename,deptno+r FROM hr.emp,(select level r from dual connect by level<10)
查看存储
cluster1@HX> select dbms_rowid.rowid_block_number(rowid) as block,count(*) from dept group by dbms_rowid.rowid_block_number(rowid);
BLOCK| COUNT(*)
----------|----------
134| 7
132| 7
133| 7
135| 7
131| 7
139| 1
可以看到，一个块上最多能存放7个簇键，与前面的假设一致
接下来，可以查看两个表的rowid，比较相同deptno的值block是否相同
cluster1@HX>select deptno,
dept_blk,
emp_blk,
case when dept_blk <> emp_blk then '*' else 'same' end flag
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
DEPTNO| DEPT_BLK| EMP_BLK|FLAG
----------|----------|----------|----
11| 135| 135|same
11| 135| 135|same
11| 135| 135|same
12| 135| 135|same
…………………………………………………………
如果size设置不当,或者插入的数据影响，两个表中相同dept的行不一定能放在同一个块上。
alter table emp add column(address char(1200) default null)
insert into dept(deptno,dname,loc)
select deptno+r,dname,loc from hr.dept,(select level r from dual connect by level<10)
INSERT INTO emp(empno,ename,deptno,address)
SELECT rownum,ename,deptno+r,'asdfjkkll' FROM hr.emp,(select level r from dual connect by level<10),
(select level r1 from dual connect by level<10)
select deptno,
dept_blk,
emp_blk,
case when dept_blk <> emp_blk then '*' else 'same' end flag
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
DEPTNO| DEPT_BLK| EMP_BLK|FLAG
----------|----------|----------|----
11| 135| 145|*
11| 135| 143|*
11| 135| 143|*
11| 135| 141|*
11| 135| 140|*
11| 135| 138|*
11| 135| 138|*
13| 131| 131|same
21| 135| 139|*
21| 135| 148|*
21| 135| 147|*
21| 135| 147|*
21| 135| 146|*
21| 135| 145|*
21| 135| 143|*
21| 135| 142|*
21| 135| 141|*
21| 135| 140|*
21| 135| 140|*
由于两个表是分别批量插入，而且emp表有个长度为1200的列，已经超过了size，两个表已经有很多相同deptno的数据不在一个数据块上了，这显然是不利的，在这种情况下，可以增大size，留出更多空间，加大聚合度。同时,最好交替插入，让两个表数据尽量都能在一个块上，这才符合表簇设计的理念
cluster1@HX> alter cluster emp_dept size 1500;
cluster1@HX> alter table emp modify address char(1200);
sys@HX> create table table1 as select deptno+r deptno,dname,loc from hr.dept,(select level r from dual connect by level<10);
create table table2 as SELECT rownum empno,ename,deptno+r deptno FROM hr.emp,(select level r from dual connect by level<10);
BEGIN
for x in ( select * from table1 )
loop
insert into dept(deptno,dname,loc)
values( x.deptno, x.dname, x.loc );
insert into emp(EMPNO,ENAME,DEPTNO,address)
select EMPNO,ENAME,DEPTNO,'asdf' from table2
where deptno = x.deptno;
end loop;
END;
/
cluster1@HX> select deptno,
2 dept_blk,
3 emp_blk,
4 case when dept_blk <> emp_blk then '*' else 'same' end flag
5 from(
select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
7 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
8 dept.deptno
9 from emp,dept
10 where emp.deptno = dept.deptno
11 )
12 /
DEPTNO| DEPT_BLK| EMP_BLK|FLAG
----------|----------|----------|----
12| 131| 131|same
12| 131| 131|same
12| 131| 131|same
22| 131| 131|same
22| 131| 131|same
22| 131| 131|same
13| 132| 132|same
13| 132| 132|same
13| 132| 132|same
23| 132| 132|same
23| 132| 132|same
23| 132| 132|same
15| 133| 133|same
15| 133| 133|same
15| 133| 133|same
25| 133| 133|same
25| 133| 133|same
25| 133| 133|same
14| 134| 134|same
14| 134| 134|same
14| 134| 134|same
24| 134| 134|same
24| 134| 134|same
24| 134| 134|same
11| 135| 135|same
11| 135| 135|same
11| 135| 135|same
21| 135| 135|same
21| 135| 135|same
21| 135| 135|same
36| 143| 136|*
36| 143| 136|*
36| 143| 136|*
36| 143| 136|*
36| 143| 136|*
17| 136| 136|same
17| 136| 136|same
21| 135| 137|*
21| 135| 137|*
31| 135| 137|*
……………………………………………………
如果单行数据不是很大，列比较少，而且不频繁更新，两表查询连接较多，就可以考虑使用表簇（看来这种应用环境实在太少）。oracle有些数据字典就使用了表簇：
sys@HX> select a.CLUSTER_NAME,b.TABLE_NAME from user_clusters a,USER_tables b where a.cluster_name=b.cluster_name;
CLUSTER_NAME |TABLE_NAME
------------------------------|------------------------------
C_COBJ# |CCOL$
C_COBJ# |CDEF$
C_FILE#_BLOCK# |UET$
C_FILE#_BLOCK# |SEG$
C_MLOG# |MLOG$
C_MLOG# |SLOG$
C_OBJ# |ICOL$
C_OBJ# |IND$
C_OBJ# |COL$
C_OBJ# |CLU$
C_OBJ# |TAB$
C_OBJ# |LOB$
C_OBJ# |COLTYPE$
C_OBJ# |SUBCOLTYPE$
C_OBJ# |NTAB$
C_OBJ# |REFCON$
C_OBJ# |OPQTYPE$
C_OBJ# |ICOLDEP$
C_OBJ# |VIEWTRCOL$
C_OBJ# |LIBRARY$
C_OBJ# |ASSEMBLY$
C_OBJ# |ATTRCOL$
C_OBJ# |TYPE_MISC$
C_OBJ#_INTCOL# |HISTGRM$
C_RG# |RGROUP$
C_RG# |RGCHILD$
C_TOID_VERSION# |TYPE$
C_TOID_VERSION# |COLLECTION$
C_TOID_VERSION# |ATTRIBUTE$
C_TOID_VERSION# |METHOD$
C_TOID_VERSION# |PARAMETER$
C_TOID_VERSION# |RESULT$
C_TS# |TS$
C_TS# |FET$
C_USER# |USER$
C_USER# |TSQ$
SMON_SCN_TO_TIME_AUX |SMON_SCN_TIME
