《Expert one on one Oracle》- 表- 笔记-3

聚簇表

Cluster就是将拥有相同列的一些表存储在同一个数据块上，将相关数据存储在一起。例如：值为a的行都存储在同一个数据块上。Cluster不同于IOT，是以堆的形式存储。

看一个Cluster的定义：
create cluster emp_dept_cluster
( deptno number(2) )
size 1024
/
Size指定有1024byte的数据每个cluster的key值相关。对于8k的block而言，大概每个块将会存储7个cluster keys。当插入的值不在这些cluster keys中，那么将会插入新的块。Size影响cluster使用的最重要的参数，这个值太大，会造成存储空间的浪费，值太小，会影响到造成过多数据链（类似于IOT的overflow）。

create index emp_dept_cluster_idx
on cluster emp_dept_cluster
/
这条数据的主要工作就是根据cluster的key值，返回块中包含key值的地址。Key实际上是主健。首先会查找cluster key，然后决定block的地址，从而读取数据。

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

create table emp_clu
( empno number primary key,
ename varchar2(10),
job varchar2(9),
mgr number,
hiredate date,
sal number,
comm number,
deptno number(2) references dept(deptno)
)
cluster emp_dept_cluster(deptno)
/
和普通表相同，除了多了一个关键词cluster，告诉Oracle哪一列映射到cluster的cluster key。

begin
for x in ( select * from scott.dept )
loop
insert into dept_clu
values ( x.deptno, x.dname, x.loc );
insert into emp_clu
select *
from scott.emp
where deptno = x.deptno;
end loop;
end;
/
如果通过如下两个SQL查询，可以发现这两个表的数据都存储在同一个数据块上。
SELECT ROWID, deptno, DBMS_ROWID.rowid_block_number (ROWID) BLOCK
FROM dept_clu;

SELECT ROWID, deptno, DBMS_ROWID.rowid_block_number (ROWID) BLOCK
FROM emp_clu;

上述的例子是插入dept_clu的值然后再插入emp_clu表中和这个值关联的值，为什么不先完全插入完成dept_clu，然后再插入emp_clu呢？如果这样，由于dept_clu中dept中行很小，根据size设置为1024，每块就能存储下7个cluster key相关的行。当完成dept_clu的插入之后，再插入emp_clu。由于emp_clu的行比较大，可能针对一个cluster key，超过1024byte的大小，从而造成了过多的数据链。而采用上述方式载入数据，就会以更紧凑的方式存储数据，而一定是每块7个cluster key。

分别以两种方式载入数据进行对比：
SQL> create table dept_clu
2 ( deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 )
6 cluster emp_dept_cluster(deptno)
7 /

Table created.

SQL> create table emp_clu
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 data char(1000) default '*'--与前面不同，这里更改了行大小
11 )
12 cluster emp_dept_cluster(deptno)
13 /
Table created.


--以顺序的方式插入
SQL> insert into dept_clu select * from scott.dept;
5 rows created.

SQL> insert into emp_clu select emp.*,'*' from scott.emp;
14 rows created.

--Highlight部分显示超过半数emp的block与dept不在同一个block
SQL> select dbms_rowid.rowid_block_number(dept_clu.rowid) dept_rid,
2 dbms_rowid.rowid_block_number(emp_clu.rowid) emp_rid,
3 dept_clu.deptno
4 from emp_clu, dept_clu
5 where emp_clu.deptno = dept_clu.deptno
6 /

DEPT_RID EMP_RID DEPTNO
---------- ---------- ----------
18 20 10
18 19 10
18 19 10
18 18 20
18 18 20
18 20 20
18 19 20
18 19 20
18 18 30
18 18 30
18 18 30
18 18 30
18 19 30
18 19 30
14 rows selected.

--以交叉的方式插入
SQL> begin
2 for x in ( select * from scott.dept )
3 loop
4 insert into dept_clu
5 values ( x.deptno, x.dname, x.loc );
6 insert into emp_clu
7 select emp.*,'x'
8 from scott.emp
9 where deptno = x.deptno;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.

--Highlight部分显示只有两个emp的block与dept不在同一个block
SQL> select dbms_rowid.rowid_block_number(dept_clu.rowid) dept_rid,
2 dbms_rowid.rowid_block_number(emp_clu.rowid) emp_rid,
3 dept_clu.deptno
4 from emp_clu, dept_clu
5 where emp_clu.deptno = dept_clu.deptno
6 /

DEPT_RID EMP_RID DEPTNO
---------- ---------- ----------
19 19 30
19 19 30
19 19 30
19 19 30
19 19 30
19 19 30
20 20 20
20 20 20
20 20 20
20 18 20
20 18 20
20 20 10
20 20 10
20 20 10
14 rows selected.

什么情况下不使用cluster？

a. 更新比较频繁的表。
b. 需要经常全表扫描的表。因为可能需要扫描更多的表。
c. 需要频繁truncate的表。

什么情况下使用cluster?

a. 数据主要用来读取，通过索引读取，join比较频繁的表。

Hash Cluster Table

在Hash Cluster Table中，对于一行得健值，Oracle通过内部或者外部函数进行三列（hash）计算，从而决定着一行应该在磁盘位置。采用Hash算法的一个副作用是，如果没有表上建立索引，不能对hash cluster中的表进行range scan。例如：对于select * from emp where deptno between 10 and 20，如果表emp在hash cluster中，那么将会是全表扫描。

在Index Cluster Table中，空间是动态分配的，而在hash cluster table中，空间预先分配的。空间的计算公式为：(HASHKEYS/trunc(blocksize/SIZE))。

散列表有冲突值也是可以OK的。冲突的值被散列表聚簇在一起，这也是散列表的特性。这也是为什么Oracle需要指定HASHKEY和SIZE。当然，要避免那些没有预期的冲突。例如：指定HASHKEY为1000（实际应该为1009，取最接近素数），如果在这个表中放入1100个部分，那么至少就会存在一个冲突。

通过一个SHOW_SPACE的存储过程看，创建一个HASH_CLUSTER，然后查看其空间使用的情况。
SQL> create or replace
2 procedure show_space
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE',
6 p_partition in varchar2 default NULL )
7 as
8 l_free_blks number;
9
10 l_total_blocks number;
11 l_total_bytes number;
12 l_unused_blocks number;
13 l_unused_bytes number;
14 l_LastUsedExtFileId number;
15 l_LastUsedExtBlockId number;
16 l_last_used_block number;
17 procedure p( p_label in varchar2, p_num in number )
18 is
19 begin
20 dbms_output.put_line( rpad(p_label,40,'.')
21 p_num );
22 end;
23 begin
24 dbms_space.free_blocks
25 ( segment_owner => p_owner,
26 segment_name => p_segname,
27 segment_type => p_type,
28 partition_name => p_partition,
29 freelist_group_id => 0,
30 free_blks => l_free_blks );
31
32 dbms_space.unused_space
33 ( segment_owner => p_owner,
34 segment_name => p_segname,
35 segment_type => p_type,
36 partition_name => p_partition,
37 total_blocks => l_total_blocks,
38 total_bytes => l_total_bytes,
39 unused_blocks => l_unused_blocks,
40 unused_bytes => l_unused_bytes,
41 last_used_extent_file_id => l_LastUsedExtFileId,
42 last_used_extent_block_id => l_LastUsedExtBlockId,
43 last_used_block => l_last_used_block );
44
45 p( 'Free Blocks', l_free_blks );
46 p( 'Total Blocks', l_total_blocks );
47 p( 'Total Bytes', l_total_bytes );
48 p( 'Unused Blocks', l_unused_blocks );
49 p( 'Unused Bytes', l_unused_bytes );
50 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
51 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
52 p( 'Last Used Block', l_last_used_block );
53 end;
54 /

Procedure created.

SQL> create cluster hash_cluster
2 ( hash_key number )
3 hashkeys 1000
4 size 8192
5 /

Cluster created.

SQL> exec show_space('HASH_CLUSTER',user,'CLUSTER');

PL/SQL procedure successfully completed.

SQL> set serveroutput on;
SQL> exec show_space('HASH_CLUSTER',user,'CLUSTER');
Free Blocks.............................0
Total Blocks............................1024
Total Bytes.............................8388608
Unused Blocks...........................14
Unused Bytes............................114688
Last Used Ext FileId....................12
Last Used Ext BlockId...................1033
Last Used Block.........................114

PL/SQL procedure successfully completed.

可以看到，为这个一共分配了1024个块，其中有14个未使用的，1个块用于管理extends，所以共有1024-14-1共有1009个块在HWM下。1009也是1000之后下一个素数。所以，ORACLE总共为这个表分配了8192*1009空间。

刚创建一张 表的时候，HWM下的块的数量为0。然而创建一个HASH CLUSTER，由于ORACLE需要初始化每一个块，类似于将数据架载到这张表上，所以花费的时间较长。同时，对于HASH CLUSTER而言，数据的多少对于全表扫描花费同样多的时间。但是不用担心，创建HASH CLUSTER主要通过HASH KEY来查找，而不是全表扫描来查找。

对于数据相同的两张表，A表存入HASH CLUSTER，B表以堆表的方式存储。查询通常，A表的查询通常会消耗较多的CPU，B表的查询会消耗较多的内存。
什么情况下使用HASH CLUSTER
a. 在一定程度上知道表中行的数量或者上限，能够正确设置HASHKEY以及SIZE，避免重构。如果表生命周期很短，可以容易做到。
b. DML，特别是插入语句消耗资源很少，也不会产生太多负担，更新语句更新HASHKEY则不一样，会产生行迁移。
c. 适合经常需要访问的数据。