再上一篇:10.3堆组织表
上一篇:10.4索引组织表
主页
下一篇:10.6散列聚簇表
再下一篇:10.7有序散列聚簇表
文章列表

10.5索引聚簇表

Oracle 9i 10g编程艺术:深入数据库体系结构

我常常发现,人们对Oracle中聚簇的理解是不正确的。许多人都把聚簇与SQL Server或Sybase中 的“聚簇索引”相混淆。但它们并不一样。聚簇(cluster)是指:如果一组表有一些共同的列,则将这样 一组表存储在相同 的数据库块中;聚簇还表示把相关的数据存储在同一个块上。SQL Server中的聚簇索 引(clustered index)则要求行按索引键有序的方式存储,这类似于前面所述的IOT。利用聚簇,一个块 可能包含多个表的数据。从概念上讲,这是将数据“预联结”地存 储。聚簇还可以用于单个表,可以按某 个列将数据分组存储。例如,部门10的所有员工都存储在同一个块上(或者如果一个块放不下,则存储在 尽可能少的几个块 上)。聚簇并不是有序地存储数据(这是IOT的工作),它是按每个键以聚簇方式存储 数据,但数据存储在堆中。所以,部门100可能挨在部门1旁边,而与部 门101和99离得很远(这是指 磁盘上的物理位置)。
如 同10-8所示,图的左边使用了传统的表,EMP会存储在它的段中。DEPT也存储在自己的段中。它 们可能位于不同的文件和不同的表空间,而且绝对在单独 的区段中。从图的右边可以看到将这两个表聚簇 起来会是什么情况。方框表示数据库块。现在将值10抽取出来,只存储一次。这样聚簇的所有表中对应部 门10的 所有数据都存储在这个块上。如果部门10的所有数据在一个块上放不下,可以为原来的块串链另 外的块,来包含这些溢出的部分,这与IOT的溢出块所用的方式 类似。

图10-8 索引聚簇数据
因 此,下面来看如何创建一个聚簇对象。在对象中创建表的一个聚簇很直接。对象的存储定义
(PCTFREE、PCTUSED、INITIAL等)与 CLUSTER相关,而不是与表相关。这是有道理的,因为聚簇中会有 多个表,而且它们在同一个块上。有多个不同的PCTFREE没有意义。因此, CREATE CLUSTER非常类似于 只有很少几个列的CREATE TABLE(只有聚簇键列):

ops$tkyte@ORA10GR1> create cluster emp_dept_cluster
2 ( deptno number(2) )
3 size 1024
4 /
ster created.
在此,我们创建了一个索引聚簇(index cluster, 还有一种类型是散列聚簇(hash cluster),将
在下一节介绍)。这个聚簇的聚簇列是DEPTNO列。表中的列不必非得叫DEPTNO,但是必须是NUMBER(2), 这样才能与定 义匹配。我们在这个聚簇定义中加一个SIZE 1024选项。这个选项原来告诉Oracle:我们 希望与每个聚簇键值关联大约1024字节的数据,Oracle会在用这个数据库块上设置来计算每个块最 多能 放下多少个聚簇键。假设块大小为8KB,Oracle会在每个数据库块上放上最多7 个聚簇键(但是如果数据 比预想的更大,聚簇键可能还会少一些)。也 就是说,对应部门10、20、30、40、50、60和70的数据会 放在一个块上,一旦插入部门80,就会使用一个新块。这并不是说数据按一种有序的方式 存储,而是说 如果按这种顺序插入部门,它们会很自然地放在一起。如果按下面的顺序插入部门,即先插入 10、80、20、30、
40、50、60,然后插入 70,那么最后一个部门(70)将放在新增的块上。稍后会看到,数据的大小以及数 据插入的顺序都会影响每个块上都存储的聚簇键个数。
因 此,SIZE测试控制着每块上聚簇键的最大个数。这是对聚簇空间利用率影响最大的因素。如果把 这个SIZE设置得太高,那么每个块上的键就会很少,我们会 不必要地使用更多的空间。如果设置得太低, 又会导致数据过分串链,这又与聚簇本来的目的不符,因为聚簇原本是为了把所有相关数据都存储在一个 块上。对于聚 簇来说,SIZE是最重要的参数。
下 面来看聚簇上的聚簇索引。向聚簇中放数据之前,需要先对聚簇建立索引。可以现在就在聚簇中 创建表,但是由于我们想同时创建和填充表,而有数据之前必须有一 个聚簇索引,所以我们先来建立聚簇 索引。聚簇索引的任务是拿到一个聚簇键值,然后返回包含这个键的块的块地址。实际上这是一个主键, 其中每个聚簇键值指向 聚簇本身中的一个块。因此,我们请求部门10的数据时,Oracle会读取聚簇键, 确定相应的块地址,然后读取数据。聚簇键索引如下创建:

ops$tkyte@ORA10GR1> create index emp_dept_cluster_idx
2 on cluster emp_dept_cluster
3 /
ex created.
对 于索引平常有的存储参数,聚簇索引都可以有,而且聚簇索引可以存储在另一个表空间中。它就 像是一个常规的索引,所以同样可以在多列上建立;聚簇索引只不过 恰好是一个聚簇的索引,另外可以包 含对应完全null值的条目(这很有意思,之所以要指出这一点,原因将在第11章解释)。注意,在这个 CREATE INDEX语句中,并没有指定列的一个列表,索引列可以由 CLUSTER定义本身得出。现在我们可以在 聚簇中创建表了:

ops$tkyte@ORA10GR1> create table dept
2 ( deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13)

5 )
6 cluster emp_dept_cluster(deptno)
7 /
le created.
ops$tkyte@ORA10GR1> 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)
12 /
le created.
在 此,与“正常”表惟一的区别是,我们使用了CLUSTER关键字,并告诉Oracle基表的哪个列会映 射到聚簇本身的聚簇键。要记住,这里的段是聚簇,因 此这个表不会有诸如TABLESPACE、PCTFREE等段属 性,它们都是聚簇段的属性,而不是我们所创建的表的属性。现在可以向这些表加载初始数据 集:

$tkyte@ORA10GR1> 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 *
8 from scott.emp
9 where deptno = x.deptno;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
你 可能会奇怪,为什么不是插入所有DEPT数据,然后再插入所有EMP数据呢?或者反之,先插入所 有EMP数据,然后插入所有DEPT数据?为什么要像这样 按DEPTNO逐个地加载数据呢?原因就在于聚簇的 设计。我们在模拟一个聚簇的大批量初始加载。如果写加载所有DEPT行,每个块上就会有7个键(根据前 面指定的SIZE 1024设置),这是因为DEPT行非常小(只有几个字节)。等到加载EMP行时,可能会发现 有些部门的数据远远超过了1024字节。这样就会在那些聚簇 键块上导致过度的串链。Oracle会把包含这 些信息的一组块串链或链接起来。如果同时加载对应一个给定聚簇键的所有数据,就能尽可能紧地塞满块, 等空间 用完时再开始一个新块。Oracle并不是在每个块中放最多7个聚簇键值,而是会适当地尽可能多 地放入聚簇键值。
下 面给出一个小例子,从中可以看出这两种方法的区别。我们将向EMP表增加一个很大的列: CHAR(1000)。加这个列是为了让EMP行远远大于现在的大 小。我们将以两种方式加载聚簇表:先加载DEPT, 再加载EMP。第二次加载时,则会按部门编号来加载:先是一个DEPT行,然后是与之相关的所有EMP 行 , 然后又是一个DEPT行。我们将查看给定情况下每一行最后在哪个块上,从而得出哪种方法最好,能最好地 实现将数据按DEPTNO共同存储的目标。我们 的EMP表如下:

ops$tkyte@ORA10GR1> 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 data char(1000)
11 )
12 cluster emp_dept_cluster(deptno)

13 /
le created.
向DEPT和EMP表中加载数据时,可以看到许多EMP行与DEPT行不在同一个块上(DBMS_ROWID)是一 个内置包,可以用于查看行ID的内容):

ops$tkyte@ORA10GR1> insert into dept
2 select * from scott.dept;
ows created.
ops$tkyte@ORA10GR1> insert into emp
2 select emp.*, '*' from scott.emp;
rows created.
ops$tkyte@ORA10GR1> select dept_blk, emp_blk,
2 case when dept_blk <> emp_blk then '*' end flag,
3 deptno
4 from (
5 select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
6 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
7 dept.deptno
8 from emp, dept
9 where emp.deptno = dept.deptno
10 )
11 order by deptno
12 /
DEPT_BLK EMP_BLK F DEPTNO
---------------- -------------- - ----------

4792 4788 * 10
4792 4788 * 10
4792 4791 * 10
4792 4788 * 20
4792 4788 * 20
4792 4792 20
4792 4792 20
4792 4791 * 20
4792 4788 * 30
4792 4792 30
4792 4792 30
4792 4792 30
4792 4792 30
4792 4788 * 30 rows selected.
一半以上的EMP行与DEPT行不在同一个块上。如果使用聚簇键而不是表键来加载数据,会得到以下 结果:

$tkyte@ORA10GR1> 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'
8 from scott.emp

9 where deptno = x.deptno;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA10GR1> select dept_blk, emp_blk,
2 case when dept_blk <> emp_blk then '*' end flag,
3 deptno
4 from (
5 select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,
6 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,
7 dept.deptno
8 from emp, dept
9 where emp.deptno = dept.deptno
10 )
11 order by deptno
12 /
DEPT_BLK EMP_BLK F DEPTNO
---------- ---------- - ----------
12 12 10
12 12 10
12 12 10
11 11 20
11 11 20

11 11 20
11 12 * 20
11 11 20
10 10 30
10 10 30
10 10 30
10 10 30
10 10 30
10 11 * 30 rows selected.
注意 你 看到的结果可能与此不同,因为从SCOTT.DEPT表获取行的顺序可能会(而且将会)改变这个结果,
另外使用ASSM或是MSSM也会带来影响。不过, 概念应该很清楚:如果把对应DEPTNO=n的行放在一个给 定块上,然后再加载对应DEPTNO=n的员工行,就能得到最佳的聚簇。
大 多数EMP行都与DEPT行在同一个块上。这个例子少有些技巧,因为我故意把SIZE参数设置得很 小以便得出结论,不过这里建议的方法对于聚簇的初始加载 确实是正确可行的。由此可以确保,如果某些 聚簇键超过了估计的SIZE,最后大多数数据都会聚簇到同一个块上。如果一次加载一个表,则做不到这一 点。
这种技术只适用于聚簇的初始加载,在此之后,只有在事务认为必要的时候才应使用这个技术。你不 会为了专门使用聚簇去调整应用。
这里存在一个很让人诧异的困惑。许多人错误地认为一个rowid能惟一地标识数据库中的一个行,给 定一个rowid,就能得出这一行来自哪个表。实际上,这是做不到的。从聚簇可以得到(而且将得到)重 复的rowid。例如,执行以上代码后,你会发现:

ops$tkyte@ORA10GR1> select rowid from emp
2 intersect
3 select rowid from dept;
ID
--------------- OniAAJAAAAAKAAA OniAAJAAAAAKAAB OniAAJAAAAALAAA OniAAJAAAAAMAAA
DEPT中为各行分配的每个rowid也同时分配给了EMP中的行。这是因为,要由表和行ID共同地惟一 标识一行。Rowid伪列只是在一个表中惟一。
我还发现,许多人认为聚簇对象是一种神秘的对象,以为没有人用它,所有人都只是在使用普通表。 事实上,每次你使用Oracle的时候都会使用聚簇。例如,许多数据字典就存储在各个聚簇中:
sys@ORA10GR1> break on cluster_name sys@ORA10GR1> select cluster_name, table_name
2 from user_tables
3 where cluster_name is not null
4 order by 1;
STER_NAME TABLE_NAME
------------------------------ ------------------------------
OBJ# CCOL$
ILE#_BLOCK# UET$
LOG# MLOG$
BJ# ICOL$
BJ#_INTCOL# HISTGRM$
CDEF$ SEG$ SLOG$ CLU$
COL$
TYPE_MISC$ VIEWTRCOL$ ATTRCOL$ SUBCOLTYPE$ COLTYPE$ LOB$
TAB$ IND$ ICOLDEP$ OPQTYPE$ REFCON$ LIBRARY$ NTAB$
C_RG# RGROUP$

OID_VERSION# TYPE$
RGCHILD$
S# TS$ SER# USER$ N_SCN_TO_TIME SMON_SCN_TIME
rows selected.

COLLECTION$ METHOD$ RESULT$ PARAMETER$ ATTRIBUTE$
FET$ TSQ$
可 以看到,与对象相关的大多数数据都存储在一个聚簇(C_OBJ#聚簇)中:16个表都在同一个块中 。 这里存储的主要是与列相关的信息,所以关于表或索引列 集的所有信息都物理地存储在同一个块上。这是 有道理的:Oracle解析一个查询时,它希望访问所引用的表中所有列的数据。如果这些数据分布得到处都 是, 就要花一些时间才能把它们收集起来。如果数据都在一个块上,通常就能很容易地得到。
什么时候要使用聚簇呢?可能反过来回答什么时候不应该使用聚簇会更容易一些:
q 如果预料到聚簇中的表会大量修改:必须知道,索引聚簇会对DML的性能产生某种负面影 响(特别是INSERT语句)。管理聚簇中的数据需要做更多的工作。
q 如果需要对聚簇中的表执行全表扫描:不只是必须对你的表中的数据执行全面扫描,还必 须对(可能的)多个表中的数据进行全面扫描。由于需要扫描更多的数据,所以全表扫描耗时 更久。
q 如果你认为需要频繁地TRUNCATE和加载表:聚簇中的表不能截除。这是显然的,因为聚 簇在一个块上存储了多个表,必须删除聚簇表中的行。
因 此,如果数据主要用于读(这并不表示“从来不写”;聚簇表完全可以修改),而且要通过索引 来读(可以是聚簇键索引,也可以是聚簇表上的其他索引),另外会 频繁地把这些信息联结在一起,此时 聚簇就很适合。应用找出逻辑上相关而且总是一起使用的表,设计Oracle数据字典的人就是这样做的,他 们把与列相关的 所有信息都聚簇在一起。
索引聚簇表小结 利用聚簇表,可以物理地“预联结”数据。使用聚簇可以把多个表上的相关数据存储在同一个数据库
块上。聚簇有助于完成总是把数据联结在一起或者访问相关数据集(例如,部门10中的每一个人)的读密 集型操作。
聚簇表可以减少Oracle必须缓存的块数,从而提供缓存区缓存的效率。不好的一面是,除非你能正 确地计算出SIZE参数设置,否则聚簇在空间利用方面可能效率低下,而且可能会使有大量 DML的操作变慢 。