oracle 散列聚簇表

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

散列聚簇表（Hash clustered table） 在概念上与索引聚簇表非常相似，只有一个主要区别：聚簇键索引被一个散列函数所取代。表中的数据就是索引；这里没有物理索引。Oracle会取得一行的键值，使用每个内部函数或者你提供的每个函数对其计算散列，然后使用这个散列值得出数据应该在磁盘上的哪个位置。不过，使用散列算法来定位数据有 一个副作用，如果不向表增加一个传统的索引，将无法对散列聚簇中的表完成区间扫描。在一个索引聚簇中，如果有以下查询：

select * from emp where deptno between 10 and 20

它就能利用聚簇键索引来找到这些行。在一个散列聚簇中，这个查询会导致一个全表扫描，除非DEPTNO列上已经有一个索引。如果没有使用一个支持区间扫描的索引，就只能在散列键上完成精确搜索（包括列表和子查询）。

理想情况下，散列键值均匀分布，并且有一个散列函数可以将这些散列键值均匀地分布到为散列聚簇分配的所有块上，从查询利用一个I/O就能直接找到数据。但在实际中，最后可能会有多个散列键值散列到同一个数据库块地址，而且这个块上放不下这么多散列键值。这就会导致块串链，Oracle必须用一个链表把块串起来，来保存散列到这个块的所有行。现在，当需要获取与某个散列键匹配的行时，可能必须访问多个块。
数据库中的散列表有固定的“大小”。创建表时，必须确定这个表中将有多少个散列键（而且这个数永远不变）。但散列表的大小并不限制其中能放的行数。

创建散列聚簇时，还是使用 CREATE CLUSTER语句，不过选项不同。这里只是要增加一个HASHKEYS选项来指定散列表的大小。Oracle得到你的HASHKEYS值，将其“舍入” 为与之最接近的质数（散列键数总是一个质数）。然后Oracle再将SIZE参数乘以修改后的HASHKEYS值，计算出一个值。再根据这个值为聚簇分配空间，也就是说，至少要分配这么多字节的空间。这与索引聚簇有很大差别，索引聚簇会在需要时动态地分配空间，散列聚簇则要预留足够的空间来保存（HASHKEYS/trunc(blocksize/SIZE)）字节的数据。例如，如果将SIZE设置为1,500字节，而且块大小为4KB， Oracle会在每个块上存储两个键。如果你计划有1,000个HASHKEY，Oracle就分配500个块。
这里允许有散列冲突，实际上，许多情况下还需要有冲突。还是用前面的DEPT/EMP例子，可以根据 DEPTNO列建立一个散列聚簇。显然，多个行会散列到同一个值，这正是你希望的（因为它们有相同的DEPTNO）。这就反映了聚簇某些方面的特点：要把类似的数据聚簇在一起。正是由于这个原因，所以Oracle要求你指定HASHKEY（你预计一段时间会有多少个部门号）和SIZE（与各个部门号相关联 的数据量）。Oracle会分配一个散列表来保存HASHKEY个部门，每个部门有SIZE字节的数据。你想避免的是无意的散列冲突。如果就散列表的大小设置为1,000（实际上是1,099，因为散列表的大小总是质数，而且Oracle会为你找出与之最接近的质数），而你在表中放入了 1,010个部门，就至少会存在一个冲突（两个不同部门散列到同一个值）。无意的散列冲突是要避免的，因为它们会增加开销，使块串链的可能性增加。
要 查看散列聚簇会用哪种空间，下面使用 存储过程SHOW_SPACE。这个例程只是使用DBMS_SPACE提供的包来得到数据库中段所用存储空间的详细信息。

创建show_space ：

scott@ORCL>create or replace procedure show_space
  2  ( p_segname_1 in varchar2,
  3  p_owner_1 in varchar2 default user,
  4  p_type_1 in varchar2 default 'TABLE',
  5  p_space in varchar2 default 'AUTO',
  6  p_analyzed in varchar2 default 'Y'
  7  )
  8  as
  9  p_segname varchar2(100);
 10  p_type varchar2(10);
 11  p_owner varchar2(30);
 12
 13  l_unformatted_blocks number;
 14  l_unformatted_bytes number;
 15  l_fs1_blocks number;
 16  l_fs1_bytes number;
 17  l_fs2_blocks number;
 18  l_fs2_bytes number;
 19  l_fs3_blocks number;
 20  l_fs3_bytes number;
 21  l_fs4_blocks number;
 22  l_fs4_bytes number;
 23  l_full_blocks number;
 24  l_full_bytes number;
 25
 26  l_free_blks number;
 27  l_total_blocks number;
 28  l_total_bytes number;
 29  l_unused_blocks number;
 30  l_unused_bytes number;
 31  l_LastUsedExtFileId number;
 32  l_LastUsedExtBlockId number;
 33  l_LAST_USED_BLOCK number;
 34
 35  procedure p( p_label in varchar2, p_num in number )
 36  is
 37  begin
 38  dbms_output.put_line( rpad(p_label,40,'.') ||
 39  p_num );
 40  end;
 41  begin
 42  p_segname := upper(p_segname_1); -- rainy changed
 43  p_owner := upper(p_owner_1);
 44  p_type := p_type_1;
 45
 46  if (p_type_1 = 'i' or p_type_1 = 'I') then --rainy changed
 47  p_type := 'INDEX';
 48  end if;
 49
 50  if (p_type_1 = 't' or p_type_1 = 'T') then --rainy changed
 51  p_type := 'TABLE';
 52  end if;
 53
 54  if (p_type_1 = 'c' or p_type_1 = 'C') then --rainy changed
 55  p_type := 'CLUSTER';
 56  end if;
 57
 58
 59  dbms_space.unused_space
 60  ( segment_owner => p_owner,
 61  segment_name => p_segname,
 62  segment_type => p_type,
 63  total_blocks => l_total_blocks,
 64  total_bytes => l_total_bytes,
 65  unused_blocks => l_unused_blocks,
 66  unused_bytes => l_unused_bytes,
 67  LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 68  LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 69  LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 70
 71  if p_space = 'MANUAL' or (p_space <> 'auto' and p_space <> 'AUTO') then
 72  dbms_space.free_blocks
 73  ( segment_owner => p_owner,
 74  segment_name => p_segname,
 75  segment_type => p_type,
 76  freelist_group_id => 0,
 77  free_blks => l_free_blks );
 78
 79  p( 'Free Blocks', l_free_blks );
 80  end if;
 81
 82  p( 'Total Blocks', l_total_blocks );
 83  p( 'Total Bytes', l_total_bytes );
 84  p( 'Unused Blocks', l_unused_blocks );
 85  p( 'Unused Bytes', l_unused_bytes );
 86  p( 'Last Used Ext FileId', l_LastUsedExtFileId );
 87  p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
 88  p( 'Last Used Block', l_LAST_USED_BLOCK );
 89
 90
 91  /*IF the segment is analyzed */
 92  if p_analyzed = 'Y' then
 93  dbms_space.space_usage(segment_owner => p_owner ,
 94  segment_name => p_segname ,
 95  segment_type => p_type ,
 96  unformatted_blocks => l_unformatted_blocks ,
 97  unformatted_bytes => l_unformatted_bytes,
 98  fs1_blocks => l_fs1_blocks,
 99  fs1_bytes => l_fs1_bytes ,
100  fs2_blocks => l_fs2_blocks,
101  fs2_bytes => l_fs2_bytes,
102  fs3_blocks => l_fs3_blocks ,
103  fs3_bytes => l_fs3_bytes,
104  fs4_blocks => l_fs4_blocks,
105  fs4_bytes => l_fs4_bytes,
106  full_blocks => l_full_blocks,
107  full_bytes => l_full_bytes);
108  dbms_output.put_line(rpad(' ',50,'*'));
109  dbms_output.put_line('The segment is analyzed');
110  p( '0% -- 25% free space blocks', l_fs1_blocks);
111  p( '0% -- 25% free space bytes', l_fs1_bytes);
112  p( '25% -- 50% free space blocks', l_fs2_blocks);
113  p( '25% -- 50% free space bytes', l_fs2_bytes);
114  p( '50% -- 75% free space blocks', l_fs3_blocks);
115  p( '50% -- 75% free space bytes', l_fs3_bytes);
116  p( '75% -- 100% free space blocks', l_fs4_blocks);
117  p( '75% -- 100% free space bytes', l_fs4_bytes);
118  p( 'Unused Blocks', l_unformatted_blocks );
119  p( 'Unused Bytes', l_unformatted_bytes );
120  p( 'Total Blocks', l_full_blocks);
121  p( 'Total bytes', l_full_bytes);
122
123  end if;
124
125  end;
126  /

过程已创建。

scott@ORCL>create cluster hash_cluster
  2  ( hash_key number )
  3  hashkeys 1000
  4  size 8192
  5  tablespace mssm
  6  /

簇已创建。

scott@ORCL>exec show_space( 'HASH_CLUSTER', user, 'CLUSTER' );
Total Blocks............................1152
Total Bytes.............................9437184
Unused Blocks...........................117
Unused Bytes............................958464
Last Used Ext FileId....................8
Last Used Ext BlockId...................15488
Last Used Block.........................11
*************************************************
The segment is analyzed
0% -- 25% free space blocks.............0
0% -- 25% free space bytes..............0
25% -- 50% free space blocks............0
25% -- 50% free space bytes.............0
50% -- 75% free space blocks............0
50% -- 75% free space bytes.............0
75% -- 100% free space blocks...........0
75% -- 100% free space bytes............0
Unused Blocks...........................0
Unused Bytes............................0
Total Blocks............................1009
Total bytes.............................8265728

PL/SQL 过程已成功完成。

可 以看到，为表分配的总块数为1152。其中117个块未用（空闲）。另外有1个块用于维护表开销，以管理区段。因此，有1152个块在这个对象的HWM 之下，这些是聚簇使用的块。由于块大小为8KB，可以看到，Oracle实际上会分配 （8,192×1152）字节。由于区段的”舍入“而且/或者通过使用本地管理的表空间（区段的大小一致），实际分配的空间（9,437,184）比这个数稍高一些。

关于散列聚簇需要注意以下问题。一般地，如果创建一个空表，该表在HWM下的块数为0.如果对它执行全表扫描，达到HWM就会停止。对于一个散列聚簇，表一开始就很大，需要花更长的时间创建，因为Oracle必须初始化各个块（而对于一般的表，这个动作通常在数据增加到表时才发生）。散列聚簇 表有可能把数据放在第一个块和最后一个块中，而中间什么都没有。对一个几乎为空的散列聚簇进行前面扫描与全面扫描一个满的散列聚簇所花的时间是一样的。这 不一定是件坏事：建立散列聚簇的本来目的是为了根据散列键查找从而非常快地访问数据。而不是为了频繁地对它进行全面扫描。

现在可以开始把表放在散列聚簇中，仍采用前面索引聚簇所用的方法：

scott@ORCL>create table hashed_table
  2  ( x number, data1 varchar2(4000), data2 varchar2(4000) )
  3  cluster hash_cluster(x);

表已创建。

为 了看出散列聚簇可能有哪些区别，建立了一个小测试。首先创建一个散列聚簇，在其中加载一些数据，再将这些数据复制到一个有传统索引的“常规“表中，然后 对各个表完成一些随机读（对每个完成的随机读是一样的）。通过使用runstats、SQL_TRACE和TKPPOF，可以确定各个表的特征。以下先完 成散列聚簇和表的建立，其后是分析：

scott@ORCL>create cluster hash_cluster
  2  ( hash_key number )
  3  hashkeys 75000
  4  size 150
  5  /

簇已创建。

scott@ORCL>create table t_hashed
  2  cluster hash_cluster(object_id)
  3  as
  4  select *
  5  from all_objects
  6  /

表已创建。

scott@ORCL>alter table t_hashed add constraint
  2  t_hashed_pk primary key(object_id)
  3  /

表已更改。

scott@ORCL>begin
  2  dbms_stats.gather_table_stats( user, 'T_HASHED', cascade=>true );
  3  end;
  4  /

PL/SQL 过程已成功完成。

在此创建了一个SIZE为150字节的散列聚簇。这是因为，我认为我的表中一行的平均大小大约是100字节，但是根据实际数据，具体的行大小可能会上下浮动。然后在这个聚簇中创建并填充一个表，作为ALL_OBJECTS的一个副本。

接下来，创建这个表的传统版本的“克隆“（即相应的堆组织表）：

scott@ORCL>create table t_heap
  2  as
  3  select *
  4  from t_hashed
  5  /

表已创建。

scott@ORCL>alter table t_heap add constraint
  2  t_heap_pk primary key(object_id)
  3  /

表已更改。

scott@ORCL>begin
  2  dbms_stats.gather_table_stats( user, 'T_HEAP', cascade=>true );
  3  end;
  4  /

PL/SQL 过程已成功完成。

现 在，我需要一些“随机“的数据，用来从各个表中抽取行。为此，我只是把所有OBJECT_ID选择到一个数组中，然后随机地排序，从而以一种分散的方式命 中表的各个块。我使用了一个PL/SQL包来定义和声明这个数组，并使用一些PL/SQL代码来”准备“这个数组，填入随机数据：

scott@ORCL>create or replace package state_pkg
  2  as
  3  type array is table of t_hashed.object_id%type;
  4  g_data array;
  5  end;
  6  /

程序包已创建。

scott@ORCL>begin
  2  select object_id bulk collect into state_pkg.g_data
  3  from t_hashed
  4  order by dbms_random.random;
  5  end;
  6  /

PL/SQL 过程已成功完成。

要看到各个表完成的工作，我使用了以下代码块（如果把这里出现的HASHED都代之以HEAP，就可以得到另一个要测试的代码块）：

scott@ORCL>declare
  2  l_rec t_hashed%rowtype;
  3  begin
  4  for i in 1 .. state_pkg.g_data.count
  5  loop
  6  select * into l_rec from t_hashed
  7  where object_id = state_pkg.g_data(i);
  8  end loop;
  9  end;
 10  /

PL/SQL 过程已成功完成。

接 下来，就前面的代码块（以及用HEAP取代HASHED得到的代码块）运行3次。第一次运行是系统“热身“，以避免以后再完成硬解析。第二次运行这个代码 块时，我使用runstats来查看二者的主要差别：先运行散列实现，然后运行堆实现。第三次运行代码块时，我启用了SQL_TRACE，从而能看到一个 TKPPOF报告。

第一个实现（散列实现）使用的闩少得多，这说明在一个读密集型环境中，散列实现应该能更好地扩缩，因为它需要的串行化资源（这些资 源要求某种程度的串行化）更少。与HEAP表相比，散列实现需要的I/O显著减少.

HASHED 现只是把传递到查询的OBJECT_ID转换为要读取的一个FILE/BLOCK，并且直接读，这里没有索引。HEAP表则不同，它必须对每一行在 索引上完成两个I/O。Oracle必须得到索引的根块，然后找出包含该行位置的叶子块。接下来必须得到叶子块信息，其中包含行的ROWID，再利用第3个I/O在表 中访问这个行。HEAP表完成的I/O是HASHED实现的3倍。
这里的要点是：
q 散列聚簇完成的I/O（查询列）少得多。查询只是取随机的OBJECT_ID，对其完成散列，然后找到块。散列聚簇至少要做一次I/O来 得到数据。有索引的传统表则必须完成索引扫描，然后要根据rowid访问表，才能得到同样的答案。在这个例子中，索引表必须至少完成3个I/O才能得到数 据。
q 不 论用于什么目的，散列聚簇查询与索引查询所用的CPU是一样的，尽管它访问缓存区缓存的次数只是后者的1/3。执行散列是一个 CPU相当密集的操作，执行索引查询则是一个I/O密集的操作。不过，随着用户数的增加，可以想见，散列聚簇查询能更好地扩缩，因为要想 很好地扩缩，就不能太过频繁地访问缓存区缓存。

散列聚簇有一个特例，称为单表散列聚簇（single table hash cluster）。它一次只支持聚簇中的一个表（必须DROP（删除）单表散列聚簇中现有的表，才能在其中创建另一个表）。另外，如果散列键和数据行之间存在一对一的映射，访问行还会更快一些。这种散列聚簇是为以下情况设计的：如果你想按主键来访问一个表，但是不关心其他表是否与这个 表聚簇在一起存储。如果你需要按EMPNO快速地访问员工记录，可能就需要一个单表散列聚簇。不过，你只能使用表中可用的列，而且编写自己的散列函数时只能使用Oracle的内置函数（例如，不能有PL/SQL代码）。由于上例中 OBJECT_ID是一个介于1～75,000之间的数，充分利用这一点，我建立了自己的“散列函数”：就是OBJECT_ID本身。采用这种方式，可以 保证绝对不会有散列冲突。综合在一起，我如下创建一个单表散列聚簇（有我自己的散列函数）：

create cluster hash_cluster
( hash_key number(10) )
hashkeys 75000
size 150
single table
hash is HASH_KEY
/

这里只是增加了关键字SINGLE TABLE， 使之作为一个单步散列聚簇。在这种情况下，我的散列函数就是HASH_KEY聚簇键本身。这是一个SQL函数，所以如果我愿意，也可以使用trunc (mod(hash_key/324+278,555)/abs(hash_key+1))（这只是说明，只要我们愿意， 完全可以使用一个复杂的函数）。我使用了NUMBER(10)而不是NUMBER，这是因为散列值必须是一个整数，所以不能有任何小数部分。下面，在这个表单散列聚簇中创建表：

scott@ORCL>create table t_hashed
  2  cluster hash_cluster(object_id)
  3  as
  4  select OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  5  cast( OBJECT_ID as number(10) ) object_id,
  6  DATA_OBJECT_ID, OBJECT_TYPE, CREATED,
  7  LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY,
  8  GENERATED, SECONDARY
  9  from all_objects
 10  /

表已创建。

以上建立了散列表。注意这里使用了CAST内置函数将OBJECT_ID强制转换为它本来的数据类型。

这个单表散列聚簇需要更少的缓冲区缓存闩来完成处理（它能更快地结束数据查找，而且能得到更多的信息）

散列聚簇表小结
散列聚簇在概念上与索引聚簇很相似，只不过没有使用聚簇索引。在这里，数据就是索引。聚簇键散列到一个块地址上，数据应该就在那个位置上。关于散列聚簇，需要了解以下要点：
q 散列聚簇一开始就要分配空间。Oracle根据你的HASHKEYS和SIZE来计算HASHKEYS/trunc（blocksize/SIZE），立即 分配空间，并完成格式化，一旦将第一个表放入这个聚簇中，任何全面扫描都会命中每一个已分配的块。在这方面，它与其他的所有表都不同。
q 散列聚簇中的HASHKEY数是固定大小的。除非重新聚簇，否则不能改变散列表的大小。这并不会限制聚簇中能存储的数据量，它只是限制了能为这个聚簇生成的惟一散列键的个数。如果HASHKEY值设置得太低，可能因为无意的散列冲突影响性能。
q 不能在聚簇键上完成区间扫描。诸如WHERE cluster_key BETWEEN 50 AND 60谓词条件不能使用散列算法。介于50～60之间的可能值有无限多个，服务器必须生成所有可能的值，并分别计算散列，来查看相应位置是否有数据。这是不 可能的。如果你在一个聚簇键上使用区间扫描，而且没有使用传统索引，实际上会全面扫描这个聚簇。
散列聚簇适用于以下情况：
你很清楚表中会有多少行，或者你知道一个合理的上界。HASHKEY和SIZE参数的大小要正确，这对于避免聚簇重建至关重要。
与 获取操作相比，DML（特别是插入）很轻。这说明必须在数据获取的优化和新数据的创建之间有所权衡。更新不会引入严重的开销， 除非更新了HASHKEY（不过这可不是一个好主意，因为更新HASHKEY会导致行迁移）。
经常按HASHKEY值访问数据。例如，假如有一个零件表，并按零件号来访问这些零件。查找表特别适合采用散列聚簇。

转载于:https://my.oschina.net/u/1862478/blog/1825381