您現在的位置是：首頁 > 攝影首頁 攝影

詳解Oracle表分割槽：範圍分割槽，雜湊分割槽，列表分割槽和複合分割槽

由波波說運維發表于攝影2021-08-27

簡介insert into graderecord values（‘511607’，‘明’，‘240’，90）

分割槽是什麼意思

概述

當生產環境中表的資料量不斷增大，查詢資料的速度就會變慢，應用程式的效能就會下降，這時我們就應該考慮對錶進行分割槽。表進行分割槽後，邏輯上表仍然是一張完整的表，只是將表中的資料在物理上存放到多個表空間（物理檔案上），這樣查詢資料時，不至於每次都掃描整張表，其實就是查詢的範圍少了，IO也分散了。

下面介紹下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） ——優秀）

插入測試資料：

下面查詢一下全部資料，然後查詢各個分割槽資料：

select * from graderecord； select * from graderecord partition（bujige）； select * from graderecord partition（jige）； select * from graderecord partition（youxiu）；

全部資料如下：

不及格資料如下：

及格資料如下：

優秀資料如下：

說明：因為資料中有空值，Oracle機制會自動將其規劃到maxvalue的分割槽中。

二、雜湊分割槽

雜湊分割槽是根據欄位的hash值進行均勻分佈，儘可能的實現各分割槽所雜湊的資料相等。

還是剛才那個表，只不過把範圍分割槽改換為雜湊分割槽，語法如下（刪除表之後重建）：

drop table graderecord purge；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說的算，也可能我下一次搜尋就不是這個資料了。

三、列表分割槽

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

drop table graderecord purge；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分割槽所得資料如下：

四、複合分割槽

（範圍-雜湊分割槽，範圍-列表分割槽）

1、範圍-雜湊分割槽

列表分割槽不支援多列，但是範圍分割槽和雜湊分割槽支援多列。

drop table graderecord purge；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）； commit；

查詢如下：

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：

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

2、範圍-列表分割槽

範圍-列表分割槽有兩種建立方式，區別在有沒模板。

2.1、沒有模板的建立方式

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（‘201805’，‘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（‘201809’，‘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（‘201901’，‘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（‘201801’，‘010’，‘04’，‘ghk001’，‘13800000000’）； insert into MobileMessage values（‘201802’，‘015’，‘12’，‘myx001’，‘13633330000’）； insert into MobileMessage values（‘201803’，‘015’，‘24’，‘hjd001’，‘13300000000’）； insert into MobileMessage values（‘201804’，‘010’，‘04’，‘ghk001’，‘13800000000’）； insert into MobileMessage values（‘201805’，‘010’，‘04’，‘ghk001’，‘13800000000’）； insert into MobileMessage values（‘201805’，‘011’，‘18’，‘sxl001’，‘13222000000’）； insert into MobileMessage values（‘201806’，‘011’，‘21’，‘sxl001’，‘13222000000’）； insert into MobileMessage values（‘201806’，‘012’，‘11’，‘tgg001’，‘13800044400’）； insert into MobileMessage values（‘201807’，‘010’，‘04’，‘ghk001’，‘13800000000’）； insert into MobileMessage values（‘201808’，‘012’，‘24’，‘tgg001’，‘13800044400’）； insert into MobileMessage values（‘201809’，‘014’，‘29’，‘zjj001’，‘13100000000’）； insert into MobileMessage values（‘201810’，‘014’，‘29’，‘zjj001’，‘13100000000’）； insert into MobileMessage values（‘201811’，‘014’，‘29’，‘zjj001’，‘13100000000’）； insert into MobileMessage values（‘201811’，‘013’，‘30’，‘wgc001’，‘13444000000’）； insert into MobileMessage values（‘201812’，‘013’，‘30’，‘wgc001’，‘13444000000’）； insert into MobileMessage values（‘201812’，‘010’，‘30’，‘ghk001’，‘13800000000’）； insert into MobileMessage values（‘201901’，‘015’，‘22’，‘myx001’，‘13633330000’）； commit；

查詢分割槽資料如下：

select * from MobileMessage； select * from MobileMessage partition（p1）；select * from MobileMessage partition（p2）；select * from MobileMessage subpartition（xiaxun2）；select * from MobileMessage partition（p3）；

分割槽p1查詢結果如下：

分割槽p2查詢結果如下：

子分割槽xiaxun2查詢結果如下：

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

2。2、範圍-列表分割槽

包含模板的（比較繁瑣，但是更加精確，處理海量儲存資料十分必要）：

create table MobileMessage_model （ 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_1801_010 values less than（‘201801’，‘011’）， partition p_1801_011 values less than（‘201801’，‘012’）， partition p_1801_012 values less than（‘201801’，‘013’）， partition p_1801_013 values less than（‘201801’，‘014’）， partition p_1801_014 values less than（‘201801’，‘015’）， partition p_1801_015 values less than（‘201801’，‘016’）， partition p_1802_010 values less than（‘201802’，‘011’）， partition p_1802_011 values less than（‘201802’，‘012’）， partition p_1802_012 values less than（‘201802’，‘013’）， partition p_1802_013 values less than（‘201802’，‘014’）， partition p_1802_014 values less than（‘201802’，‘015’）， partition p_1802_015 values less than（‘201802’，‘016’）， partition p_1803_010 values less than（‘201803’，‘011’）， partition p_1803_011 values less than（‘201803’，‘012’）， partition p_1803_012 values less than（‘201803’，‘013’）， partition p_1803_013 values less than（‘201803’，‘014’）， partition p_1803_014 values less than（‘201803’，‘015’）， partition p_1803_015 values less than（‘201803’，‘016’）， partition p_1804_010 values less than（‘201804’，‘011’）， partition p_1804_011 values less than（‘201804’，‘012’）， partition p_1804_012 values less than（‘201804’，‘013’）， partition p_1804_013 values less than（‘201804’，‘014’）， partition p_1804_014 values less than（‘201804’，‘015’）， partition p_1804_015 values less than（‘201804’，‘016’）， partition p_1805_010 values less than（‘201805’，‘011’）， partition p_1805_011 values less than（‘201805’，‘012’）， partition p_1805_012 values less than（‘201805’，‘013’）， partition p_1805_013 values less than（‘201805’，‘014’）， partition p_1805_014 values less than（‘201805’，‘015’）， partition p_1805_015 values less than（‘201805’，‘016’）， partition p_1806_010 values less than（‘201806’，‘011’）， partition p_1806_011 values less than（‘201806’，‘012’）， partition p_1806_012 values less than（‘201806’，‘013’）， partition p_1806_013 values less than（‘201806’，‘014’）， partition p_1806_014 values less than（‘201806’，‘015’）， partition p_1806_015 values less than（‘201806’，‘016’）， partition p_1807_010 values less than（‘201807’，‘011’）， partition p_1807_011 values less than（‘201807’，‘012’）， partition p_1807_012 values less than（‘201807’，‘013’）， partition p_1807_013 values less than（‘201807’，‘014’）， partition p_1807_014 values less than（‘201807’，‘015’）， partition p_1807_015 values less than（‘201807’，‘016’）， partition p_1808_010 values less than（‘201808’，‘011’）， partition p_1808_011 values less than（‘201808’，‘012’）， partition p_1808_012 values less than（‘201808’，‘013’）， partition p_1808_013 values less than（‘201808’，‘014’）， partition p_1808_014 values less than（‘201808’，‘015’）， partition p_1808_015 values less than（‘201808’，‘016’）， partition p_1809_010 values less than（‘201809’，‘011’）， partition p_1809_011 values less than（‘201809’，‘012’）， partition p_1809_012 values less than（‘201809’，‘013’）， partition p_1809_013 values less than（‘201809’，‘014’）， partition p_1809_014 values less than（‘201809’，‘015’）， partition p_1809_015 values less than（‘201809’，‘016’）， partition p_1810_010 values less than（‘201810’，‘011’）， partition p_1810_011 values less than（‘201810’，‘012’）， partition p_1810_012 values less than（‘201810’，‘013’）， partition p_1810_013 values less than（‘201810’，‘014’）， partition p_1810_014 values less than（‘201810’，‘015’）， partition p_1810_015 values less than（‘201810’，‘016’）， partition p_1811_010 values less than（‘201811’，‘011’）， partition p_1811_011 values less than（‘201811’，‘012’）， partition p_1811_012 values less than（‘201811’，‘013’）， partition p_1811_013 values less than（‘201811’，‘014’）， partition p_1811_014 values less than（‘201811’，‘015’）， partition p_1811_015 values less than（‘201811’，‘016’）， partition p_1812_010 values less than（‘201812’，‘011’）， partition p_1812_011 values less than（‘201812’，‘012’）， partition p_1812_012 values less than（‘201812’，‘013’）， partition p_1812_013 values less than（‘201812’，‘014’）， partition p_1812_014 values less than（‘201812’，‘015’）， partition p_1812_015 values less than（‘201812’，‘016’）， partition p_1901_010 values less than（‘201901’，‘011’）， partition p_1901_011 values less than（‘201901’，‘012’）， partition p_1901_012 values less than（‘201901’，‘013’）， partition p_1901_013 values less than（‘201901’，‘014’）， partition p_1901_014 values less than（‘201901’，‘015’）， partition p_1901_015 values less than（‘201901’，‘016’）， partition p_other values less than（maxvalue， maxvalue））；

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

插入上面不帶模板分割槽實驗相同的資料

insert into MobileMessage_model values（‘201801’，‘010’，‘04’，‘ghk001’，‘13800000000’）； insert into MobileMessage_model values（‘201802’，‘015’，‘12’，‘myx001’，‘13633330000’）； insert into MobileMessage_model values（‘201803’，‘015’，‘24’，‘hjd001’，‘13300000000’）； insert into MobileMessage_model values（‘201804’，‘010’，‘04’，‘ghk001’，‘13800000000’）； insert into MobileMessage_model values（‘201805’，‘010’，‘04’，‘ghk001’，‘13800000000’）； insert into MobileMessage_model values（‘201805’，‘011’，‘18’，‘sxl001’，‘13222000000’）； insert into MobileMessage_model values（‘201806’，‘011’，‘21’，‘sxl001’，‘13222000000’）； insert into MobileMessage_model values（‘201806’，‘012’，‘11’，‘tgg001’，‘13800044400’）； insert into MobileMessage_model values（‘201807’，‘010’，‘04’，‘ghk001’，‘13800000000’）； insert into MobileMessage_model values（‘201808’，‘012’，‘24’，‘tgg001’，‘13800044400’）； insert into MobileMessage_model values（‘201809’，‘014’，‘29’，‘zjj001’，‘13100000000’）； insert into MobileMessage_model values（‘201810’，‘014’，‘29’，‘zjj001’，‘13100000000’）； insert into MobileMessage_model values（‘201811’，‘014’，‘29’，‘zjj001’，‘13100000000’）； insert into MobileMessage_model values（‘201811’，‘013’，‘30’，‘wgc001’，‘13444000000’）；insert into MobileMessage_model values（‘201812’，‘013’，‘30’，‘wgc001’，‘13444000000’）；insert into MobileMessage_model values（‘201812’，‘010’，‘30’，‘ghk001’，‘13800000000’）； insert into MobileMessage_model values（‘201901’，‘015’，‘22’，‘myx001’，‘13633330000’）； commit；

查詢分割槽p_1801_010的資料：

select * from MobileMessage_model partition（p_1801_010）；