oracle数据库表_笔记7:索引聚簇表

索引聚簇表

聚簇(cluster)是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中;聚簇还表示把相关的数据存储在同一个块上。利用聚簇，一个块可能包含多个表的数据。从概念上讲，这是将数据“预联结”地存储。聚簇还可以用于单个表，可以按某个列将数据分组存储。聚簇并不是有序地存储数据(这是IOT的工作)，它是按某个键以聚簇方式存储数据，但数据存储在堆中。

在对象中创建表的一个聚簇很直接。对象的存储定义(PCTFREE、PCTUSED、INITIAL等)与CLUSTER相关，而不是与表相关。这是有道理的，因为聚簇中会有多个表，而且它们在同一个块上。有多个不同的PCTFREE没有意义。因此，CREATE CLUSTER非常类似于只有很少几个列的CREATE TABLE(只有聚簇键列)

sys@ORCL> create cluster emp_dept_cluster

2 (deptno number(2))

3 size 1024

4 /

Cluster created.

在此，创建一个索引聚簇(index cluster)。这个聚簇的聚簇列是DEPTNO列。表中的列不必非得叫DEPTNO，但是必须是NUMBER(2)，这样才能与定义匹配。在这个聚簇定义中加了一个SIZE 1024选项。这个选项用来告诉Oracle：我们希望与每个聚簇键值关联大约1024字节的数据。Oracle会使用这个选项设置来计算每个块最多能放下多少聚簇键。假设块大小为8KB,Oracle会在每个数据库块上放上最多7个聚簇键(但是如果数据比预想的更大，聚簇键可能还会少一些)。也就是说，对应部门10、20、30、40、50、60和70的数据会放在一个块上，一理插入部门80，就会使用一个新块。这并不是说数据按一种有序的方式存储，而是说如果按这种顺序插入部门，它们会自然地放在一起。如果按下面的顺序插入部门，即先插入10、20、30、40、50、60，然后插入70，那么最后一个部门(70)将谢谢在新增的块上。数据的大小以及数据插入的顺序都会影响每个块上能存储的聚簇键个数。

因此，SIZE参数控制着每块上聚簇键的最大个数。这是对聚簇空间利用率影响最大的因素。如果把这个SIZE设置得太高，那么每个块上的键就会很少，会不必要的使用更多的空间。如果设置得太低，又会导致数据过分串链，这又与聚簇本来的目的不符，因为聚簇原本是为了把所有相关数据都存储在一个块上。对于聚簇来说，SIZE是最重要的参数。

下面来看聚簇上的聚簇索引。向聚簇中放数据之前，需要先对聚簇建立索引。可以现在就在聚簇中创建表，但是由于我们想同时创建和填充表，而有数据之前必须有一个聚簇索引，所以我们先来建立聚簇索引。聚簇索引的任务是拿到一个聚簇键值，然后返回包含这个键的块的块地址。实际上这是一个主键，其中每个聚簇键值指向聚簇本身中的一个块。因此，请求部门10的数据时，Oracle会读取聚簇键，确定相庆的块地址，然后读取数据。聚簇键索引如下创建：

sys@ORCL> create index emp_dept_cluster_idx

2 on cluster emp_dept_cluster

3 /

Index created.

对于索引平常有的所有存储参数，聚簇索引都可以有，而且聚簇索引可以存储在另一个表空间中。它就像是一个常规的索引，所以同样可以在多列上建立;聚簇索引只不过恰好是一个聚簇的索引，另外可以包含对应完全null值的条目。注意，在这个CREATE INDEX语句中，并没有指定列的一个列表，索引列可以由CLUSTER定义本身得出。

sys@ORCL> 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 /

Table created.

sys@ORCL> 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 /

Table created.

在此，与“正常”表唯一的区别是，使用了CLUSTER关键字，并告诉Oracle基表的哪个列会映射到聚簇本身的聚簇键。要记住，这里的段是聚簇，因此这个表不会有诸如TABLESPACE、PCTFREE等段属性，它们都是聚簇段的属性，而不是我们所创建的表的属性。现在可以向这些表加载初始数据集：

sys@ORCL> insert into dept

2 (deptno,dname,loc)

3 select deptno+r,dname,loc

4 from scott.dept,

5 (select level r from dual connect by level<10);

36 rows created.

sys@ORCL> insert into emp

2 (empno,ename,job,mgr,hiredate,sal,comm,deptno)

3 select rownum,ename,job,mgr,hiredate,sal,comm,deptno+r

4 from scott.emp,

5 (select level r from dual connect by level<10);

126 rows created.

下面来看它们在磁盘上的组织。使用DBMS_ROWID包分析rowid，并查看数据存储在哪些块上。首先来看DEPT表，看看每块有多少DEPT行：

sys@ORCL> select min(count(*)),max(count(*)),avg(count(*))

2 from dept

3 group by dbms_rowid.rowid_block_number(rowid)

4 /

MIN(COUNT(*)) MAX(COUNT(*)) AVG(COUNT(*))

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

1 76

这样看来，尽管先加载了DEPT——而且DEPT行非常小(正常情况下，一个8KB的块可以放下几百个DEPT行)——但我们发现，这个表中一块上的最大DEPT行数仅为7。这与将SIZE设置为1024时所预期的完全一致。对于一个8KB的块，并且如果对于组合EMP和DEPT记录每个聚簇键的数据为1024字节，可以看到每块大约有7个唯一的聚簇键值，与这里看到的结果正好相同。下面来看EMP和DEPT表。查看各个表的rowid，并比较按DEPTNO联结之后的块数。如果块数相同，说明EMP行和DEPT行都存储在同一个物理数据库块上，如果二者不同，可以知道它们没有存储在相同的块上。所有数据都得到妥善存储。完全没有EMP表中的记录与相应DEPT记录在不同块上存储的情况：

sys@ORCL> select *

2 from (

3 select dept_blk,emp_blk,

4 case when dept_blk <> emp_blk then '*' end flag,

5 deptno

6 from (

7 select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,

8 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,

9 dept.deptno

10 from emp,dept

11 where emp.deptno=dept.deptno

12 )

13 )

14 where flag='*'

15 order by deptno

16 /

no rows selected

这正是我们的目标——要让EMP表中的每一行与相应的DEPT行存储在同一个块上。如果我们估计有误，会发生什么情况?如果1024不够会怎么样?如果有些部门接近1024而另外一些部门超过这个值呢?倘若是这样，显然数据不会在同一块上，必须把某些EMP记录与DEPT记录分不同的块存放。通过重新设置上面的例子就可以很容易地看到这一点。这一次加载时，每个EMP记录加载8次。来乘以每个部门的员工记录数：

sys@ORCL> insert into dept

2 (deptno,dname,loc)

3 select deptno+r,dname,loc

4 from scott.dept,

5 (select level r from dual connect by level<10);

36 rows created.

sys@ORCL> insert into emp

2 (empno,ename,job,mgr,hiredate,sal,comm,deptno)

3 select rownum,ename,job,mgr,hiredate,sal,comm,deptno+r

4 from scott.emp,

5 (select level r from dual connect by level<10),

6 (select level r2 from dual connect by level <8);

882 rows created.

sys@ORCL> select min(count(*)),max(count(*)),avg(count(*))

2 from dept

3 group by dbms_rowid.rowid_block_number(rowid)

4 /

MIN(COUNT(*)) MAX(COUNT(*)) AVG(COUNT(*))

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

1 76

到目前为止都与前面的例子很像，不过下面来比较EMP记录所在的块与DEPT记录所在的块

sys@ORCL> select *

2 from (

3 select dept_blk,emp_blk,

4 case when dept_blk <>emp_blk then '*' end flag,

5 deptno

6 from (

7 select dbms_rowid.rowid_block_number(dept.rowid) dept_blk,

8 dbms_rowid.rowid_block_number(emp.rowid) emp_blk,

9 dept.deptno

10 from emp,dept

11 where emp.deptno=dept.deptno

12 )

13 )

14 where flag='*'

15 order by deptno

16 /

DEPT_BLK EMP_BLK F DEPTNO

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

153881 153886 * 12

153881 153886 * 12

153881 153886 * 12

153881 153886 * 12

153881 153886 * 12

153884 153886 * 17

153885 153887 * 19

153885 153887 * 19

153885 153887 * 19

153885 153887 * 19

153885 153887 * 19

153885 153887 * 19

153881 153886 * 22

153881 153886 * 22

153881 153886 * 22

153881 153886 * 22

153881 153886 * 22

153881 153886 * 22

153881 153886 * 22

153884 153886 * 27

153885 153887 * 29

153885 153887 * 29

153885 153887 * 29

153885 153887 * 29

153885 153887 * 29

153885 153887 * 29

153885 153887 * 29

153885 153887 * 29

153881 153886 * 32

153881 153886 * 32

153881 153886 * 32

153881 153886 * 32

153881 153886 * 32

153881 153886 * 32

153881 153886 * 32

153881 153886 * 32

153885 153886 * 39

153885 153886 * 39

153885 153886 * 39

153885 153886 * 39

153885 153886 * 39

153885 153886 * 39

153885 153886 * 39

153885 153886 * 39

153885 153886 * 39

45 rows selected.

可以看到882行EMP记录中有45行与DEPT表中相应的DEPTNO不在同一个块上。由于我们设置的聚簇过小。

本文来源 我爱IT技术网 http://www.52ij.com/jishu/5287.html 转载请保留链接。