Oracle study之--HASH Cluster特点

骑猪看日落 2022-08-08 05:23 90阅读 0赞

**  Oracle study之--HASH Cluster特点**

Hash Cluster Table是Cluster Table的一种(另一种是Index Cluster Table)。在Hash Cluster Table中，Oracle会为每行数据按Hash键计算一个Hash值，拥有同样Hash值的记录在Hash Table中会物理上存放在一起,oracle为Hash Key计算所得 到的Hash Value会对应到确定的数据库块地址，这样，应用在访问Hash Cluster Table时就可以根据Hash Value快速定位到要访问的数据，也就是说，在Hash Cluster Table中，数据本身就是索引。

在创建Hash Cluster Table时，有两个参数很重要，它们是SIZE和HASHKEYS参数。SIZE参数指定用来保存相同键值记录的空间大小。HASHKEYS参数指定Hash键的个数。该值要求是一个质数，若用户指定的值不是质数，则数据库会取与用户指定值最接近的质数。

本文试图通过测试验证关于Hash Cluster Table的以下问题：

1.  在提高应用访问性能方面，Hash Cluster Table与索引的区别；

2.  不同SIZE和HASHKEYS参数值对Hash Cluster Table的存储和查询性能有何影响；

3.  Hash Cluster Table的适用场景，使用上有什么限制。

测试如下：

表test\_hash\_cluster有100万条记录，占用空间为55M,其表结构如下：

1.  SQL> select count(\*) from test\_hash\_cluster;  
    
2.    COUNT(\*)  
    
3.  \----------  
    
4.     1000000  
    
5.     
    
6.  SQL> select bytes/1024/1024 from user\_segments where segment\_name=\\'TEST\_HASH\_CLUSTER\\';  
    
7.  BYTES/1024/1024  
    
8.  \---------------  
    
9.               55

SQL> desc TEST\_HASH\_CLUSTER                                
 Name                        Null?    Type                 
 --------------------------- -------- ------------------   
 TABLE\_NAME                           VARCHAR2(30)         
 NDA\_ID                               NUMBER(38)           
 AGI\_ID                               NUMBER(38)           
 CHUNK\_DATE                           DATE                 
 COMMIT\_SCN                           NUMBER(22)

假设我们的应用对test\_hash\_cluster表的访问方式比较固定，具体查询语句如下：

1.  select \* from test\_hash\_cluster where table\_name=:B1 and NDA\_ID=:B2 and AGI\_ID=:B3 and CHUNK\_DATE=:B4;

若要求以上查询有个快速响应结果，通常我们会怎么做呢？

一般来说，我们都会考虑在(TABLE\_NAME,NDA\_ID,AGI\_ID,CHUNK\_DATE)列上创建组合索引，或者我们将TEST\_HASH\_CLUSTER表创建为索引组织表(IOT).其实我们还可以考虑Hash Cluster Table.

首先，我们还是为TEST\_HASH\_CLUSTER表创建索引，以与后面的Hash Cluster Table作性能上的比较。

1.  create index ind\_test\_hash\_cluster on test\_hash\_cluster (table\_name, nda\_id, agi\_id, chunk\_date);  
    
2.     
    
3.  SQL> select bytes/1024/1024 from user\_segments where segment\_name=\\'IND\_TEST\_HASH\_CLUSTER\\';  
    
4.     
    
5.  BYTES/1024/1024  
    
6.  \---------------  
    
7.               54  
    
8.     
    
9.  exec DBMS\_STATS.GATHER\_TABLE\_STATS(\\'SCOTT\\',\\'TAB\_HASH\_CLUSTER\\',estimate\_percent=>100);  
    
10.    
    
11. SQL> select BLEVEL from user\_indexes where index\_name=\\'IND\_TEST\_HASH\_CLUSTER\\';  
    
12.    
    
13.     BLEVEL  
    
14. \----------  
    
15.          2

索引ind\_test\_hash\_cluster占用54MB的空间，其BLEVEL值为2，当我们查询TEST\_HASH\_CLUSTER表中的一条记录时，理论上我们需要访问三个索引数据块(root block, branch block, leaf block)+一个表数据块，即在没有数据缓存的情况下，我们需要作4次Physical Reads.下面的结果验证了上面的猜测：

1.  select \* from test\_hash\_cluster where table\_name=\\'NTS\_OPEN\_FUND\_UNIT\_TS\\' and NDA\_ID=1213871 and AGI\_ID=2560 and CHUNK\_DATE=to\_date(\\'20050101\\',\\'yyyymmdd\\');  
    
2.     
    
3.  \-----------------------------------------------------------------------------------------------------  
    
4.  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |  
    
5.  \-----------------------------------------------------------------------------------------------------  
    
6.  | 0 | SELECT STATEMENT | | 1 | 45 | 4 (0)| 00:00:01 |  
    
7.  | 1 | TABLE ACCESS BY INDEX ROWID| TEST\_HASH\_CLUSTER | 1 | 45 | 4 (0)| 00:00:01 |  
    
8.  |\* 2 | INDEX RANGE SCAN | IND\_TEST\_HASH\_CLUSTER | 1 | | 3 (0)| 00:00:01 |  
    
9.  \-----------------------------------------------------------------------------------------------------  
    
10. Statistics  
    
11. \----------------------------------------------------------  
    
12.           0 recursive calls  
    
13.           0 db block gets  
    
14.           5 consistent gets  
    
15.           4 physical reads  
    
16.           0 redo size  
    
17.         857 bytes sent via SQL\*Net to client  
    
18.         523 bytes received via SQL\*Net from client  
    
19.           2 SQL\*Net roundtrips to/from client  
    
20.           0 sorts (memory)  
    
21.           0 sorts (disk)  
    
22.           1 rows processed

接下来，我们看看Hash Cluster Table的表现：

1.  CREATE CLUSTER HASH\_CLUSTER (  
    
2.          TABLE\_NAME VARCHAR2(30),  
    
3.          NDA\_ID NUMBER(38,0),  
    
4.          AGI\_ID NUMBER(38,0),  
    
5.          CHUNK\_DATE DATE )  
    
6.  SIZE 128  
    
7.  HASHKEYS 1000003  
    
8.  STORAGE(INITIAL 1048576 NEXT 1048576);  
    
9.     
    
10. SQL> select bytes/1024/1024 from user\_segments where segment\_name=\\'HASH\_CLUSTER\\';  
    
11.    
    
12. BYTES/1024/1024  
    
13. \---------------  
    
14.              128  
    
15.   
    
16. CREATE TABLE TAB\_HASH\_CLUSTER  
    
17.    ( TABLE\_NAME VARCHAR2(30),  
    
18.         NDA\_ID NUMBER(38,0),  
    
19.         AGI\_ID NUMBER(38,0),  
    
20.         CHUNK\_DATE DATE,  
    
21.         COMMIT\_SCN NUMBER(22,0)  
    
22.    )  
    
23. CLUSTER HASH\_CLUSTER (TABLE\_NAME, NDA\_ID, AGI\_ID, CHUNK\_DATE) ;  
    
24.    
    
25. SQL> insert into TAB\_HASH\_CLUSTER select \* from TEST\_HASH\_CLUSTER;  
    
26.    
    
27. 1000000 rows created.  
    
28.    
    
29. SQL>commit;  
    
30.    
    
31. SQL> alter system flush buffer\_cache;  
    
32.    
    
33. SQL> select \* from TAB\_HASH\_CLUSTER where table\_name=\\'NTS\_OPEN\_FUND\_UNIT\_TS\\' and NDA\_ID=1213871 and AGI\_ID=2560 and CHUNK\_DATE=to\_date(\\'20050101\\',\\'yyyymmdd\\');  
    
34. \----------------------------------------------------------------------------  
    
35. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|  
    
36. \----------------------------------------------------------------------------  
    
37. | 0 | SELECT STATEMENT | | 1 | 65 | 0 (0)|  
    
38. |\* 1 | TABLE ACCESS HASH| TAB\_HASH\_CLUSTER | 1 | 65 | |  
    
39. \----------------------------------------------------------------------------  
    
40. Statistics  
    
41. \----------------------------------------------------------  
    
42.           0 recursive calls  
    
43.           0 db block gets  
    
44.           2 consistent gets  
    
45.           1 physical reads  
    
46.           0 redo size  
    
47.         853 bytes sent via SQL\*Net to client  
    
48.         523 bytes received via SQL\*Net from client  
    
49.           2 SQL\*Net roundtrips to/from client  
    
50.           0 sorts (memory)  
    
51.           0 sorts (disk)  
    
52.           1 rows processed

从测试结果可以看到，同样的查询语句，当底层表是Hash Cluster Table时，我们只需要作一次Physical Reads，读取一个数据块即可得到需要的数据了。

这是如何实现的呢？

在TAB\_HASH\_CLUSTER表的底层，oracle根据每条记录的(TABLE\_NAME,NDA\_ID,AGI\_ID,CHUNK\_DATE)列作hash计算，得到了一个Hash Value,这些Hash Value会对应到该记录在Cluster中具体的块地址。当应用在查询数据时，如果查询的where条件中提供了具体的(TABLE\_NAME,NDA\_ID,AGI\_ID,CHUNK\_DATE)值，Oracle会为查询的条件用同样的Hash算法计算Hash Value,这样，查询时就可以根据该Hash Value直接找到具体的数据块了。

接下来，我们看看SIZE和HASHKEYS参数对Hash表的影响：

前面，我们在创建Cluster HASH\_CLUSTER时指定的HASHKEYS值为1000003，表TAB\_HASH\_CLUSTER的记录数为1000000，HASHKEYS值大于数据记录数，也就是有足够的HASHKEYS来保证HASHKEYS与数据之间是一一对应的。

那么，如果我们指定一个相对较小的HASHKEYS值会怎么样呢？

1.  CREATE CLUSTER HASH\_CLUSTER2 (  
    
2.          TABLE\_NAME VARCHAR2(30),  
    
3.          NDA\_ID NUMBER(38,0),  
    
4.          AGI\_ID NUMBER(38,0),  
    
5.          CHUNK\_DATE DATE )  
    
6.  SIZE 128  
    
7.  HASHKEYS 10007;  
    
8.    
    
9.  CREATE TABLE TAB\_HASH\_CLUSTER2  
    
10.    ( TABLE\_NAME VARCHAR2(30),  
    
11.         NDA\_ID NUMBER(38,0),  
    
12.         AGI\_ID NUMBER(38,0),  
    
13.         CHUNK\_DATE DATE,  
    
14.         COMMIT\_SCN NUMBER(22,0)  
    
15.    )  
    
16. CLUSTER HASH\_CLUSTER2 (TABLE\_NAME, NDA\_ID, AGI\_ID, CHUNK\_DATE);

表中有1,000,000条记录，但Hashkeys值只有10007个，这样，一个hash值就需要对应多条记录，也就是在读取数据时，一个Hash Value会对应到多个数据块上，也就是需要访问多个数据块，如下：

1.  alter system flush buffer\_cache;  
    
2.    
    
3.  SQL> select \* from TAB\_HASH\_CLUSTER2 where table\_name=\\'NTS\_OPEN\_FUND\_UNIT\_TS\\' and NDA\_ID=1213871 and AGI\_ID=2560 and CHUNK\_DATE=to\_date(\\'20050101\\',\\'yyyymmdd\\');  
    
4.  \----------------------------------------------------------------------------  
    
5.  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|  
    
6.  \----------------------------------------------------------------------------  
    
7.  | 0 | SELECT STATEMENT | | 1 | 65 | 0 (0)|  
    
8.  |\* 1 | TABLE ACCESS HASH| TAB\_HASH\_CLUSTER2 | 1 | 65 | |  
    
9.  \----------------------------------------------------------------------------  
    
10. Statistics  
    
11. \----------------------------------------------------------  
    
12.           0 recursive calls  
    
13.           0 db block gets  
    
14.          64 consistent gets  
    
15.          63 physical reads  
    
16.           0 redo size  
    
17.         853 bytes sent via SQL\*Net to client  
    
18.         523 bytes received via SQL\*Net from client  
    
19.           2 SQL\*Net roundtrips to/from client  
    
20.           0 sorts (memory)  
    
21.           0 sorts (disk)  
    
22.           1 rows processed

除了Hashkeys参数，影响Hash Cluster Table的另一个重要参数是Size. 拥有相同键值的记录保存在由Size参数指定的同一空间中，或者说同一数据块中。在上面的例子中，10,007个Hash Value对应1,000,000条记录，平均一个Hash Value对应大约100条记录，但从统计信息看来，只访问了63个数据块，而不是100左右，其中重要原因就是Size参数，这里我们设置的是128字节，该Size可能可以存储多于一条的记录，这样100条记录就不一定是在100个不同块中，所以实际访问的数据块只有63。按此逻辑，如果我们将Size参数设置得更大，就可以保证更多拥有相同Hash Value的记录在物理上聚集在一起存放，比如，这里测试将Size设置为1024 byte.

1.  CREATE CLUSTER HASH\_CLUSTER3 (  
    
2.          TABLE\_NAME VARCHAR2(30),  
    
3.          NDA\_ID NUMBER(38,0),  
    
4.          AGI\_ID NUMBER(38,0),  
    
5.          CHUNK\_DATE DATE )  
    
6.  SIZE 1024  
    
7.  HASHKEYS 10007;  
    
8.    
    
9.  CREATE TABLE TAB\_HASH\_CLUSTER3  
    
10.    ( TABLE\_NAME VARCHAR2(30),  
    
11.         NDA\_ID NUMBER(38,0),  
    
12.         AGI\_ID NUMBER(38,0),  
    
13.         CHUNK\_DATE DATE,  
    
14.         COMMIT\_SCN NUMBER(22,0)  
    
15.    )   
    
16. CLUSTER HASH\_CLUSTER3 (TABLE\_NAME, NDA\_ID, AGI\_ID, CHUNK\_DATE);  
    
17.   
    
18. select \* from TAB\_HASH\_CLUSTER4 where table\_name=\\'NTS\_OPEN\_FUND\_UNIT\_TS\\' and NDA\_ID=1213871 and AGI\_ID=2560 and CHUNK\_DATE=to\_date(\\'20050101\\',\\'yyyymmdd\\');  
    
19. \----------------------------------------------------------------------------  
    
20. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|  
    
21. \----------------------------------------------------------------------------  
    
22. | 0 | SELECT STATEMENT | | 1 | 65 | 0 (0)|  
    
23. |\* 1 | TABLE ACCESS HASH| TAB\_HASH\_CLUSTER3 | 1 | 65 | |  
    
24. \----------------------------------------------------------------------------  
    
25.   
    
26. Statistics  
    
27. \----------------------------------------------------------  
    
28.           0 recursive calls  
    
29.           0 db block gets  
    
30.           7 consistent gets  
    
31.           6 physical reads  
    
32.           0 redo size  
    
33.         853 bytes sent via SQL\*Net to client  
    
34.         523 bytes received via SQL\*Net from client  
    
35.           2 SQL\*Net roundtrips to/from client  
    
36.           0 sorts (memory)  
    
37.           0 sorts (disk)  
    
38.           1 rows processed

正如前面所预料的, physical reads 降低了很多, 从63 降到了 6.

总结来说，当我们决定要用Hash Cluster Table来提高应用查询性能，那么选择合适的SIZE和HASHKEYS参数值就显得异常重要。设置合适SIZE和HASHKEYS参数的标准就是在读取数据时需要访问尽量少的数据块。一种方法就是设置HASHKEYS的值比数据记录数更大，或者当HASHKEYS小于总记录数时，保证Size指定的大小能存下所有拥有相同Hash Value的记录。否则，就会发生类似与“行链接”的想象，数据库需要用溢出块来存储多余的数据，并在原块中建立一个链接来指到新的溢出块上，进而影响访问性能。

最后，我们来看看Hash Cluster Table的适用场景及使用限制：

1.  Hash Cluster Table适用于查询较频繁，而写操作相对较少的表，因为写数据时需要为每条记录计算Hash 值，并且需要在固定的块上作插入操作；

2.  由于Hash Cluster Table是基于Hash算法创建的，所以查询时必须是等值查询；否则，无从按查询条件计算Hash值；

3.  在我们决定使用Hash Cluster Table之前，我们需要能合理估计Hash Key的数量以及数据的空间大小，从而选择合适的SIZE和HASHKEYS参数,这对表的访问性能至关重要；

4.  与索引相比，Hash Cluster Table有其优势，如前面的测试所示，但也有其劣势，比如不够灵活。还以前面的例子来看，若我们建的是复合索引，则我们在查询时只要查询条件中指定了前面的一个或几个列，就能利用索引作快速查询。但若是Hash Cluster Table，则要求我们在查询条件中包含所有的Hash Key值。

**   --------转载自http://blog.itpub.net/27243841/viewspace-1104755/，感谢作者 ！**