Hash分割槽表分割槽數與資料分佈的測試

較早就知道Hash分割槽建議分割槽數是2的冪， 只是當作經驗值記錄，一直都沒有測試過， 今天做了個簡單測試， 供大家參考。 親手做過的實驗，
記憶更加深刻一些 。



1.  建立分割槽數為5的hash分割槽表test01：

create table test01   
partition by hash(object_id)
(partition p1,
partition p2,
partition p3,
partition p4,
Partition p5)
as select * from sys.dba_objects;

檢視各個分割槽的記錄數 (隱約可以看出如果1，5合併的話，資料分佈會非常平均)：

select count(*) from test01 partition (p1);
6746
select count(*) from test01 partition (p2);
13550
select count(*) from test01 partition (p3);
13764
select count(*) from test01 partition (p4);
13445
select count(*) from test01 partition (p5);
6777




2.  直接建立分割槽數為8 (2的3次方) 的hash分割槽表test02：

create table test02   
partition by hash(object_id)
(partition p1,
partition p2,
partition p3,
partition p4,
partition p5,
partition p6,
partition p7,
Partition p8)
as select * from sys.dba_objects;


檢視各個分割槽的記錄數 (資料是平均分佈的)：

select count(*) from test02 partition (p1);
6750
select count(*) from test02 partition (p2);
6861
select count(*) from test02 partition (p3);
6891
select count(*) from test02 partition (p4);
6682
select count(*) from test02 partition (p5);
6778
select count(*) from test02 partition (p6);
6689
select count(*) from test02 partition (p7);
6874
select count(*) from test02 partition (p8);
6766






3.  在test01上增加hash分割槽p6：
alter table test01 add partition p6 ;


這時候後來看test01的資料分佈：

select count(*) from test01 partition (p1); -- 沒變
6746   
select count(*) from test01 partition (p2); -- 少了6689
6861
select count(*) from test01 partition (p3); -- 沒變
13764
select count(*) from test01 partition (p4); -- 沒變
13445
select count(*) from test01 partition (p5); -- 沒變
6777
select count(*) from test01 partition (p6); -- 恰好是6689
6689





4.  在test01上增加hash分割槽p7：
alter table test01 add partition p7 ;


這時候後來看test01的資料分佈(以下比較是相對於加入p6後)：

select count(*) from test01 partition (p1); -- 沒變
6746   
select count(*) from test01 partition (p2); -- 沒變
6861
select count(*) from test01 partition (p3); -- 少了6874
6890
select count(*) from test01 partition (p4); -- 沒變
13445
select count(*) from test01 partition (p5); -- 沒變
6777
select count(*) from test01 partition (p6); -- 沒變  
6689
select count(*) from test01 partition (p7); -- 恰好是6874  
6874




5.  在test01上增加hash分割槽p8：
alter table test01 add partition p8 ;


這時候後來看test01的資料分佈(以下比較是相對於加入p7後)：

select count(*) from test01 partition (p1); -- 沒變
6746   
select count(*) from test01 partition (p2); -- 沒變
6861
select count(*) from test01 partition (p3); -- 沒變
6890
select count(*) from test01 partition (p4); -- 少了6765
6680
select count(*) from test01 partition (p5); -- 沒變
6777
select count(*) from test01 partition (p6); -- 沒變  
6689
select count(*) from test01 partition (p7); -- 沒變   
6874
select count(*) from test01 partition (p7); -- 恰好是6765   
6765


大家從上面的資料分佈拆分情況可以大致看出Oracle是如何將資料平均分佈
的，也應該大致理解了為什麼Oracle的HASH分割槽數建議是2個冪 。

還可以看到加入到8個分割槽(2的3次方)後資料都平均分佈了，和一次性直接劃分
為8個分割槽資料分佈比較接近 (但是不相同)。  




6.  下面簡單測試一下如果從8個分割槽繼續加入到9，10，11，16
個分割槽又是怎樣的情況呢 ？ 這裡我們還是以test01表來做測試。

alter table test01 add partition p9 ;  


這時候後來看test01的資料分佈(以下比較是相對於加入p8後)：

select count(*) from test01 partition (p1); -- 少了3390
3356
select count(*) from test01 partition (p2); -- 沒變
6861
select count(*) from test01 partition (p3); -- 沒變
6890
select count(*) from test01 partition (p4); -- 沒變  
6680
select count(*) from test01 partition (p5); -- 沒變
6777
select count(*) from test01 partition (p6); -- 沒變  
6689
select count(*) from test01 partition (p7); -- 沒變   
6874
select count(*) from test01 partition (p8); -- 沒變   
6765
select count(*) from test01 partition (p9); -- 恰好是3390   
3390  





7.  alter table test01 add partition p10 ;  

這時候後來看test01的資料分佈(以下比較是相對於加入p9後)：

select count(*) from test01 partition (p1); -- 沒變  
3356
select count(*) from test01 partition (p2); -- 少了3443  
3418
select count(*) from test01 partition (p3); -- 沒變
6890
select count(*) from test01 partition (p4); -- 沒變  
6680
select count(*) from test01 partition (p5); -- 沒變
6777
select count(*) from test01 partition (p6); -- 沒變  
6689
select count(*) from test01 partition (p7); -- 沒變   
6874
select count(*) from test01 partition (p8); -- 沒變   
6765
select count(*) from test01 partition (p9); -- 沒變     
3390  
select count(*) from test01 partition (p10); -- 恰好是3443     
3443   



8.  alter table test01 add partition p11 ;  

這時候後來看test01的資料分佈(以下比較是相對於加入p10後)：

select count(*) from test01 partition (p1); -- 沒變  
3356
select count(*) from test01 partition (p2); -- 沒變   
3418
select count(*) from test01 partition (p3); -- 少了3444
3446
select count(*) from test01 partition (p4); -- 沒變  
6680
select count(*) from test01 partition (p5); -- 沒變
6777
select count(*) from test01 partition (p6); -- 沒變  
6689
select count(*) from test01 partition (p7); -- 沒變   
6874
select count(*) from test01 partition (p8); -- 沒變   
6765
select count(*) from test01 partition (p9); -- 沒變     
3390  
select count(*) from test01 partition (p10); -- 沒變      
3443   
select count(*) from test01 partition (p11); -- 恰好是3444     
3444  



OK, 其實不用測試這麼多，大家就可以看出規律了，但是這裡之所以測試
這些， 是為了透過機率的方式統計一下到底每次在拆分資料量的時候有什
麼規律 (雖然大前提是hash演算法)。 這裡可以粗略知道的是： 假設一個
表從8個分割槽增加到16個分割槽， partition 1~8 的 hash bucket no 應
該和9～16 的對應相等，因為9～16的資料都是分別從1～8 partition中
拆分出來的 。   



9. 現在我們一次性將分割槽加到16個，看看資料分佈情況，明顯已經均勻分佈了。

select count(*) from test01 partition (p1);  
3356
select count(*) from test01 partition (p2);   
3418
select count(*) from test01 partition (p3);  
3446
select count(*) from test01 partition (p4);  
3322
select count(*) from test01 partition (p5);  
3427
select count(*) from test01 partition (p6);  
3367
select count(*) from test01 partition (p7);  
3392
select count(*) from test01 partition (p8);   
3421
select count(*) from test01 partition (p9);   
3390  
select count(*) from test01 partition (p10);      
3443   
select count(*) from test01 partition (p11);     
3444  
select count(*) from test01 partition (p12);     
3358  
select count(*) from test01 partition (p13);     
3350
select count(*) from test01 partition (p14);     
3322
select count(*) from test01 partition (p15);     
3482
select count(*) from test01 partition (p16);     
3344