Oracle表分區

Oracle表分區

Oracle表分區分為四種：范圍分區，散列分區，列表分區和復合分區。

 

一：范圍分區

就是根據數據庫表中某一字段的值的范圍來劃分分區，例如：

按時間分區表創建

create table t_test (
pk_id number(30) not null,
add_date_time DATE,
constraintPK_T_TEST primary key (pk_id)
)
PARTITION BY RANGE (add_date_time)
(
PARTITION t_test_2013_less VALUES LESS THAN (TO_DATE('2013-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITION t_test_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','yyyy-mm-ddhh24:mi:ss')) TABLESPACE TS_MISPS,
PARTITION t_test_2014 VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')) TABLESPACE TS_MISPS
)

按分數分區表創建

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;

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

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