hash partition 平衡分布数据的测试(一)

Thedestination of a row is determined by the internal hash function applied to therow by the database. The hashing algorithm is designed to evenly distributesrows across devices so that each partition contains about the same number ofrows.

官方文档说明hashpartition 能够平衡每个分区上面的行的个数，做了个小测试，记录下来，供大家参考。

1. 创建一个hash分区表 ， 一共四个分区，分区名称由系统自动生成。
--也可以替换为 create table hash_t (id number ) partition by hash(id) (partition&part_name1,partition &part_name2,partition &part_name3,partition&part_name4) ;

dexter@ORCL>create tablehash_t (id number ) partition by hash(id) partitions 4 ;

Tablecreated.

--查看一下表的统计信息，可以看到blocks为空但是segment已经创建
dexter@ORCL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME ,NUM_ROWS , BLOCKS , segment_created fromuser_tab_partitions ;

TABLE_NAMEPARTITION_NAME TABLESPACE_NAME NUM_ROWS BLOCKSSEG
---------- ------------------------------ ---------------------------------------- ---------- ---
HASH_T SYS_P29 USERS YES
HASH_T SYS_P28 USERS YES
HASH_T SYS_P30 USERS YES
HASH_T SYS_P31 USERS YES

--已经分配了extent
dexter@ORCL> select * from user_extents where segment_name='HASH_T' ;

SEGMENT_NAPARTITION_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID BYTES BLOCKS
---------- ------------------------------ ------------------------------------------------ ---------- --------------------
HASH_T SYS_P28 TABLE PARTITION USERS 0 65536 8
HASH_T SYS_P29 TABLE PARTITION USERS 0 65536 8
HASH_T SYS_P30 TABLE PARTITION USERS 0 65536 8
HASH_T SYS_P31 TABLE PARTITION USERS 0 65536 8

--查看一下参数
dexter@ORCL> show parameter deferred_segment_creation

NAME TYPE VALUE
------------------------------------ -----------------------------------------
deferred_segment_creation boolean TRUE

2. 初始化一些数据
dexter@ORCL> insert into hash_t select level from dual connectby level <= 100000 ;

100000rows created.

dexter@ORCL> commit ;

Commitcomplete.

3. 查看一下分区统计信息
--我们来收集一下统计信息，使用dbms_stats收集分区表的统计信息更加准确
dexter@ORCL> execdbms_stats.gather_table_stats('dexter','hash_t',cascade=>true);

PL/SQLprocedure successfully completed.

--可以看到分布的比较均匀
dexter@ORCL> select TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME ,NUM_ROWS , BLOCKS fromuser_tab_partitions ;

TABLE_NAMEPARTITION_NAME TABLESPACE_NAME NUM_ROWS BLOCKS
---------- ------------------------------ ---------------------------------------- ----------
HASH_T SYS_P29 USERS 24956 43
HASH_T SYS_P28 USERS 24945 43
HA