查看: 4818|回复: 6

[笔记] Hash分区表分区数与数据分布的测试

[复制链接]
认证徽章
论坛徽章:
71
2015年新春福章
日期:2015-03-06 11:57:312013年新春福章
日期:2013-02-25 14:51:24双黄蛋
日期:2013-01-06 13:31:18蜘蛛蛋
日期:2013-01-06 10:26:08茶鸡蛋
日期:2012-11-21 19:35:23ITPUB 11周年纪念徽章
日期:2012-10-09 18:05:07版主2段
日期:2012-05-15 15:24:11铁扇公主
日期:2012-02-21 15:02:402012新春纪念徽章
日期:2012-02-13 15:13:512012新春纪念徽章
日期:2012-02-13 15:13:51
发表于 2011-1-20 09:58 | 显示全部楼层 |阅读模式
较早就知道Hash分区建议分区数是2的幂, 只是当作经验值记录,一直都没有测试过, 今天做了个简单测试, 供大家参考。 亲手做过的实验,
记忆更加深刻一些 。

Oracle 10.2.0.4  

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

[ 本帖最后由 tolywang 于 2011-1-21 10:01 编辑 ]
论坛徽章:
86
2015中国数据库技术大会纪念徽章
日期:2015-04-24 16:04:24马上有车
日期:2014-02-19 11:55:14马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11优秀写手
日期:2013-12-18 09:29:11日产
日期:2013-10-17 08:44:39马自达
日期:2013-08-26 16:28:022013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-23 16:55:51马上有房
日期:2014-02-19 11:55:14
发表于 2011-1-20 10:26 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
47
蒙奇·D·路飞
日期:2017-03-27 08:04:23马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11一汽
日期:2013-09-01 20:46:27复活蛋
日期:2013-03-13 07:55:232013年新春福章
日期:2013-02-25 14:51:24ITPUB 11周年纪念徽章
日期:2012-10-09 18:03:322012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:202012新春纪念徽章
日期:2012-02-13 15:13:20
发表于 2011-1-21 06:45 | 显示全部楼层
http://jonathanlewis.wordpress.com/2009/11/21/ora_hash-function/

Jonathan Lewis uses ora_hash function (new in 10g) to get the "ideal" number of the hash partition a given row will be inserted into. "Ideal" is my word here; it means if you don't have that partition number, the row will be put into one of the existing partitions. Run the SQLs on that page to see what I mean. The dbms_mview.pmarker(rowid) function returns the actual partition the row (whose ID is the rowid here) is in. If this pmarker returns the same value for different ora_hash values, these different rows will be put into the same partition.

It still doesn't answer the question when the total number of hash partitions is not power of 2 (so non-ideal number exists), which partition is picked to insert the row.

I don't see any practical value in this knowledge. If anybody sees practical benefit, let us know. Anyway it's interesting in itself, and being curious is not a bad thing.

Yong Huang

使用道具 举报

回复
论坛徽章:
9
生肖徽章2007版:兔
日期:2009-05-04 15:21:552009日食纪念
日期:2009-07-22 09:30:00祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:19:09ITPUB9周年纪念徽章
日期:2010-10-08 09:31:222010广州亚运会纪念徽章:乒乓球
日期:2010-11-10 15:23:182010广州亚运会纪念徽章:武术
日期:2011-01-26 12:59:23ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26马上有房
日期:2014-12-31 21:53:05
发表于 2011-1-21 13:35 | 显示全部楼层
学习了!!

使用道具 举报

回复
论坛徽章:
3
ITPUB十周年纪念徽章
日期:2011-11-01 16:26:292012新春纪念徽章
日期:2012-01-04 11:57:362013年新春福章
日期:2013-02-25 14:51:24
发表于 2011-10-26 18:42 | 显示全部楼层
不错啊 不错 学习了啊

使用道具 举报

回复
论坛徽章:
4
奥运会纪念徽章:垒球
日期:2008-09-08 13:24:552010广州亚运会纪念徽章:壁球
日期:2010-11-25 09:32:31复活蛋
日期:2011-08-01 17:25:35鲜花蛋
日期:2013-04-21 21:49:58
发表于 2011-10-26 19:07 | 显示全部楼层
学习了啊。

使用道具 举报

回复
论坛徽章:
9
ITPUB元老
日期:2009-12-28 22:11:19ITPUB9周年纪念徽章
日期:2010-10-08 09:31:21ITPUB十周年纪念徽章
日期:2011-11-01 16:21:152012新春纪念徽章
日期:2012-01-04 11:51:222014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02
发表于 2011-10-27 21:11 | 显示全部楼层
2的N次方.TOM建议的.

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

DTCC2020中国数据库技术大会 限时8.5折

【架构革新 高效可控】2020年6月4日~6日第十一届中国数据库技术大会将在北京隆重召开。

大会设置2大主会场,20+技术专场,将邀请超百位行业专家,重点围绕数据架构、AI与大数据、传统企业数据库实践和国产开源数据库等内容展开分享和探讨,为广大数据领域从业人士提供一场年度盛会和交流平台。

http://dtcc.it168.com


大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表