oracle 分區詳解

轉自;http://love-flying-snow.iteye.com/blog/573303

廢話少說，直接講分區文法。

Oracle表分區分為四種：定界分割，散列分區，列表分區和複合分區。

 

一：定界分割

就是根據資料庫表中某一欄位的值的範圍來劃分分區，例如：

create table graderecord  (  sno varchar2(10),  sname varchar2(20),  dormitory varchar2(3),  grade int)partition by range(grade)(  partition bujige values less than(60), --不及格  partition jige values less than(85), --及格  partition youxiu values less than(maxvalue) --優秀)

插入實驗資料：

insert into graderecord values('511601','魁','229',92);insert into graderecord values('511602','凱','229',62);insert into graderecord values('511603','東','229',26);insert into graderecord values('511604','亮','228',77);insert into graderecord values('511605','敬','228',47);insert into graderecord(sno,sname,dormitory) values('511606','峰','228');insert into graderecord values('511607','明','240',90);insert into graderecord values('511608','楠','240',100);insert into graderecord values('511609','濤','240',67);insert into graderecord values('511610','博','240',75);insert into graderecord values('511611','錚','240',60);

下面查詢一下全部資料，然後查詢各個分區資料，代碼一起寫：

select * from graderecord;select * from graderecord partition(bujige);select * from graderecord partition(jige);select * from graderecord partition(youxiu);

全部資料如下：

 

不及格資料如下：

 

及格資料如下：

 

優秀資料如下：

說明：資料中有空值，Oracle機制會自動將其規划到maxvalue的分區中。

 

二：散列分區

散列分區是根據欄位的hash值進行均勻分布，儘可能的實現各分區所散列的資料相等。

還是剛才那個表，只不過把定界分割改換為散列分區，文法如下（刪除表之後重建）：

create table graderecord(  sno varchar2(10),  sname varchar2(20),  dormitory varchar2(3),  grade int)partition by hash(sno)(  partition p1,  partition p2,  partition p3);

 

插入實驗資料，與定界分割實驗插入的資料相同。

然後查詢分區資料：

select * from graderecord partition(p1);select * from graderecord partition(p2);select * from graderecord partition(p3);

p1分區的資料：

 

p2分區的資料：

 

p3分區的資料：

 

說明：散列分區即為雜湊分割，Oracle採用雜湊碼技術分區，具體分區如何由Oracle說的算，也可能我下一次搜尋就不是這個資料了。

 

三：列表分區

列表分區明確指定了根據某欄位的某個具體值進行分區，而不是像定界分割那樣根據欄位的值範圍來劃分的。

create table graderecord(  sno varchar2(10),  sname varchar2(20),  dormitory varchar2(3),  grade int)partition by list(dormitory)(  partition d229 values('229'),  partition d228 values('228'),  partition d240 values('240'))

以上根據宿舍來進行列表分區，插入與定界分割實驗相同的資料，做查詢如下：

select * from graderecord partition(d229);select * from graderecord partition(d228);select * from graderecord partition(d240);

d229分區所得資料如下：

 

d228分區所得資料如下：

 

d240分區所得資料如下：

 

四：複合分區 （範圍-散列分區，範圍-列表分區）

首先講範圍-散列分區。先聲明一下：列表分區不支援多列，但是定界分割和雜湊分割支援多列。

代碼如下：

create table graderecord(  sno varchar2(10),  sname varchar2(20),  dormitory varchar2(3),  grade int)partition by range(grade)subpartition by hash(sno,sname)(  partition p1 values less than(75)            (               subpartition sp1,subpartition sp2            ),  partition p2 values less than(maxvalue)            (               subpartition sp3,subpartition sp4            ));

以grade劃分範圍，然後以sno和sname劃分散列分區，當資料量大的時候散列分區則趨於“平均”。

插入資料：

insert into graderecord values('511601','魁','229',92);insert into graderecord values('511602','凱','229',62);insert into graderecord values('511603','東','229',26);insert into graderecord values('511604','亮','228',77);insert into graderecord values('511605','敬','228',47);insert into graderecord(sno,sname,dormitory) values('511606','峰','228');insert into graderecord values('511607','明','240',90);insert into graderecord values('511608','楠','240',100);insert into graderecord values('511609','濤','240',67);insert into graderecord values('511610','博','240',75);insert into graderecord values('511611','錚','240',60);insert into graderecord values('511612','狸','244',72);insert into graderecord values('511613','傑','244',88);insert into graderecord values('511614','萎','244',19);insert into graderecord values('511615','猥','244',65);insert into graderecord values('511616','丹','244',59);insert into graderecord values('511617','靳','244',95);

查詢如下：

select * from graderecord partition(p1);select * from graderecord partition(p2);select * from graderecord subpartition(sp1);select * from graderecord subpartition(sp2);select * from graderecord subpartition(sp3);select * from graderecord subpartition(sp4);

分區p1資料如下，本例中75分以下：

 

分區p2資料如下，本例中75分之上包括75分：

 

子分區sp1：

 

子分區sp2：

 

子分區sp3：

 

子分區sp4：

 

說明：當資料量越來越大時，雜湊分割的分區表中資料越來越趨於平衡。

 

下面講範圍-列表分區

範圍-列表分區有兩種創立方式，先說說沒有模板的建立方式，這個表我要重建：

create table MobileMessage( ACCT_MONTH VARCHAR2(6), -- 帳期 格式：年月 YYYYMM AREA_NO VARCHAR2(10), -- 地區號碼  DAY_ID VARCHAR2(2), -- 本月中的第幾天 格式 DD SUBSCRBID VARCHAR2(20), -- 使用者標識  SVCNUM VARCHAR2(30) -- 手機號碼)partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)(  partition p1 values less than('200705','012')  (    subpartition shangxun1 values('01','02','03','04','05','06','07','08','09','10'),    subpartition zhongxun1 values('11','12','13','14','15','16','17','18','19','20'),    subpartition xiaxun1 values('21','22','23','24','25','26','27','28','29','30','31')  ),  partition p2 values less than('200709','014')  (    subpartition shangxun2 values('01','02','03','04','05','06','07','08','09','10'),    subpartition zhongxun2 values('11','12','13','14','15','16','17','18','19','20'),    subpartition xiaxun2 values('21','22','23','24','25','26','27','28','29','30','31')  ),  partition p3 values less than('200801','016')  (    subpartition shangxun3 values('01','02','03','04','05','06','07','08','09','10'),    subpartition zhongxun3 values('11','12','13','14','15','16','17','18','19','20'),    subpartition xiaxun3 values('21','22','23','24','25','26','27','28','29','30','31')  ))

 

 插入實驗資料：

insert into MobileMessage values('200701','010','04','ghk001','13800000000');insert into MobileMessage values('200702','015','12','myx001','13633330000');insert into MobileMessage values('200703','015','24','hjd001','13300000000');insert into MobileMessage values('200704','010','04','ghk001','13800000000');insert into MobileMessage values('200705','010','04','ghk001','13800000000');insert into MobileMessage values('200705','011','18','sxl001','13222000000');insert into MobileMessage values('200706','011','21','sxl001','13222000000');insert into MobileMessage values('200706','012','11','tgg001','13800044400');insert into MobileMessage values('200707','010','04','ghk001','13800000000');insert into MobileMessage values('200708','012','24','tgg001','13800044400');insert into MobileMessage values('200709','014','29','zjj001','13100000000');insert into MobileMessage values('200710','014','29','zjj001','13100000000');insert into MobileMessage values('200711','014','29','zjj001','13100000000');insert into MobileMessage values('200711','013','30','wgc001','13444000000');insert into MobileMessage values('200712','013','30','wgc001','13444000000');insert into MobileMessage values('200712','010','30','ghk001','13800000000');insert into MobileMessage values('200801','015','22','myx001','13633330000');

查詢結果如下：

select * from MobileMessage;

 查詢結果如下：

 

 

分區p1查詢結果如下：

 

分區p2查詢結果如下：

 

子分區xiaxun2查詢結果如下：

 

說明：定界分割 range(A,B)的分區法則，定界分割都是 values less than(A,B)的，通常情況下以A為準，如果小於A的不用考慮B，直接插進去，如果等於A那麼考慮B，要是滿足B的話也插進去。

 

另一種範圍-列表分區，包含模板的（比較繁瑣，但是更加精確，處理海量儲存資料十分必要）：

create table MobileMessage( ACCT_MONTH VARCHAR2(6), -- 帳期 格式：年月 YYYYMM AREA_NO VARCHAR2(10), -- 地區號碼 DAY_ID VARCHAR2(2), -- 本月中的第幾天 格式 DD SUBSCRBID VARCHAR2(20), -- 使用者標識  SVCNUM VARCHAR2(30) -- 手機號碼)partition by range(ACCT_MONTH,AREA_NO) subpartition by list(DAY_ID)subpartition template( subpartition sub1 values('01'),subpartition sub2 values('02'), subpartition sub3 values('03'),subpartition sub4 values('04'), subpartition sub5 values('05'),subpartition sub6 values('06'), subpartition sub7 values('07'),subpartition sub8 values('08'), subpartition sub9 values('09'),subpartition sub10 values('10'), subpartition sub11 values('11'),subpartition sub12 values('12'), subpartition sub13 values('13'),subpartition sub14 values('14'), subpartition sub15 values('15'),subpartition sub16 values('16'), subpartition sub17 values('17'),subpartition sub18 values('18'), subpartition sub19 values('19'),subpartition sub20 values('20'), subpartition sub21 values('21'),subpartition sub22 values('22'), subpartition sub23 values('23'),subpartition sub24 values('24'), subpartition sub25 values('25'),subpartition sub26 values('26'), subpartition sub27 values('27'),subpartition sub28 values('28'), subpartition sub29 values('29'),subpartition sub30 values('30'), subpartition sub31 values('31'))(  partition p_0701_010 values less than('200701','011'),  partition p_0701_011 values less than('200701','012'),  partition p_0701_012 values less than('200701','013'),  partition p_0701_013 values less than('200701','014'),  partition p_0701_014 values less than('200701','015'),  partition p_0701_015 values less than('200701','016'),  partition p_0702_010 values less than('200702','011'),  partition p_0702_011 values less than('200702','012'),  partition p_0702_012 values less than('200702','013'),  partition p_0702_013 values less than('200702','014'),  partition p_0702_014 values less than('200702','015'),  partition p_0702_015 values less than('200702','016'),  partition p_0703_010 values less than('200703','011'),  partition p_0703_011 values less than('200703','012'),  partition p_0703_012 values less than('200703','013'),  partition p_0703_013 values less than('200703','014'),  partition p_0703_014 values less than('200703','015'),  partition p_0703_015 values less than('200703','016'),    partition p_0704_010 values less than('200704','011'),  partition p_0704_011 values less than('200704','012'),  partition p_0704_012 values less than('200704','013'),  partition p_0704_013 values less than('200704','014'),  partition p_0704_014 values less than('200704','015'),  partition p_0704_015 values less than('200704','016'),    partition p_0705_010 values less than('200705','011'),  partition p_0705_011 values less than('200705','012'),  partition p_0705_012 values less than('200705','013'),  partition p_0705_013 values less than('200705','014'),  partition p_0705_014 values less than('200705','015'),  partition p_0705_015 values less than('200705','016'),    partition p_0706_010 values less than('200706','011'),  partition p_0706_011 values less than('200706','012'),  partition p_0706_012 values less than('200706','013'),  partition p_0706_013 values less than('200706','014'),  partition p_0706_014 values less than('200706','015'),  partition p_0706_015 values less than('200706','016'),    partition p_0707_010 values less than('200707','011'),  partition p_0707_011 values less than('200707','012'),  partition p_0707_012 values less than('200707','013'),  partition p_0707_013 values less than('200707','014'),  partition p_0707_014 values less than('200707','015'),  partition p_0707_015 values less than('200707','016'),    partition p_0708_010 values less than('200708','011'),  partition p_0708_011 values less than('200708','012'),  partition p_0708_012 values less than('200708','013'),  partition p_0708_013 values less than('200708','014'),  partition p_0708_014 values less than('200708','015'),  partition p_0708_015 values less than('200708','016'),    partition p_0709_010 values less than('200709','011'),  partition p_0709_011 values less than('200709','012'),  partition p_0709_012 values less than('200709','013'),  partition p_0709_013 values less than('200709','014'),  partition p_0709_014 values less than('200709','015'),  partition p_0709_015 values less than('200709','016'),    partition p_0710_010 values less than('200710','011'),  partition p_0710_011 values less than('200710','012'),  partition p_0710_012 values less than('200710','013'),  partition p_0710_013 values less than('200710','014'),  partition p_0710_014 values less than('200710','015'),  partition p_0710_015 values less than('200710','016'),    partition p_0711_010 values less than('200711','011'),  partition p_0711_011 values less than('200711','012'),  partition p_0711_012 values less than('200711','013'),  partition p_0711_013 values less than('200711','014'),  partition p_0711_014 values less than('200711','015'),  partition p_0711_015 values less than('200711','016'),    partition p_0712_010 values less than('200712','011'),  partition p_0712_011 values less than('200712','012'),  partition p_0712_012 values less than('200712','013'),  partition p_0712_013 values less than('200712','014'),  partition p_0712_014 values less than('200712','015'),  partition p_0712_015 values less than('200712','016'),    partition p_0801_010 values less than('200801','011'),  partition p_0801_011 values less than('200801','012'),  partition p_0801_012 values less than('200801','013'),  partition p_0801_013 values less than('200801','014'),  partition p_0801_014 values less than('200801','015'),  partition p_0801_015 values less than('200801','016'),    partition p_other values less than(maxvalue, maxvalue));

這個是帶有模板子分區的，模板子分區詳細到月中的天。這種分區模式只要建立了分區就會自動建立子分區的。

插入上面不帶模板分區實驗相同的資料，隨機查詢分區資料：

 

查詢分區p_0701_010的資料：

select * from MobileMessage partition(p_0701_010);

查詢結果：

 

查詢子分區p_0701_010_sub4的資料：

select * from MobileMessage subpartition(p_0701_010_sub4);

 查詢結果如下：

 

查詢分區p_0706_011的資料：

select * from MobileMessage partition(p_0706_011);

 查詢結果如下：

 

查詢子分區p_0706_011_sub21的資料：

select * from MobileMessage subpartition(p_0706_011_sub21);

 查詢結果如下：

 

下面講講分區的維護操作：

(1)分裂分區，以第一個定界分割為例：

alter table graderecord split partition jige at(75)       into(partition keyi,partition lianghao); 

 把分區及格分裂為兩個分區：可以和良好。

 

(2)合并分區，以第一個定界分割為例：

alter table graderecord merge partitions keyi,lianghao into partition jige;

 把可以和良好兩個分區合并為及格。

 (3)添加分區，由於在定界分割上添加分區要求添加的分區範圍大於原有分區最大值，但原有分區最大值已經為maxvalue，故本處以第二個散列分區為例：

alter table graderecord add partition p4;

 給散列分區例子又增加了一個分區p4 。

 (4)刪除分區，文法：

alter table table_name drop partition partition_name;

 (5)截斷分區，清空分區中的資料

alter table table_name truncate partition partition_name;

 

說明：對待分區的操作同樣可以對待子分區，效果一樣。刪除一個分區會同時刪除其下的子分區。合并多個分區也會把他們的子分區自動合并。分裂分區時注意分裂點。

 

另外不帶模板子分區和帶有模板子分區的分區表操作的區別：帶有子分區模板的分區表在添加分區時候自動添加子分區，不帶模板子分區的分區表沒有這個功能；帶有子分區模板的分區表在更改分區時只需更改分區，不帶模板子分區的分區表在更改分區時一定注意連同子分區一起更改。