ora_hash分区表的数据均衡问题

基于dba_objects创建一个测试表t,并测试数据分布情况.

5个hash分区时:(4<5<8的分区数)

SQL> exec hash_proc(5,:x);

PL/SQL procedure successfully completed.


PN      CNT HG
-- ---------- --------------------------------
p1     9523 ***************
p2    18770 *****************************
p3    19024 ******************************
p4    18706 *****************************
p5     9322 **************

查询五个分区的object_id的hash值:

SELECT partition_name pname, partition_position pos FROM user_tab_partitions WHERE table_name = 'T' ORDER BY partition_position;

PNAME                                 POS
------------------------------ ----------
SYS_P87                                 1
SYS_P88                                 2
SYS_P89                                 3
SYS_P90                                 4
SYS_P91                                 5

select ora_hash(object_id,7,0)+1,count(*) from t PARTITION (SYS_P87) group by ora_hash(object_id,7,0)+1 union all
select ora_hash(object_id,7,0)+1,count(*) from t PARTITION (SYS_P88) group by ora_hash(object_id,7,0)+1 union all
select ora_hash(object_id,7,0)+1,count(*) from t PARTITION (SYS_P89) group by ora_hash(object_id,7,0)+1 union all
select ora_hash(object_id,7,0)+1,count(*) from t PARTITION (SYS_P90) group by ora_hash(object_id,7,0)+1 union all
select ora_hash(object_id,7,0)+1,count(*) from t PARTITION (SYS_P91) group by ora_hash(object_id,7,0)+1;

1对应SYS_P87; 

2,6对应SYS_P88;

3,7对应SYS_P89;

4,8对应SYS_P90;

5对应SYS_P91.

ORA_HASH(OBJECT_ID,7,0)+1   COUNT(*)
------------------------- ----------
                        1       9523
                        6       9274
                        2       9496
                        7       9514
                        3       9510
                        4       9363
                        8       9343
                        5       9322

此时添加一个hash分区:

alter table t add partition t_hash_p6;

再次检查数据分布.

SYS_P88分出了新的分区t_hash_p6; 

PN      CNT HG
-- ---------- -------------------------------
p1     9523 ***************
p2     9496 ***************
p3    19024 ******************************
p4    18706 *****************************
p5     9322 **************
p6     9274 **************

再添加一个分区

alter table t add partition t_hash_p7;

再次检查数据分布.

SYS_P89分出了新的分区t_hash_p7.

PN      CNT HG
-- ---------- ------------------------------
p1     9523 ***************
p2     9496 ***************
p3     9510 ***************
p4    18706 ******************************
p5     9322 ***************
p6     9274 ***************
p7     9514 ***************

当再次添加个新的分区后,变为8个分区,表数据终于均衡分布了.

SQL> exec hash_proc_2(:x);

PL/SQL procedure successfully completed.


PN      CNT HG
-- ---------- --------------------------------
p1     9523 ******************************
p2     9496 ******************************
p3     9510 ******************************
p4     9363 *****************************
p5     9322 *****************************
p6     9274 *****************************
p7     9514 ******************************
p8     9343 *****************************

如果coalesce partition,也是按照上面的hash值的分配进行拆分分区了.这样在添加一个分区或者合并分区时,数据在重新分布时,io的消耗就很低了.