【原創】Oracle之range,hash,list分割槽現實應用及優缺點彙總

引言：oracle的range,hash,list三大分割槽可能我們大家在熟悉不過了，但什麼每種分割槽適用於什麼場景可能是很多人的疑惑點，那麼在選擇不同的分割槽時應該注意什麼，為什麼這麼選，有哪些需要我們加以注意的地方，讓我們一一來探索一下吧：）

A.建立range分割槽，一般用於日期化處理，range分割槽可以很好的管理基於日期來分割槽的資料
建立一個新使用者ls
> create user ls identified by ls;
> grant dba to ls;
> 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”
> 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)
(
partition  asia VALUES('china','japan'),
partition  europe VALUES ('germany','italy'),
partition  africa VALUES('libya','brazil'),
partition  other  VALUES(DEFAULT)              --預設分割槽
);
插入資料
insert into liusheng_list values(1,'china','asia',100);
insert into liusheng_list values(2,'germany','europe',101);
insert into liusheng_list values(3,'libya','africa',102);
insert into liusheng_list values(4,'liusheng_city','other',103);
檢視資料
> select * from liusheng_list;

   CITY_ID COUNTRY_NAME                   STATE                CITY_AMOUNT
---------- ------------------------------ -------------------- -----------
         1 china                                               asia                         100
         2 germany                                      europe                       101
         3 libya                                                 africa                       102
         4 liusheng_city                                 other                        103

小結：測試了上面的三大分割槽後，我相信現在不僅僅有感性的認識而且也加深了理性的認識，分割槽是個好東西，合理的利用可以提高我們管理收益（但不一定會提高查詢收益），使用不當也會給我們新增許多麻煩，三思而後行是做DBA的一個好習慣。

Leonarding
2012.7.24
天津&summer
分享技術~收穫快樂
Blog：http://space.itpub.net/26686207

補充：分割槽之優勢

分割槽技術實質可以把資料分攤到不同的物理位置，增加I/O負載，提高檢索效率。
可用性：分割槽表可以跨越表空間，而普通表則不然，好處就是如果表的一個分割槽損壞，其他分割槽不會受到影響我們只需要修復損壞的分割槽即可