HASH分区表的好处和注意点

一、好处
  1、对于分区本生不需要定期的进行分区加入（范围分区和LIST分区需要定期的对新加入的值新建分区）
  2、可以消除访问热点块及索引热点块，由于索引是排序后的结构，对于一列自增的列加入范围分区，可能对索引的高位块进行频繁的数据插入，导致频繁的写入和分裂
     对于这样的索引如果加入散列分区索引即可消除。
二、注意
  1、分区不能太多，典型的大约1000个分区，那么在分区触发（谓词导致索引范围扫描）的并行访问操作时可能更慢，因为有非常多额外的分区维护操作（我曾经遇到过1个1844个HASH分区，导致的并行性能问题）
  2、散列索引在维护方面可能和范围和LIST分区有一些区别。
  详细见：
  http://blog.itpub.net/7728585/viewspace-756659/
  http://blog.itpub.net/7728585/viewspace-756736/
  http://blog.itpub.net/7728585/viewspace-756859/
  http://blog.itpub.net/7728585/viewspace-756884/
  http://blog.itpub.net/7728585/viewspace-757019/
  http://blog.itpub.net/7728585/viewspace-757029/
  3、对于长期使用范围扫描的字段不适合散列分区，因为这样会导致多个分区扫描，而对于经常唯一扫描的字段适合建立HASH分区。


 select /*+  gather_plan_statistics */  * from  t_pe_h where i>9990

----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | 
----------------------------------------------------------------------------------------------------
|   1 |  PARTITION HASH ALL                |          |      1 |     10 |   230 |     3   (0)|     1 |     4 |  
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_PE_H   |      4 |     10 |   230 |     3   (0)|     1 
|*  3 |    INDEX RANGE SCAN                | T_PE_H_L |      4 |     10 |       |     2   (0)|     1 |     4 |  
----------------------------------------------------------------------------------------------------

 select /*+  gather_plan_statistics */  * from  t_pe_h where i=9990


Plan hash value: 1195440189


----------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   1 |  PARTITION HASH SINGLE             |          |      1 |      1 |    23 |     2   (0)|     1 |     1 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_PE_H   |      1 |      1 |    23 |     2   (0)|     1 
|*  3 |    INDEX UNIQUE SCAN               | T_PE_H_L |      1 |      1 |       |     1   (0)|     1 |     1 |
----------------------------------------------------------------------------------------------------

  
  4、分区的个数是2的N次密。
  5、对于自增字段非常适合建立HASH分区，这样数据非常适合均匀的HASH分布，对于一个字段建立HASH分区最好事先对字段进行HASH统计，来测试是否能够达到均匀的分布。
select ora_hash(it, 7) + 1 ora_hash, count(*) 
from test1 
group by ora_hash(it, 7) 
order by 1;


ora_hash('strings', N, 0 )
    strings: 输入值
    N：最大hash bucket的值
    0：起始hash bucket值，缺省是
  
  6、对于所有分区类型，如果想用LOCAL分区索引来保证唯一性，那么唯一和主键约束（唯一索引也必须）必须包含有整个（注意是整个，如果分区键是两个字段，那么两个字段都要包含）分区键本生，或者说是子集
SQL>   CREATE TABLE t_pe_h (i NUMBER, j NUMBER , f varchar2(20),k varchar2(20))
  2         PARTITION BY hash(i,j)
  3             (PARTITION p1,
  4              PARTITION p2,
  5              PARTITION P3,
  6              PARTITION P4);
 
Table created
 
如下可以： 
SQL> create unique index t_pe_h_l on t_pe_h(i,j) local;
 
Index created
 
不行： 
SQL> create unique index t_pe_h_l on t_pe_h(i) local;
 
create unique index t_pe_h_l on t_pe_h(i) local
 
ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE index
 
可以： 
SQL> create unique index t_pe_h_1 on t_pe_h(i,j,f) local;
 
Index created

  
SQL> alter table t_pe_h add constraint t_pe_h_pk primary key(i,j) using index t_pe_h_l;
 
Table altered