Oracle之range,hash,list分区现实应用及优缺点汇总

引言：Oracle的range,hash,list三大分区可能我们大家在熟悉不过了，但什么每种分区适用于什么场景可能是很多人的疑惑点，那么在选择不同的分区时应该注意什么，为什么这么选，有哪些需要我们加以注意的地方，让我们一一来探索一下吧：）

A.创建range分区，一般用于日期化处理，range分区可以很好的管理基于日期来分区的数据
创建一个新用户ls
SYS@LEO> create user ls identified by ls;
SYS@LEO> grant dba to ls;
SYS@LEO> conn ls/ls
创建样本数据表
create table liusheng (orderid number(10),name varchar2(10),ls_date date);
insert into liusheng values (1,'ls1',to_date('1981-01-02','yyyy-mm-dd'));
insert into liusheng values (1,'ls2',to_date('1998-01-03','yyyy-mm-dd'));
insert into liusheng values (1,'ls3',to_date('1999-01-04','yyyy-mm-dd'));
insert into liusheng values (1,'ls4',to_date('2000-01-05','yyyy-mm-dd'));
insert into liusheng values (1,'ls5',to_date('2000-01-06','yyyy-mm-dd'));
insert into liusheng values (1,'ls6',to_date('2001-01-07','yyyy-mm-dd'));
insert into liusheng values (1,'ls7',to_date('2001-01-08','yyyy-mm-dd'));
insert into liusheng values (1,'ls8',to_date('2002-01-09','yyyy-mm-dd'));
insert into liusheng values (1,'ls9',to_date('2002-01-10','yyyy-mm-dd'));
insert into liusheng values (1,'ls10',to_date('2011-01-11','yyyy-mm-dd'));
创建range分区表
create table liusheng_part
partition by range (ls_date)
(
partition liusheng_part_1999_1 values less than (to_date('1999-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2000_1 values less than (to_date('2000-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2001_1 values less than (to_date('2001-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2002_1 values less than (to_date('2002-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2003_1 values less than (to_date('2003-01-01','yyyy-mm-dd')) ,
partition other values less than (maxvalue)
)
as select * from liusheng;
查看分区
select * from liusheng_part partition (liusheng_part_1999_1);
select * from liusheng_part partition (liusheng_part_2000_1);
select * from liusheng_part partition (liusheng_part_2001_1);
select * from liusheng_part partition (liusheng_part_2002_1);
select * from liusheng_part partition (liusheng_part_2003_1);
select * from liusheng_part partition (other);

B.创建hash分区，利用hash函数打散某列使数据均匀分布，一般用于均衡I/O，缺点数据不容易管理，哈希分区不能DROP、SPLIT 以及MERGE分区
我们创建了拥有10个分区的哈希分区表“LIUSHENG_HASH”
LS@LEO> create table liusheng_hash partition by hash(object_id) partitions 10 as select * from dba_objects;
hash分区所占用的区个数，看每个分区占用的个数都差不多，说明数据还是比较均匀分布的
缺点：hash列上数值不能有太多的重复值，否则会导致数据分布不均匀
select partition_name,count(*) from user_extents where segment_name='LIUSHENG_HASH' group by partition_name;
PARTITION_NAME?????????????????? COUNT(*)
------------------------------ ----------
SYS_P27???????????????????????????????? 3
SYS_P26???????????????????????????????? 3
SYS_P22???????????????????????????????? 2
SYS_P28???????????????????????????????? 3
SYS_P29???????????????????????????????? 2
SYS_P21???????????????????????????????? 2
SYS_P23???????????????????????????????? 3
SYS_P25???????????????????????????????? 3
SYS_P30???????????????????????????????? 2
SYS_P24???????????????????????????????? 3
select count(*) from liusheng_hash;

COUNT(*)
----------
????? 9860

C.创建list分区，一般用于数据可枚举，有限个值，可以考虑列表分区，例如国家名字，按州来分区
创建list分区表，我们按国家来分别存放在不同的州，每个州是一个分区
create table liusheng_list
(city_id NUMBER(5),
city_name VARCHAR2(30),
city_state VARCHAR2(20),
city_amount NUMBER(10)
)
partition by list (city_name)
(
partiti