亚洲成年人在线,操逼在线播放,www.毛片动漫在线

廢話少說，直接講分區(qū)語法。

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

一：范圍分區(qū)

就是根據(jù)數(shù)據(jù)庫表中某一字段的值的范圍來劃分分區(qū)，例如：

Sql****代碼[[圖片上傳失敗...(image-51275c-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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) --優(yōu)秀
)

插入實驗數(shù)據(jù)：

Sql****代碼[[圖片上傳失敗...(image-a3e621-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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);

下面查詢一下全部數(shù)據(jù)，然后查詢各個分區(qū)數(shù)據(jù)，代碼一起寫：

Java****代碼[[圖片上傳失敗...(image-6ef77d-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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

全部數(shù)據(jù)如下：

[圖片上傳失敗...(image-ca8a8a-1515138077250)]

不及格數(shù)據(jù)如下：

[圖片上傳失敗...(image-6c4788-1515138077250)]

及格數(shù)據(jù)如下：

[圖片上傳失敗...(image-77258f-1515138077250)]

優(yōu)秀數(shù)據(jù)如下：

[圖片上傳失敗...(image-fa342a-1515138077249)]

說明：數(shù)據(jù)中有空值，****Oracle****機制會自動將其規(guī)劃到****maxvalue****的分區(qū)中。

二：散列分區(qū)

散列分區(qū)是根據(jù)字段的hash值進行均勻分布，盡可能的實現(xiàn)各分區(qū)所散列的數(shù)據(jù)相等。

還是剛才那個表，只不過把范圍分區(qū)改換為散列分區(qū)，語法如下（刪除表之后重建）：

Sql****代碼[[圖片上傳失敗...(image-9f1fbd-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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

插入實驗數(shù)據(jù)，與范圍分區(qū)實驗插入的數(shù)據(jù)相同。

然后查詢分區(qū)數(shù)據(jù)：

Sql****代碼[[圖片上傳失敗...(image-de2ed9-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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

p1分區(qū)的數(shù)據(jù)：

[圖片上傳失敗...(image-769f89-1515138077249)]

p2分區(qū)的數(shù)據(jù)：

[圖片上傳失敗...(image-2e99dc-1515138077249)]

p3分區(qū)的數(shù)據(jù)：

[圖片上傳失敗...(image-7551e0-1515138077249)]

說明：散列分區(qū)即為哈希分區(qū)，****Oracle****采用哈希碼技術(shù)分區(qū)，具體分區(qū)如何由****Oracle****說的算，也可能我下一次搜索就不是這個數(shù)據(jù)了。

三：列表分區(qū)

列表分區(qū)明確指定了根據(jù)某字段的某個具體值進行分區(qū)，而不是像范圍分區(qū)那樣根據(jù)字段的值范圍來劃分的。

Sql****代碼[[圖片上傳失敗...(image-3cf79a-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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')
)

以上根據(jù)宿舍來進行列表分區(qū)，插入與范圍分區(qū)實驗相同的數(shù)據(jù)，做查詢?nèi)缦拢?/p>

Sql****代碼[[圖片上傳失敗...(image-b3b475-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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

d229分區(qū)所得數(shù)據(jù)如下：

[圖片上傳失敗...(image-497d44-1515138077249)]

d228分區(qū)所得數(shù)據(jù)如下：

[圖片上傳失敗...(image-a49ba4-1515138077249)]

d240分區(qū)所得數(shù)據(jù)如下：

[圖片上傳失敗...(image-aea166-1515138077249)]

四：復合分區(qū)（范圍-散列分區(qū)，范圍-列表分區(qū)）

首先講范圍-散列分區(qū)。先聲明一下：列表分區(qū)不支持多列，但是范圍分區(qū)和哈希分區(qū)支持多列。

代碼如下：

Sql****代碼[[圖片上傳失敗...(image-8de749-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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劃分散列分區(qū)，當數(shù)據(jù)量大的時候散列分區(qū)則趨于“平均”。

插入數(shù)據(jù)：

Sql****代碼[[圖片上傳失敗...(image-d4cea5-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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);

查詢?nèi)缦拢?/p>

Sql****代碼[[圖片上傳失敗...(image-6eead3-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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);

分區(qū)p1數(shù)據(jù)如下，本例中75分以下：

[圖片上傳失敗...(image-3dece-1515138077247)]

分區(qū)p2數(shù)據(jù)如下，本例中75分之上包括75分：

[圖片上傳失敗...(image-48a329-1515138077247)]

子分區(qū)sp1：

[圖片上傳失敗...(image-9d2724-1515138077247)]

子分區(qū)sp2：

[圖片上傳失敗...(image-a23043-1515138077247)]

子分區(qū)sp3：

[圖片上傳失敗...(image-2ec84-1515138077247)]

子分區(qū)sp4：

[圖片上傳失敗...(image-c39cfb-1515138077247)]

說明：當數(shù)據(jù)量越來越大時，哈希分區(qū)的分區(qū)表中數(shù)據(jù)越來越趨于平衡。

下面講范圍-列表分區(qū)

范圍-列表分區(qū)有兩種創(chuàng)立方式，先說說沒有模板的創(chuàng)建方式，這個表我要重建：

Sql****代碼[[圖片上傳失敗...(image-a930fa-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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')

)
)

插入實驗數(shù)據(jù)：

Sql****代碼[[圖片上傳失敗...(image-d1b759-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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');

查詢結(jié)果如下：

Sql****代碼[[圖片上傳失敗...(image-e9aade-1515138077228)]](javascript:void() ""收藏這段代碼" ")

select * from MobileMessage;

[圖片上傳失敗...(image-dfa7af-1515138077231)]

分區(qū)p1查詢結(jié)果如下：

[圖片上傳失敗...(image-2bb670-1515138077243)]

分區(qū)p2查詢結(jié)果如下：

[圖片上傳失敗...(image-58ad36-1515138077243)]

子分區(qū)xiaxun2查詢結(jié)果如下：

[圖片上傳失敗...(image-87abbb-1515138077243)]

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

另一種范圍-列表分區(qū)，包含模板的（比較繁瑣，但是更加精確，處理海量存儲數(shù)據(jù)十分必要）：

Sql****代碼[[圖片上傳失敗...(image-2d5386-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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)
);

這個是帶有模板子分區(qū)的，模板子分區(qū)詳細到月中的天。這種分區(qū)模式只要建立了分區(qū)就會自動創(chuàng)建子分區(qū)的。

插入上面不帶模板分區(qū)實驗相同的數(shù)據(jù)，隨機查詢分區(qū)數(shù)據(jù)：

查詢分區(qū)p_0701_010的數(shù)據(jù)：

Sql****代碼[[圖片上傳失敗...(image-51a698-1515138077228)]](javascript:void() ""收藏這段代碼" ")

select * from MobileMessage partition(p_0701_010);

查詢結(jié)果：

[圖片上傳失敗...(image-6f073-1515138077237)]

查詢子分區(qū)p_0701_010_sub4的數(shù)據(jù)：

Sql****代碼[[圖片上傳失敗...(image-3416d6-1515138077228)]](javascript:void() ""收藏這段代碼" ")

select * from MobileMessage subpartition(p_0701_010_sub4);

查詢結(jié)果如下：

[圖片上傳失敗...(image-31726-1515138077237)]

查詢分區(qū)p_0706_011的數(shù)據(jù)：

Sql****代碼[[圖片上傳失敗...(image-bd151-1515138077228)]](javascript:void() ""收藏這段代碼" ")

select * from MobileMessage partition(p_0706_011);

查詢結(jié)果如下：

[圖片上傳失敗...(image-448b44-1515138077237)]

查詢子分區(qū)p_0706_011_sub21的數(shù)據(jù)：

Sql****代碼[[圖片上傳失敗...(image-cb3b7b-1515138077228)]](javascript:void() ""收藏這段代碼" ")

select * from MobileMessage subpartition(p_0706_011_sub21);

查詢結(jié)果如下：

[圖片上傳失敗...(image-717fbf-1515138077237)]

下面講講分區(qū)的維護操作：

(1)分裂分區(qū)，以第一個范圍分區(qū)為例：

Sql****代碼[[圖片上傳失敗...(image-3b7673-1515138077228)]](javascript:void() ""收藏這段代碼" ")

alter table graderecord split partition jige at(75)

  **into**(partition keyi,partition lianghao);

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

(2)合并分區(qū)，以第一個范圍分區(qū)為例：

Sql****代碼[[圖片上傳失敗...(image-37ccc1-1515138077228)]](javascript:void() ""收藏這段代碼" ")

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

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

(3)添加分區(qū)，由于在范圍分區(qū)上添加分區(qū)要求添加的分區(qū)范圍大于原有分區(qū)最大值，但原有分區(qū)最大值已經(jīng)為maxvalue，故本處以第二個散列分區(qū)為例：

Sql****代碼[[圖片上傳失敗...(image-b2ff6c-1515138077228)]](javascript:void() ""收藏這段代碼" ")

alter table graderecord add partition p4;

給散列分區(qū)例子又增加了一個分區(qū)p4 。

(4)刪除分區(qū)，語法：

Sql****代碼[[圖片上傳失敗...(image-33d6e2-1515138077228)]](javascript:void() ""收藏這段代碼" ")

alter table table_name drop partition partition_name;

(5)截斷分區(qū)，清空分區(qū)中的數(shù)據(jù)

Sql****代碼[[圖片上傳失敗...(image-f8ec27-1515138077228)]](javascript:void() ""收藏這段代碼" ")

alter table table_name truncate partition partition_name;

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

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

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av

Oracle表分區(qū)大全

Oracle表分區(qū)大全

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九 欧美,1769亚洲,黄色成人av

Oracle表分區(qū)大全

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容

色偷偷精品伊人,欧洲久久精品,欧美综合婷婷骚逼,国产AV主播,国产最新探花在线,九色在线视频一区,伊人大交九欧美,1769亚洲,黄色成人av