Oracle Clustered Table

最新推荐文章于 2021-04-14 02:03:12 发布

weixin_34068198

最新推荐文章于 2021-04-14 02:03:12 发布

阅读量121

点赞数

文章标签：数据库数据结构与算法

原文链接：http://blog.51cto.com/suexiukjung/1136785

版权

本文主要介绍Oracle Clustered Table。Oracle集群表分为 B*树索引集群表（B* tree index clustered table）和散列集群表（hash-clustered table）

一 .群（cluster）简介

群为 Oracle中的段，他完成两件事：

u 通过公共键物理的将数据保存在一起。数据不排序；只是通过键物理的存储在一起。如果有一个建立在数值键上的群，具有键值 10的所有数据将物理的保存在一起，如果装的下的话，最的是保存在相同的数据块上。

u 允许将来自多个数据库表的数据存储在相同的物理数据块中。例如，来自 DEPT表的DEPTNO=10的行与来自EMP表的相同DEPTNO的行可能位于相同的块上。可以考虑将这种方法用于数据预连接。

将数据物理的保存在同一位置表示可以在相同数据块上存放来自多表的行。所有表共享一个群键索引这一事实表示减少了索引的需求。（事实上，散列群完全消除了对索引的需求，因为数据就是索引。）

简单的说，群允许限制系统为回答我们的查询需要执行的 I/O量。如果把一起使用的数据放在一起，数据库为回到查询需要执行的物理I/O较少，甚至有时逻辑I/O也较少，但是群存在两个基本限制：

u 不能进行群的直接路径装载。

u 不能分区集群表。

Oracle 数据库字典中的主表是以群 (B*树群)存放的，可以用以下查询查看：

SQL> select cluster_name,owner,table_name from dba_tables

2 where cluster_name is not null

3 order by cluster_name;

CLUSTER_NAME OWNER TABLE_NAME

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

C_COBJ# SYS CDEF$

C_COBJ# SYS CCOL$

… … …

已选择 36行。

C_COBJ#群中有 16个表，这表示16个表中的行可以保存在单个数据块中，全都由他们的公共键预链接。在oracle需要查找一个对象的信息时，单个物理I/O将把16个不同表的信息同时读出。

Oracle支持 B*树群和散列群。这两种群之间的差别在于如何访问数据：

u B*树群使用传统的 B*树索引来存储一个键值和之中可找到数据的块地址。因此，他非常像在表上使用的索引，Oracle将在群索引中查找键值，找到数据所在的块地址，然后在其上面找到数据。

u 散列群使用散列算法将键值转换为数据库块地址，从而跳过处块度曲以为的所有 I/O。对于散列群，数据为自身的索引。这表示存在一种用来执行查找的最佳逻辑I/O。

二 .创建群

创建 B*树群：

SQL> create cluster user_objects_cluster_btree

2 (username varchar2(30))

3 size 1024

4 /

已创建数据簇。

以上语句创建了一个名 USERNAME的VARCHAR2(20)列作为键。这表示添加到此群中的所有表数据将按某个VARCHAR2(30)列物理组织，该列将是一个用户名或者对象拥有者，我们可以将任何形式的VARCHAR2(30)列作为键，但是在一个名为USERNAME的键上创建群，然后再用用户名之外的东西作为键添加表到群中是一种非常坏的习惯。

SIZE参数此处是用来告诉 oracle希望关联到每个群键值的数据大约为1024个字节。Oracle将用他来计算社和每个块的群键的最大数目。假如块的尺寸为8KB，oracle将为每个数据库块准备7个群键，也就是说，最多有7个不同的USERNAME键值的数据可以放到一个块上，一旦插入8个用户名，将会起用一个新块。这里并不表示存储的书库要按用户名排序，只是说与给定用户名相关的数据一般将在相同的块上找到，数据的尺寸和数据插入的顺序将会影响每块能存储的键的数目。

创建群键索引

SQL> create index user_objects_idx

2 on cluster user_objects_cluster_btree

3 /

索引已创建。

以上命令创建一个 B*树索引，插入群的每个唯一键值以及包含该键值的数据的第一块的指针共同在此B*树索引占有一项。这个索引是必选的，因为B*树群的整个概念是建立在将类似数据存储在一起的基础上的。为知道在群中如何存储和读取数据，oracle需要这个索引，他告诉oracle关于KEY=Y的数据位于块Y上。

群索引的任务是去群键值并返回包含该键的块地址。他实际上是一个主键，其中每个群键值指向群本身中的一个块。因此在请求用户名为 SCOTT的数据时，oracle将读取群键索引，确定相应的块地址，然后读取数据。

创建散列群：

SQL> create cluster user_objects_cluster_hash

2 (username varchar2(30))

3 hashkeys 100

4 size 3168

5 /

已创建数据簇。

在这条 CREATE CLUSTER语句中有两个关键成分：HASHKEYS和SIZE。HASHKEYS指出预期的唯一键值的数目。这里设置为100。并不是限制只能有100个不同的用户名，他只是oracle将建立的散列表的尺寸，如果超过该值，必定会在散列表中出现“碰撞”。这并不妨碍工作，但会降低散列表的效能。

SIZE具有与 B*树群样例中相同的含义。这里因为散列群预分配足够的空间来保存HASHKEYS/TRUNC(BLOCKSIZE/SIZE)字节的数据，我们希望更仔细的设置尺寸，例如，如果设置SIZE为1500字节，而块尺寸为4KB，oracle将期望没块存储两个键。如果打算有1000个散列键，oracle将分配500个块。

在群中创建表：

将在 B*树群中创建两个表(对散列群的处理相同)。我们将利用DBA_USERS/DBA_OBJECTS表建立的父/子表：

SQL> create table user_info

2 (username varchar2(30),

3 user_id number,

4 account_status varchar2(32),

5 lock_date date,

6 expiry_date date,

7 default_tablespace varchar2(30),

8 temporary_tablespace varchar2(30),

9 profile varchar2(30))

10 cluster user_objects_cluster_btree(username)

11 /

表已创建

SQL> create table users_objects

2 (owner varchar2(30),

3 object_name varchar2(128),

4 object_id number,

5 object_type varchar2(18),

6 last_ddl_time date,

7 timestamp varchar2(19),

8 status varchar2(7))

9 cluster user_objects_cluster_btree(owner)

10 /

表已创建。

这样就创建了两个空表，第一个表 USER_INFO将使用起名为USERNAME的列来组织自身，而表USERS_OBJECTS使用OWNER列。这表示USER_INFO表中USERNAME值为X的所有数据将进入与USERS_OBJECTS表的OWNER=X的所有数据相同的位置。

三 .使用群（cluster）

将数据装载到表能说明应用程序中群的潜在效果的一个重要方面。群的其他有用方面还包括减少 I/O，增加缓存区告诉缓存的效率，消除索引块以及创建只读查询表等。

SQL> insert

2 when (r=1) then

3 into user_info

4 (username,user_id,account_status,lock_date,

5 expiry_date,default_tablespace,

6 temporary_tablespace,profile)

7 values

8 (username,user_id,account_status,lock_date,

9 expiry_date,default_tablespace,

10 emporary_tablespace,profile)

11 when (1=1) then

12 into users_objects

13 (owner,object_name,object_id,object_type,

14 last_ddl_time,timestamp,status)

15 values

16 (owner,object_name,object_id,object_type,

17 last_ddl_time,timestamp,status)

18 select dba_users.username,dba_users.user_id,

19 dba_users.account_status,dba_users.lock_date,

20 dba_users.expiry_date,dba_users.default_tablespace,

21 dba_users.temporary_tablespace,dba_users.created,

22 dba_users.profile,dba_objects.owner,

23 dba_objects.object_name,dba_objects.object_id,

24 dba_objects.object_type,dba_objects.created,

25 dba_objects.last_ddl_time,dba_objects.timestamp,

26 dba_objects.status,

27 row_number() over (partition by owner order by object_id) r

28 from dba_users,dba_objects

29 where dba_users.username=dba_objects.owner

30 and dba_users.username<>'SYS'

31* /

已创建 2793行。

使用如上语句插入数据是为了达到按照键集群数据的目的，在创建 B*树群时使用了如下代码：

SQL> create cluster user_objects_cluster_btree

2 (username varchar2(30))

3 size 1024

4 /

他告诉 oracle与每个键值有关的数据大约为1KB，因此每块存储最多7个 username键，或者更少。如果首先插入来自DBA_USER的数据到这个表中，每个用户将有一行，数据量较小，很容易达到每块7个用户名键。但是在系统上，DBA_OBJECTS数据尺寸变化很大，每个键的数据在0.5K到139K之间，因此在插入该数据时，要求的数据集群达不到。对于每块7个键并不是每次都有足够的空间。对于某些USERNAME值，每块一个键。我们的目的是把这些信息储存在一起，为了做到这一点，需要一起装载数据，这是理解群以及为什么将他们用于oracle数据字典一类的东西的重要内容。

在具体的数据字典中，有一个 CLUSTER C_OBJ#对象，他包含CLU$(群信息)，COL$(列信息)，TAB$(表信息)，IND$(索引信息)，ICOL$(索引列信息)等一类的表。在建立一个表及相关信息时，oracle在大约相同的时间填充这些对象，一般来说，信息量(行数)在增加或者减少不是很快，因为并不经常删除或增加列，约束的数目一般是固定的。这里，与一个对象有关的所有信息一次性构造(在对象创建时)，而且一起进入相同的块或者多个块。

通过同时装载一个给定群键的所有数据，我们尽可能紧密的将块组合在在一起，当空间用完时才起用新块。 Oracle不是没块放置最多7个键值，而是尽可能的放置群键值。

为了理解其作用，首先看一下通过同时装载数据所达到的数据紧密度，我们将观察数据的 ROWID并按文件和块分解行。这样很快就能理解数据如何组织。执行如下语句：

SQL> select owner,arfno||'.'||ablock arowid,

2 brfno||'.'||bblock browid,count(*)

3 from (

4 select b.owner,

5 dbms_rowid.rowid_relative_fno(a.rowid) arfno,

6 dbms_rowid.rowid_block_number(a.rowid) ablock,

7 dbms_rowid.rowid_relative_fno(b.rowid) brfno,

8 dbms_rowid.rowid_block_number(b.rowid) bblock

9 from user_info a,users_objects b

10 where a.username=b.owner

11 )

12 group by owner,arfno,ablock,brfno,bblock

13 order by owner,arfno,ablock,bblock

得到以下数据并分析：

OWNER AROWID BROWID COUNT(*)

AURORA$JIS$UTILITY$ 1.61725 1.61725 50

1.61725 1.61726 22

. . . .

PM 1.61982 1.61982 26

QS 1.61983 1.61983 44

这表示对于 OWNER=QS，USER_INFO中的行位于文件1，块61983上。而对于OWNER=QS,USERS_OBJECTS中的行也位于文件1，块61983上。每个表的各行物理的存储在相同的数据块上。来自USER_INFO的单个行与来自USERS_OBJECTS的44行共同存储在块61983上。这些数据从效果上看是预先连接的

. . . .

SYSTEM 1.61993 1.61993 50

1.61993 1.61994 52

1.61993 1.61995 51

1.61993 1.61996 50

1.61993 1.61997 48

1.61993 1.61998 46

1.61993 1.61999 47

1.61993 1.62000 22

这表示对于 OWNER=SYSTEM，USER_INFO中的行位于文件1，块61993上。而对于OWNER=SYSTEM，USERS_OBJECTS中的行也位于文件1，但分布在61993——62000块上。也就是说在USERS_OBJECTS中OWNER=SYSTEM的对象有366个，这么多的数据单个块无法装下，但是oracle依然很好的把各行很好的集群在一起，按照群键把他们放在尽可能少的块中。

WKSYS 1.62000 1.62000 24

已选择 78行。

如果我们以如下考虑的不太仔细的方式装载数据：

SQL> insert into user_info

2 (username,user_id,account_status,lock_date,expiry_date,

3 default_tablespace,temporary_tablespace,profile)

4 select a.username,a.user_id,a.account_status,a.lock_date,

5 a.expiry_date,a.default_tablespace,a.temporary_tablespace,

6 a.profile from dba_users a

7 where a.username<>'SYS'

8 /

已创建 28行。

SQL> insert into users_objects

2 (owner,object_name,object_id,object_type,last_ddl_time,

3 timestamp,status)

4 select b.owner,b.object_name,b.object_id,b.object_type,

5 b.last_ddl_time,b.timestamp,b.status

6 from dba_users a,dba_objects b

7 where a.username=b.owner

8 and a.username<>'SYS'

9 order by object_type,object_name

10 /

已创建 2769行。

可以证明，我们具有相同的数据，但是物理组织很不相同。 Oracle首先接受USER_INFO的数据，这些数据非常少，因此oracle平均每块放置7个键值。然后接收USERS_OBJECTS的数据，USERS_OBJECTS的数据以一种保证OWNER了不以排序顺序出现的方式排序，这些数据是拼凑起来的，现在，如果查看数据如何放置，就会发现：

OWNER AROWID BROWID COUNT(*)

QS 1.61983 1.61983 44

QS 1.61901 1.61900 3

QS 1.61901 1.61901 41

可以看到 QS的数据分散在两个不同的块上，不像以前一样44行都存储在同一个块上。

SYSTEM 1.61993 1.61993 50

1.61993 1.61994 52

1.61993 1.61995 51

1.61993 1.61996 50

1.61993 1.61997 48

1.61993 1.61998 46

1.61993 1.61999 47

1.61993 1.62000 22

SYSTEM 1.61918 1.61910 9

1.61918 1.61911 16

1.61918 1.61912 48

1.61918 1.61913 51

1.61918 1.61914 47

1.61918 1.61915 46

1.61918 1.61916 51

1.61918 1.61917 46

1.61918 1.61918 49

1.61918 1.61922 3

USERS_OBJECTS中USERNAME=SYSTEM的数据分散在10个不连续的块上，不像以前一样分布在八个连续的块上。

从这个例子中的出的结论为，群在我们能控制怎么装载数据的环境中最高效。例如，群适合于数据仓库型的操作，其中随时要重装数据，从而可以控制怎样装载数据以达到最大集群。如果经常在大致相同的时间插入父记录和大量子记录，类似于 oracle处理CREATE TABLE语句时所完成的工作，B*树群还适合更新(事务处理)系统。数据字典中大多数父行和子行是在大致相同的时间内插入的，如果数据多数时间是随机（不按群键次序）到达的，则B*树群就不是那么适合。

四.B*树群可降低I/O并提高缓冲区高速缓存的效率

缓冲区高速缓存效率并一定指高速缓存命中率，更确切的说，通过存储相关信息在相同块上，可提高缓冲区高速缓存的效率，为响应我们的查询，缓冲区高速缓存可能只需要处理一两个数据块，而不是处理 50个数据块，这可能不是更好的高速缓存命中率的证据（虽然有可能是），而且这也不是重点。高高速缓存命中率本身并不表示系统执行的很好，降低了的逻辑计数和达到相同告诉缓存命中率所需缓冲告诉缓存较少SGA将表示具有良好的性能。

现在准备测试 B*树的效能，为了完成工作，我们将利用SQL_TRACE和TKPROF，相对于等价的堆表评估组合良好的群的性能。

需要观察的东西之一是相对于堆表方法，群实现的物理 I/O多了多少，或者是少了多少。因此要做的第一件事情就是刷新与我们的群存储的表空间有关的所有数据的高速缓存：

SQL> alter tablespace users offline;

表空间已更改。

SQL> alter tablespace users online;

表空间已更改。

现在对于基准代码本身，我们将一个群键接一个群键的从两个表中检索所有数据。总共做 10次，让他一次又一次的执行许多遍就可以了。

SQL> alter session set sql_trace=true;

会话已更改。

SQL> begin

2 for x in (select username from all_users)

3 loop

4 for i in 1 .. 10

5 loop

6 for y in (select a.username,a.temporary_tablespace,

7 b.object_name,b.object_type /* cluster */

8 from user_info a,users_objects b

9 where a.username=b.owner

10 and a.username=X.USERNAME)

11 loop

12 null;

13 end loop;

14 end loop;

15 end loop;

16 end;

17 /

PL/SQL 过程已成功完成。

SQL> alter session set sql_trace=false;

会话已更改。

现在得到了 B*树群的报表，下一步需要按传统堆表重建这两个表。使用如下语句建立，并各增加一个索引：

SQL> create table user_info_heap

2 (username varchar2(30),

3 user_id number,

4 account_status varchar2(32),

5 lock_date date,

6 expiry_date date,

7 default_tablespace varchar2(30),

8 temporary_tablespace varchar2(30),

9 profile varchar2(30))

10 /

Table created

SQL>create index user_info_username_idx

2 on user_info_heap(username);

Index created

SQL> create table users_objects_heap

2 (owner varchar2(30),

3 object_name varchar2(128),

4 object_id number,

5 object_type varchar2(18),

6 last_ddl_time date,

7 timestamp varchar2(19),

8 status varchar2(7))

9 /

Table created

SQL> create index users_objects_owner_idx

2 on users_objects_heap(owner);

Index created

在堆表上，我们使用A.USERNAME=bond_variable并连接USERNAME到OWNER。使用相同的多表INSERT语句装载这两个表，并且执行相同的基准测试代码块，在查询中/*CLUSTER*/的位置使用/*HEAP*/。

SQL> alter session set sql_trace=true;

会话已更改。

SQL> begin

2 for x in (select username from all_users)

3 loop

4 for i in 1 .. 10

5 loop

6 for y in (select a.username,a.temporary_tablespace,

7 b.object_name,b.object_type /* heap */

8 from user_info a,users_objects b

9 where a.username=b.owner

10 and a.username=X.USERNAME)

11 loop

12 null;

13 end loop;

14 end loop;

15 end loop;

16 end;

17 /

PL/SQL 过程已成功完成。

SQL> alter session set sql_trace=false;

会话已更改。

现在查看 TKPROF报表：

*******************************************************************************

SELECT A.USERNAME,A.TEMPORARY_TABLESPACE, B.OBJECT_NAME,B.OBJECT_TYPE

FROM

USER_INFO A,USERS_OBJECTS B WHERE A.USERNAME=B.OWNER AND A.USERNAME=:B1

call count cpu elapsed disk query current rows

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

Parse 1 0.01 0.00 0 0 0 0

Execute 210 0.00 0.05 1 2 0 0

Fetch 830 0.20 0.15 1 2870 0 68610

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

total 1041 0.21 0.21 2 2872 0 68610

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

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

68610 NESTED LOOPS (cr=2870 pr=1 pw=0 time=522646 us)

160 TABLE ACCESS CLUSTER USER_INFO (cr=1230 pr=1 pw=0 time=15521 us)

160 INDEX UNIQUE SCAN USER_OBJECTS_IDX (cr=210 pr=1 pw=0 time=8796 us)(object id 51345)

68610 TABLE ACCESS CLUSTER USERS_OBJECTS (cr=1640 pr=0 pw=0 time=216309 us)

*******************************************************************************

SELECT A.USERNAME,A.TEMPORARY_TABLESPACE, B.OBJECT_NAME,B.OBJECT_TYPE

FROM

USER_INFO_HEAP A,USERS_OBJECTS_HEAP B WHERE A.USERNAME=B.OWNER AND

A.USERNAME=:B1

call count cpu elapsed disk query current rows

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

Parse 1 0.01 0.00 0 0 0 0

Execute 210 0.03 0.19 4 4 0 0

Fetch 830 0.20 0.22 16 3340 0 68610

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

total 1041 0.25 0.42 20 3344 0 68610

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

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

68610 TABLE ACCESS BY INDEX ROWID USERS_OBJECTS_HEAP (cr=3340 pr=16 pw=0 time=866784 us)

68980 NESTED LOOPS (cr=1810 pr=15 pw=0 time=1179177 us)

160 TABLE ACCESS FULL USER_INFO_HEAP (cr=720 pr=0 pw=0 time=9924 us)

68610 INDEX RANGE SCAN USER_OBJECTS_OWNER_IDX (cr=1090 pr=15 pw=0 time=213493 us)(object id 51352)

*******************************************************************************

这里的 B*树进行非常少的逻辑I/O（QUERY列）和非常少的物理I/O。完成相同任务需要从磁盘读入的块较少。如果把这个例子放大到数十万或者数百万条记录，将会得到减少物理I/O并提高缓冲区高速缓存效率的好处。

不需要从多个位置读取两个或者三个索引结构和块，只需要读取一个索引结构（群键），在最好的情况下，仅需将一个数据块（这个块包含来自所有表的所需的所有行）物理的读入高速缓存即可。并且只需高速缓存较少的索引块和表块。而传统表查询需要来自两个索引的索引块（比方说至少每个索引 3个块）和来自两个表的表块（比方说每个表至少1块），大约为告诉缓存的8个块。通过把一起访问的相关数据存储在一起，或许能够在高速缓存中存放两倍那么多的数据。

五.散列群不需要索引块

如果我们用散列群而不是 B*树群处理前面的例子，则群查询的TKPROF报表显示如下：

*******************************************************************************

SELECT A.USERNAME,A.TEMPORARY_TABLESPACE, B.OBJECT_NAME,B.OBJECT_TYPE

FROM

USER_INFO_HASH A,USERS_OBJECTS_HASH B WHERE A.USERNAME=B.OWNER AND

A.USERNAME=:B1

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 210 0.04 0.20 1 6 0 0

Fetch 830 0.12 0.15 1 2740 0 68610

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

total 1041 0.17 0.36 2 2746 0 68610

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

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

68610 NESTED LOOPS (cr=2740 pr=1 pw=0 time=521025 us)

160 TABLE ACCESS HASH USER_INFO_HASH (cr=1150 pr=1 pw=0 time=18210 us)

68610 TABLE ACCESS HASH USERS_OBJECTS_HASH (cr=1590 pr=0 pw=0 time=211360 us)

*******************************************************************************

B*树群和传统表的合计分别为：

call count cpu elapsed disk query current rows

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

total 1041 0.21 0.21 2 2872 0 68610

total 1041 0.25 0.42 20 3344 0 68610

可看出，他与 B*树群等相同。不过，其数据实际上就是索引。oracle为找到数据不扫描任何索引。他取通过绑定变量：b1传入查询的USERNAME值，把他散列成块地址，然后再转到该块。

如果说 B*树索引能通过将类似数据存储在一起从而提高缓冲区高速缓存的利用率，则散列群通过完全取消索引块又更进了一步，可以将更多的相关数据放入缓冲区，因为相关的数据更少了。

六.单表散列群与只读查找表（read only）表

单表散列群是群的一种特殊案例，任何时候群中只可能存在一个表。 oracle能够优化对这个表的访问，因为他确切的知道哪些块包含了一个表的数据。从而容易处理这些块，因为oracle不再需要记忆哪些行来自哪些表的开销。

单表散列群在何处有用？几乎在任何地方都需要进行查找（把一个代码转换为一个值）。一般，每个查找都要涉及到三个索引访问，后跟一个按 ROWID进行的表访问。利用单表散列群，这三到四个逻辑I/O可以转换为单个逻辑I/O。

为说明其使用，我们将建立一个小测试案例。我们再次使用 DBA_OBJECTS，并且希望在单表散列群中放入大约50000个查找项，如下创建群：

SQL> create cluster object_id_lookup

2 (object_id number)

3 single table

4 hashkeys 50000

5 size 100

6 /

簇已创建。

其中使用关键字 SINGLE TABLE向oracle指出我们想使用这种特殊的表，此外，用HASHKEYS 50000指出所要的散列表尺寸。我们知道打算放入这个散列表的数据行平均大约为100个字节，所以使用了SIZE 100。

现在准备创建一个单表散列群表以及一个堆查找表，以便了解进行 I/O时他们之间的差异。为此目的，给每个表填充50000行，用UNION ALL使用DBA——OBJECTS两次，因为我们的DBA_OBJECTS视图大约有35000行，的出的行不止50000行，所以使用ROWNUM生成希望测试的50000行：

SQL> create table single_table_hash_cluster

2 (owner,object_name,object_id,object_type,last_ddl_time,

3 timestamp,status)

4 cluster object_id_lookup(object_id)

5 as

6 select owner,object_name,rownum,

7 object_type,last_ddl_time,timestamp,status

8 from (select * from dba_objects

9 union all

10 select * from dba_objects)

11 where rownum <=50000

12 /

表已创建。

SQL> create table heap_table

2 (owner,object_name,object_id,object_type,last_ddl_time,timestamp,status,

3 constraint heap_table_pk primary key(object_id))

4 as

5 select owner,object_name,rownum,

6 object_type,last_ddl_time,timestamp,status

7 from (select * from dba_objects

8 union all

9 select * from dba_objects)

10 where rownum<=50000

11 /

表已创建。

为比较良种实现的运行时间特性，分别查询两个表的每个记录 3次。因此，在循环中针对每个表进行了150000次查找：

SQL> declare

2 l_rec single_table_hash_cluster%rowtype;

3 begin

4 for iters in 1 .. 3

5 loop

6 for i in 1 .. 50000

7 loop

8 select * into l_rec

9 from single_table_hash_cluster

10 where object_id=i;

11 select * into l_rec

12 from heap_table

13 where object_id=i;

14 end loop;

15 end loop;

16 end;

17 /

PL/SQL 过程已成功完成。

在查看这些运行的 TKPROF报表时，看到以下内容：

*******************************************************************************

SELECT *

FROM

SINGLE_TABLE_HASH_CLUSTER WHERE OBJECT_ID=:B1

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 150000 5.37 5.02 0 3 0 0

Fetch 150000 5.45 5.26 0 170427 0 150000

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

total 300001 10.82 10.29 0 170430 0 150000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

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

150000 TABLE ACCESS HASH SINGLE_TABLE_HASH_CLUSTER (cr=170427 pr=0 pw=0 time=4433700 us)

*******************************************************************************

SELECT *

FROM

HEAP_TABLE WHERE OBJECT_ID=:B1

call count cpu elapsed disk query current rows

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

Parse 1 0.00 0.00 0 0 0 0

Execute 150000 6.28 6.56 0 0 0 0

Fetch 150000 6.70 6.41 0 450000 0 150000

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

total 300001 12.98 12.98 0 450000 0 150000

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS (recursive depth: 1)

Rows Row Source Operation

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

150000 TABLE ACCESS BY INDEX ROWID HEAP_TABLE (cr=450000 pr=0 pw=0 time=6773104 us)

150000 INDEX UNIQUE SCAN HEAP_TABLE_PK (cr=300000 pr=0 pw=0 time=3347494 us)(object id 51369)

*******************************************************************************

注意，运行时间（ CPU列）大致相同，如果再执行这个测试，将会发现CPU时间的平均数基本相同。执行，分析，取数和行的数目是相等的。给出相同的输入，这两个表给出相同的输出。差别在于I/O之上，QUERY列（一致方式取）的值差别很大。

针对单表散列群的查询的逻辑 I/O为传统堆表的42%。这里，运行时间的差异并没有显示出来（他们似乎是相等的）“栓锁是锁，锁为串行设施，串行设施极大的阻止并发性和可伸缩性”。在多用户环境中，只要能够减少执行的栓锁量，必然会提高可伸缩性和性能。如果上述测试中使用的不是SQL_TRACE和TKPROF而是runstats,会发现单表散列群使用大约85%的高速缓存缓冲区链栓锁（用来保护缓冲区高速缓存的栓琐；我们很少单独使用他们，因为我们使用的缓冲区高速缓存较少）。

对于具有只读或主要是查找表的应用程序，或者对于按键值读取一个表的数量远远大于修改的应用程序，单表散列群在简单的执行与超过性能需求之间可能存在很大的差别。

七.群小结

B*树群也具有正反两个方面。

u 物理的集中放置数据。

u 提高缓冲区高速缓存效率。

u 减少逻辑 I/O。

u 减少索引需求，因为所有表共享一个群键索引。

但是， B*树群也确实受到某些限制。除了不能进行直接路径装载或分区外，他们还有下列缺点：

u 正确设置群尺寸需要仔细考虑，设置 SIZE值过大会浪费空间，设置SIZE值过小不能体现出群的主要好处（物理集群数据）。为了纠正尺寸设置不正确的群，需要重建该群。

u 必须控制或可控制对群的插入，否则集群效果会变坏。

u 集群表的插入比传统表慢，以为数据需要他必须进入的位置。堆表可以只在任何放置数据的地方添加数据；不需要努力将数据保持在某个特定的位置。这并不是说群表不适合读 /写应用程序。oracle本身的数据字典就是最好的事例。

所以群基表可用在多种应用程序之中，特别在理解他们的好处和限制之后，应用群集表会更加自如。有许多表不进行直接路径装载或者分区，而且能够控制插入模式，对于这些对象，可考虑使用群