Oracle分区表基础运维-03HASH分区

Hash Partitioning

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify.

The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Hash partitioning is the ideal method for distributing data evenly across devices. Hash partitioning is also an easy-to-use alternative to range partitioning, especially when the data to be partitioned is not historical or has no obvious partitioning key.

创建HASH分区表

---创建hash分区表hash_part_tab

create table hash_part_tab (id number,deal_date date,area_code number,contents varchar2(4000))
partition by hash (deal_date)
PARTITIONS 12;

--以下是插入一整年日期随机数和表示福建地区号含义（591到599）的随机数记录，共有10万条，如下：

insert into hash_part_tab
  (id, deal_date, area_code, contents)
  select rownum,
         to_date(to_char(sysdate - 365, 'J') +
                 TRUNC(DBMS_RANDOM.VALUE(0, 365)),
                 'J'),
         ceil(dbms_random.value(590, 599)),
         rpad('*', 400, '*')
    from dual
  connect by rownum <= 100000;
commit;

---查看当前用户下有哪些分区表

select TABLE_NAME from user_tables a where a.partitioned='YES';

---查看分区表名，分区名，表空间等信息

select table_name, partition_name, tablespace_name, high_value
  from user_tab_partitions
 where table_name = 'HASH_PART_TAB';

---通过object_id查看每个分区数据分布情况

select * from dba_segments where segment_name = 'HASH_PART_TAB';
select * from dba_objects where object_name='HASH_PART_TAB';
select dbms_rowid.rowid_object(rowid) obj_id, count(*)
  from HASH_PART_TAB
 group by dbms_rowid.rowid_object(rowid)
 order by 1;

hash partition不能直接增加分区，而是split当前分区，hash bucket总是2的N次方，如果分区数不足，则会合并数据，产生不均衡的情况，这样增加分区时，只需要对应分区的数据做split即可。同理，减少分区也不是简单的drop，而是合并分区。

例如上面我们创建了12个分区，实际上hash bucket数量是2的4次方，既16个hash bucket，多出的4个hash bucket会进行合并，会产生数据不均，也就是有4个分区的数据会比较多。即OBJ_ID=77373,77374,77375,77376四个分区数据较多。

---增加新分区P1

alter table HASH_PART_TAB add partition P1;

此时OBJ_ID=77373的分区，分裂成OBJ_ID=77392和OBJ_ID=77393，数据由11191分成5761和5431分布存在这两个分区里。

---增加新分区P2

alter table HASH_PART_TAB add partition P2;

---增加新分区P3

alter table HASH_PART_TAB add partition P3;

---增加新分区P4

alter table HASH_PART_TAB add partition P4;

此时有16个分区了，是2的4次方，数据较比12个分区时，分布更均匀了。

---增加新分区P5，第一个分区OBJ_ID=77369分裂成两个新分区，其他分区数据不变。

alter table HASH_PART_TAB add partition P5;

HASH分区表不能通过如下方式进行删除

alter table HASH_PART_TAB drop partition P5;

ORA-14255:table is not partitioned by Range, List, Composite Range or Composite List method

更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：

http://blog.itpub.net/29785807/