阅读 175

Hash Cluster Table 研究

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. CREATE TABLE TAB_HASH_CLUSTER

  16.    ( TABLE_NAME VARCHAR2(30),

  17.         NDA_ID NUMBER(38,0),

  18.         AGI_ID NUMBER(38,0),

  19.         CHUNK_DATE DATE,

  20.         COMMIT_SCN NUMBER(22,0)

  21.    )

  22. CLUSTER HASH_CLUSTER (TABLE_NAME, NDA_ID, AGI_ID, CHUNK_DATE) ;

  23.  

  24. SQL> insert into TAB_HASH_CLUSTER select * from TEST_HASH_CLUSTER;

  25.  

  26. 1000000 rows created.

  27.  

  28. SQL>commit;

  29.  

  30. SQL> alter system flush buffer_cache;

  31.  

  32. 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\');

  33. ----------------------------------------------------------------------------

  34. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

  35. ----------------------------------------------------------------------------

  36. | 0 | SELECT STATEMENT | | 1 | 65 | 0 (0)|

  37. |* 1 | TABLE ACCESS HASH| TAB_HASH_CLUSTER | 1 | 65 | |

  38. ----------------------------------------------------------------------------

  39. Statistics

  40. ----------------------------------------------------------

  41.           0 recursive calls

  42.           0 db block gets

  43.           2 consistent gets

  44.           1 physical reads

  45.           0 redo size

  46.         853 bytes sent via SQL*Net to client

  47.         523 bytes received via SQL*Net from client

  48.           2 SQL*Net roundtrips to/from client

  49.           0 sorts (memory)

  50.           0 sorts (disk)

  51.           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. CREATE TABLE TAB_HASH_CLUSTER2

  9.    ( TABLE_NAME VARCHAR2(30),

  10.         NDA_ID NUMBER(38,0),

  11.         AGI_ID NUMBER(38,0),

  12.         CHUNK_DATE DATE,

  13.         COMMIT_SCN NUMBER(22,0)

  14.    )

  15. 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. 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\');

  3. ----------------------------------------------------------------------------

  4. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

  5. ----------------------------------------------------------------------------

  6. | 0 | SELECT STATEMENT | | 1 | 65 | 0 (0)|

  7. |* 1 | TABLE ACCESS HASH| TAB_HASH_CLUSTER2 | 1 | 65 | |

  8. ----------------------------------------------------------------------------

  9. Statistics

  10. ----------------------------------------------------------

  11.           0 recursive calls

  12.           0 db block gets

  13.          64 consistent gets

  14.          63 physical reads

  15.           0 redo size

  16.         853 bytes sent via SQL*Net to client

  17.         523 bytes received via SQL*Net from client

  18.           2 SQL*Net roundtrips to/from client

  19.           0 sorts (memory)

  20.           0 sorts (disk)

  21.           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. CREATE TABLE TAB_HASH_CLUSTER3

  9.    ( TABLE_NAME VARCHAR2(30),

  10.         NDA_ID NUMBER(38,0),

  11.         AGI_ID NUMBER(38,0),

  12.         CHUNK_DATE DATE,

  13.         COMMIT_SCN NUMBER(22,0)

  14.    )

  15. CLUSTER HASH_CLUSTER3 (TABLE_NAME, NDA_ID, AGI_ID, CHUNK_DATE);


  16. 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\');

  17. ----------------------------------------------------------------------------

  18. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|

  19. ----------------------------------------------------------------------------

  20. | 0 | SELECT STATEMENT | | 1 | 65 | 0 (0)|

  21. |* 1 | TABLE ACCESS HASH| TAB_HASH_CLUSTER3 | 1 | 65 | |

  22. ----------------------------------------------------------------------------


  23. Statistics

  24. ----------------------------------------------------------

  25.           0 recursive calls

  26.           0 db block gets

  27.           7 consistent gets

  28.           6 physical reads

  29.           0 redo size

  30.         853 bytes sent via SQL*Net to client

  31.         523 bytes received via SQL*Net from client

  32.           2 SQL*Net roundtrips to/from client

  33.           0 sorts (memory)

  34.           0 sorts (disk)

  35.           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值。



文章分类
后端
版权声明:本站是系统测试站点,无实际运营。本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 XXXXXXo@163.com 举报,一经查实,本站将立刻删除。
相关推荐