oracle 索引聚簇表

最新推荐文章于 2021-04-05 07:59:43 发布

转载最新推荐文章于 2021-04-05 07:59:43 发布 · 473 阅读

1 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/1862478/blog/1825356

文章标签：

#数据库 #python #运维

2019独角兽企业重金招聘Python工程师标准>>>

聚簇（cluster）是指：如果一组表有一些共同的列，则将这样一组表存储在相同的数据库块中；聚簇还表示把相关的数据存储在同一个块上。利用聚簇，一个块可能包含多个表的数据。从概念上讲，这是将数据“预联结”地存储。聚簇还可以用于单个表，可以按某个列将数据分组存储。例如，部门10的所有员工都存储在同一个块上（或者如果一个块放不下，则存储在尽可能少的几个块上）。聚簇并不是有序地存储数据（这是IOT的工作），它是按每个键以聚簇方式存储数据，但数据存储在堆中。所以，部门100可能挨在部门1旁边，而与部门101和99离得很远（这是指磁盘上的物理位置）。
下面来看如何创建一个聚簇对象。在对象中创建表的一个聚簇很直接。对象的存储定义（PCTFREE、PCTUSED、INITIAL等）与 CLUSTER相关，而不是与表相关。因为聚簇中会有多个表，而且它们在同一个块上。有多个不同的PCTFREE没有意义。因此，CREATE CLUSTER非常类似于只有很少几个列的CREATE TABLE（只有聚簇键列）：

scott@ORCL>create cluster emp_dept_cluster
  2  ( deptno number(2) )
  3  size 1024
  4  /

簇已创建。

我们创建了一个索引聚簇（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会读取聚簇键，确定相应的块地址，然后读取数据。聚簇键索引如下创建：

scott@ORCL>create index emp_dept_cluster_idx
  2  on cluster emp_dept_cluster
  3  /

索引已创建。

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

scott@ORCL>create table dept_bak
  2  ( deptno number(2) primary key,
  3  dname varchar2(14),
  4  loc varchar2(13)
  5  )
  6  cluster emp_dept_cluster(deptno)
  7  /

表已创建。

scott@ORCL>create table emp_bak
  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_bak(deptno)
 10  )
 11  cluster emp_dept_cluster(deptno)
 12  /

表已创建。

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

scott@ORCL>begin
  2     for x in ( select * from scott.dept )
  3     loop
  4             insert into dept_bak
  5                     values ( x.deptno, x.dname, x.loc );
  6             insert into emp_bak
  7                     select *
  8                     from scott.emp
  9                     where deptno = x.deptno;
 10     end loop;
 11  end;
 12  /

PL/SQL 过程已成功完成。

按DEPTNO逐个地加载数据，原因就在于聚簇的设计。我们在模拟一个聚簇的大批量初始加载。如果写加载所有DEPT_BAK行，每个块上就会有7个键（根据前面指定的SIZE 1024设置），这是因为DEPT_BAK行非常小（只有几个字节）。等到加载EMP行时，可能会发现有些部门的数据远远超过了1024字节。这样就会在那些聚簇键块上导致过度的串链。Oracle会把包含这些信息的一组块串链或链接起来。如果同时加载对应一个给定聚簇键的所有数据，就能尽可能紧地塞满块，等空间用完时再开始一个新块。Oracle并不是在每个块中放最多7个聚簇键值，而是会适当地尽可能多地放入聚簇键值。

下面给出一个小例子，从中可以看出这两种方法的区别。我们将向EMP_BAK表增加一个很大的列：CHAR(1000)。加这个列是为了让EMP_BAK行远远大于现在的大小。我们将以两种方式加载聚簇表：先加载DEPT_BAK，再加载EMP_BAK。第二次加载时，则会按部门编号来加载：先是一个DEPT_BAK行，然后是与之相关的所有EMP_BAK行，然后又是一个DEPT_BAK行。我们将查看给定情况下每一行最后在哪个块上，从而得出哪种方法最好，能最好地实现将数据按DEPTNO共同存储的目标。我们的EMP_BAK表如下：

scott@ORCL>create table emp_bak
  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  /

表已创建。

向DEPT和EMP表中加载数据时，可以看到许多EMP行与DEPT行不在同一个块上（DBMS_ROWID）是一个内置包，可以用于查看行ID的内容）：

scott@ORCL>insert into dept_bak
  2  select * from scott.dept;

已创建4行。

scott@ORCL>insert into emp_bak
  2  select emp.*, '*' from scott.emp;

已创建15行。

scott@ORCL>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_bak.rowid) dept_blk,
  6     dbms_rowid.rowid_block_number(emp_bak.rowid) emp_blk,
  7     dept_bak.deptno
  8     from emp_bak, dept_bak
  9     where emp_bak.deptno = dept_bak.deptno
 10     )
 11  order by deptno
 12  /

  DEPT_BLK    EMP_BLK F     DEPTNO
---------- ---------- - ----------
      1751       1753 *         10
      1751       1752 *         10
      1751       1752 *         10
      1751       1753 *         20
      1751       1752 *         20
      1751       1752 *         20
      1751       1751           20
      1751       1751           20
      1751       1752 *         30
      1751       1752 *         30
      1751       1751           30
      1751       1751           30
      1751       1751           30
      1751       1751           30

已选择14行。

一半以上的EMP行与DEPT行不在同一个块上。如果使用聚簇键而不是表键来加载数据，会得到以下结果：

scott@ORCL>begin
  2     for x in ( select * from scott.dept )
  3     loop
  4             insert into dept_bak
  5                     values ( x.deptno, x.dname, x.loc );
  6             insert into emp_bak
  7                     select emp.*, 'x'
  8                     from scott.emp
  9                     where deptno = x.deptno;
 10     end loop;
 11  end;
 12  /

PL/SQL 过程已成功完成。

scott@ORCL>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_bak.rowid) dept_blk,
  6             dbms_rowid.rowid_block_number(emp_bak.rowid) emp_blk,
  7             dept_bak.deptno
  8     from emp_bak, dept_bak
  9     where emp_bak.deptno = dept_bak.deptno
 10     )
 11  order by deptno
 12  /

  DEPT_BLK    EMP_BLK F     DEPTNO
---------- ---------- - ----------
      1753       1753           10
      1753       1753           10
      1753       1753           10
      1753       1754 *         20
      1753       1754 *         20
      1753       1754 *         20
      1753       1754 *         20
      1753       1754 *         20
      1752       1755 *         30
      1752       1755 *         30
      1752       1755 *         30
      1752       1755 *         30
      1752       1755 *         30
      1752       1754 *         30

已选择14行。

如果把对应DEPTNO=n的行放在一个给定块上，然后再加载对应DEPTNO=n的员工行，就能得到最佳的聚簇。

大多数EMP_BAK行都与DEPT_BAK行在同一个块上。如果某些聚簇键超过了估计的SIZE，最后大多数数据都会聚簇到同一个块上。如果一次加载一个表，则做不到这一点。
这种技术只适用于聚簇的初始加载，在此之后，只有在事务认为必要的时候才应使用这个技术。

许多人错误地认为一个rowid能惟一地标识数据库中的一个行，给定一个rowid，就能得出这一行来自哪个表。实际上，这是做不到的。从聚簇可以得到（而且将得到）重复的rowid。例如，执行以上代码后，会发现：

scott@ORCL>select rowid from emp_bak
  2  intersect
  3  select rowid from dept_bak;

ROWID
------------------
AAATgLAADAAAAbZAAA
AAATgLAADAAAAbZAAB

DEPT中为各行分配的每个rowid也同时分配给了EMP中的行。这是因为，要由表和行ID共同地惟一标识一行。Rowid伪列只是在一个表中惟一。

每次你使用Oracle的时候都会使用聚簇。例如，许多数据字典就存储在各个聚簇中：

scott@ORCL>break on cluster_name
scott@ORCL>select cluster_name, table_name
  2  from user_tables
  3  where cluster_name is not null
  4  order by 1;

CLUSTER_NAME                                                 TABLE_NAME
------------------------------------------------------------ -------------------
-----------
EMP_DEPT_CLUSTER                                             DEPT_BAK
                                                             EMP_BAK

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