2萬字詳解Oracle分割槽表技術，太頂了

大家好，我是哪吒，最近專案有一個新的需求，按月建表，按天分割槽。

不都是分庫分表嗎？怎麼又來個分割槽？

讓我們一起，一探究竟，深入理解一下Oracle分割槽表技術，實現快速入門，豐富個人簡歷，提高面試level，給自己增加一點談資，秒變面試小達人，BAT不是夢。

三分鐘你將學會：

1. Oracle是如何儲存資料的？
2. Oracle分割槽是什麼？
3. 何時分割槽？
4. 分割槽表的分類都有哪些？
5. Oracle分割槽技術實戰演練

一、Oracle是如何儲存資料的？

1、邏輯儲存與物理儲存

在國企或者一線大廠，一般都會選擇使用Oracle資料庫，程式透過mybatis等持久層框架訪問Oracle資料庫，指定表空間，表空間內包含若干張表，表中存有行資料，行資料以行片段的形式儲存在資料庫塊中，① 當插入的行太大，無法裝入單個塊時；② 或因為更新的緣故，導致現有行超出了當前空間時 -> 就會發生整個行不儲存在一個位置的情況。

Oracle在邏輯上將資料儲存在表空間中，在物理上將資料儲存在資料檔案中。

表空間包括若干個資料檔案，這些表空間使用與執行Oracle軟體的作業系統一致的物理結構。資料庫的資料儲存在構成資料庫表空間的資料檔案中。

臨時檔案是一個臨時表空間的檔案；它是透過TEMPFILE選項建立的。臨時表空間不包含表，通常用於排序。

2、進一步分析它們之間的關係

1. 資料庫包含若干個表空間（邏輯儲存單元）；
2. 每一個表空間包含很多的Oracle 邏輯資料塊，邏輯資料塊的大小一般在2 KB 至32 KB，預設8 KB；
3. Oracle 資料塊是邏輯I/O的最小單位；
4. 特定數目的相鄰邏輯塊構成了“區”；
5. 特定邏輯結構分配的一組區構成了一個段;

3、Oracle邏輯資料塊

資料庫塊包含塊頭、行資料、可用空間。

（1）塊頭

塊頭包含段型別（如表或索引）、資料塊地址、表目錄、行目錄和事務處理插槽。

每個插槽的大小為24 位元組，修改塊中的行時會使用這些插槽。

（2）行資料

塊中行的實際資料。

（3）可用空間

可用空間位於塊的中部，允許頭和行資料空間在必要時進行增長。當插入新行或用更大的值更新現有行的列時，行資料會佔用可用空間。

（4）致塊頭增長的原因有：

1. 行目錄需要更多的行條目；
2. 需要的事務處理插槽數多於最初配置的數目；

塊中的可用空間最初是相鄰的。但是，刪除和更新操作可能會使塊中的可用空間變成碎片，需要時Oracle 伺服器會接合塊中的空閒空間。

二、Oracle分割槽表技術

分割槽是指表和索引可以被分成若干個部分，它們擁有相同的邏輯屬性和資料結構。所有分割槽的欄位和索引都是一樣的。

分割槽表是將表資料分為若干個可以被單獨管理的片，每個片就是一個分割槽，分一個分割槽都可以擁有自己的物理屬性，比如表空間、事務槽、儲存引數、最小區段數等，透過建分割槽語句指定，提升可用性和儲存效率。

每個分割槽可以被單獨管理，降低管理成本和備份成本，提高容錯率，避免“一榮既榮，一損俱損”的問題。

1、分割槽表的優缺點

（1）優點

1. 可以透過指定分割槽提高查詢效能；
2. 提高容錯率，避免“一榮既榮，一損俱損”的問題；
3. 降低管理成本；
4. 降低備份成本；

（2）缺點

普通表和分割槽表不能直接轉換，可以透過資料遷移，再重新命名的方式實現，需要重建約束、索引，在建立表時可以新增關鍵字“parallel compress”並行執行，提高效率，下面會透過SQL例項介紹。

2、何時分割槽？

單表的資料量如果過大，會影響SQL的讀寫效能，我們可以透過分庫分表的方式解決表效能的問題，Oracle的分割槽表是將一張大表在物理上分成幾個較小的表，從邏輯上看仍然是一張完整的表。這樣，每次DML操作只考慮其中一張分割槽表即可。

那麼，臨界點是多少呢？

1. 數量量超過500萬且空間佔用超過2GB的時候必須分割槽；
2. 數量量高於100萬，低於500萬時建議分割槽；

注意：單個分割槽的資料可以超過500萬，但儲存空間不建議超過2GB。

三、分割槽相關的資料字典

根據資料字典表的字首不同，可查詢的內容及許可權有所差異：

1. DBA_開頭：需要DBA許可權，查詢全庫內容；
2. ALL_開頭：查詢當前使用者許可權下的內容；
3. USER_開頭：查詢當前使用者下的內容；

以下是分割槽表的一些相關字典表，字首是“DBA_”、“ALL_”、“USER_”；

1. 分割槽表資訊字典表：*_PART_TABLES；
2. 分割槽資訊字典表：*_TAB_PARTITIONS；
3. 子分割槽資訊字典表：*_TAB_SUBPARTITIONS；
4. 分割槽表的分割槽欄位資訊字典表：*_PART_KEY_COLUMNS；

四、分割槽表的分類

1、範圍分割槽

將資料基於範圍對映到每一個分割槽，這個範圍是由建立分割槽表時指定的分割槽鍵決定。

一般選取id或者時間作為範圍分割槽的分割槽鍵。

（1）按月建表，按天分割槽

create table WORKER_202301
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by range (SAVE_DATE)
(
 partition WORKER20230129 values less than (TO_DATE('2023-01-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace MYSPACE
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 80K
      minextents 1
      maxextents unlimited
    ),
  partition WORKER20230130 values less than (TO_DATE('2023-01-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace MYSPACE
    pctfree 10
    initrans 1
    maxtrans 255
    storage
    (
      initial 80K
      minextents 1
      maxextents unlimited
    )
);
create index IDX_WORKER_ID202301 on WORKER_202301 (ID)
  local;
create index IDX_WORKER_ID_NAME202301 on WORKER_202301 (ID, NAME)
  local;

（2）建表語句分析

1. NLS_CALENDAR=GREGORIAN：用於指定Oracle所使用的日曆體系，其取值為Arabic Hijrah、English Hijrah、Gregorian、Japanese Imperial、Persian、ROC Official、Thai Buddha。
2. tablespace：指定表空間；
3. pctfree：塊保留10%的空間留給更新該塊資料使用
4. initrans：初始化事務槽的個數；
5. maxtrans：最大事務槽的個數；
6. storage：儲存引數
7. initial：區段(extent)一次擴充套件64k
8. minextents：最小區段數
9. maxextents unlimited：最大區段無限制

❝

每個塊都有一個塊首部。這個塊首部中有一個事務表。事務表中會建立一些條目來描述哪些事務將塊上的哪些行/元素鎖定。這個事務表的初始大小由物件的INITRANS 設定指定。對於表，這個值預設為2（索引的INITRANS 也預設為2）。事務表會根據需要動態擴充套件，最大達到MAXTRANS 個條目（假設塊上有足夠的自由空間）。所分配的每個事務條目需要佔用塊首部中的23～24 位元組的儲存空間。注意，對於Oracle 10g，MAXTRANS 則會忽略，所有段的MAXTRANS 都是255。

由於oracle塊裡有一個PCT_free的概念，即oracle會預留塊大小的10%作為緩衝，當修改oracle的事務增加時，事務槽向下增長，當更新oracle塊的資料時，資料向上增長，PCT_free的空間被壓縮。

❞

local索引是針對單個分割槽表的索引；

在對分割槽表進行維護操作時需檢查索引是否失效，索引失效除了會導致查詢慢，還會導致資料寫入失敗，在ALTER TBALE語法中也可以新增關鍵字“UPDATE INDEXES”避免維護表時索引失效。

（3）插入三條資料

insert into worker_202301 (id,name,technology,save_date) values ('1','哪吒','java',to_date('2023/1/29 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202301 (id,name,technology,save_date) values ('2','雲韻','java',to_date('2023/1/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202301 (id,name,technology,save_date) values ('3','美杜莎','Python',to_date('2023/1/30 00:45:19','yyyy-MM-dd hh24:mi:ss'));

（4）查詢指定分割槽

select * from worker_202301 
partition (WORKER20230129);

跨分割槽查詢時，查詢每個分割槽的資料後使用UNION ALL關鍵字做集合查詢，提高查詢效率。

（5）新增分割槽

ALTER TABLE worker_202301 
ADD PARTITION WORKER20230131 
VALUES LESS THAN 
(TO_DATE(' 2023-02-01 00:00:00', 
'SYYYY-MM-DD HH24:MI:SS', 
'NLS_CALENDAR=GREGORIAN'));

（6）刪除分割槽

ALTER TABLE worker_202301 
DROP PARTITION WORKER20230131;

2、列表分割槽

（1）列表分割槽適用於一個欄位只有「固定」的幾個值，比如型別、月份、課程等。

create table WORKER_202302
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by list (technology)
(
 partition technology_java values ('java'),
 partition technology_python values ('python'),
 partition technology_c values ('c')
);

create index IDX_WORKER_ID202301 on WORKER_202301 (ID)
  local;
create index IDX_WORKER_ID_NAME202301 on WORKER_202301 (ID, NAME)
  local;

（2）插入三條資料

insert into worker_202302 (id,name,technology,save_date) values ('1','哪吒','java',to_date('2023/2/1 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202302 (id,name,technology,save_date) values ('2','雲韻','java',to_date('2023/2/1 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202302 (id,name,technology,save_date) values ('3','美杜莎','python',to_date('2023/2/2 00:45:19','yyyy-MM-dd hh24:mi:ss'));

（3）查詢列表分割槽資料

（4）如果一個分割槽的資料量不大，可以合併分割槽

create table WORKER_202303
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by list (technology)
(
 partition technology_java values ('java','python'),
 partition technology_c values ('c','c++')
);

3、雜湊分割槽

範圍分割槽和列表分割槽都是使用某一個欄位進行分割槽，此欄位的分割槽度大才行，但也會產生諸多問題，比如上述的按技術列表分割槽，現階段，Java開發人員明顯高於C，此時就會導致分割槽不均勻的問題。

此時，hash分割槽閃亮登場，hash分割槽的好處是讓分割槽更均勻一些。

（1）上面的諸多引數都可以省略。

create table WORKER_202304
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by hash (id)
(
 partition worker_id_1,
 partition worker_id_2,
 partition worker_id_3,
 partition worker_id_4,
);

此時，插入200條資料，id從1到200，驗證一下是否均勻。資料條數分別是51、55、61、33。

（2）何時使用hash分割槽？

1. 分割槽鍵的值最好是連續的；
2. 分割槽數量最好是2的n次方，對hash運算更加友好；

（3）新增hash分割槽：

ALTER TABLE worker_202304 
ADD PARTITION worker_id_5;

剛建立好分割槽，worker_id_5就有資料了，why？匪夷所思。

新增分割槽時，所有資料會重新計算hash值，重新分配到不同的分割槽表中。

（4）不可以刪除hash分割槽

4、範圍列表組合分割槽

（1）建表語句

create table WORKER_202305
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by range (SAVE_DATE) SUBPARTITION BY LIST (technology) 
(
  partition WORKER20230529 values less than (TO_DATE(' 2023-05-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    SUBPARTITION technology_java_29 values('java'),
     SUBPARTITION technology_python_29 values('python'),
     SUBPARTITION technology_c_29 values('c')
  ),
  partition WORKER20230530 values less than (TO_DATE(' 2023-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    SUBPARTITION technology_java_30 values('java'),
     SUBPARTITION technology_python_30 values('python'),
     SUBPARTITION technology_c_30 values('c')
  )
);

（2）插入8條資料

insert into worker_202305 (id,name,technology,save_date) values ('101','哪吒','java',to_date('2023/5/29 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202305 (id,name,technology,save_date) values ('102','雲韻','java',to_date('2023/5/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202305 (id,name,technology,save_date) values ('103','美杜莎','java',to_date('2023/5/29 00:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202305 (id,name,technology,save_date) values ('104','哪吒','java',to_date('2023/5/29 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202305 (id,name,technology,save_date) values ('105','雲韻1','python',to_date('2023/5/30 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202305 (id,name,technology,save_date) values ('106','美杜莎1','python',to_date('2023/5/30 00:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202305 (id,name,technology,save_date) values ('107','哪吒1','python',to_date('2023/5/30 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202305 (id,name,technology,save_date) values ('108','雲韻1','python',to_date('2023/5/30 22:46:19','yyyy-MM-dd hh24:mi:ss'));

（3）查詢分割槽資料

select count(1) from worker_202305 PARTITION (WORKER20230529);//4條 ok
select count(1) from worker_202305 PARTITION (WORKER20230530);//4條 ok
select count(1) from worker_202305 SUBPARTITION (TECHNOLOGY_JAVA_29);//4條 ok
select count(1) from worker_202305 SUBPARTITION (TECHNOLOGY_JAVA_30);//0條 ok

（4）新增主分割槽

ALTER TABLE worker_202305 ADD PARTITION WORKER20230531 values less than (TO_DATE(' 2023-06-1 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
    SUBPARTITION technology_java_31 values('java'),
     SUBPARTITION technology_python_31 values('python'),
     SUBPARTITION technology_c_31 values('c')
  )

為WORKER20230529新增子分割槽technology_go_29：

ALTER TABLE worker_202305 
MODIFY PARTITION WORKER20230529 
ADD SUBPARTITION technology_go_29 values('go'); 

（5）刪除子分割槽

ALTER TABLE worker_202305 
DROP SUBPARTITION technology_go_29;

5、範圍雜湊組合分割槽

（1）建表語句

create table WORKER_202306
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by range (SAVE_DATE) SUBPARTITION BY HASH (id) 
(
  partition WORKER20230628 values less than (TO_DATE(' 2023-06-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
   SUBPARTITION worker_id_1,
     SUBPARTITION worker_id_2,
     SUBPARTITION worker_id_3,
   SUBPARTITION worker_id_4
  ),
  partition WORKER20230629 values less than (TO_DATE(' 2023-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
  (
  SUBPARTITION worker_id_5,
     SUBPARTITION worker_id_6,
     SUBPARTITION worker_id_7,
   SUBPARTITION worker_id_8
  )
);

（2）插入10條資料

insert into worker_202306 (id,name,technology,save_date) values ('101','哪吒','java',to_date('2023/6/28 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('102','雲韻','java',to_date('2023/6/28 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('103','美杜莎','java',to_date('2023/6/28 00:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('104','哪吒','java',to_date('2023/6/28 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('105','雲韻1','python',to_date('2023/6/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('106','美杜莎1','python',to_date('2023/6/29 00:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('107','哪吒1','python',to_date('2023/6/29 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('108','雲韻1','python',to_date('2023/6/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('109','雲韻1','python',to_date('2023/6/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202306 (id,name,technology,save_date) values ('110','雲韻1','python',to_date('2023/6/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));

（3）查詢分割槽資料

select count(1) from worker_202306 PARTITION (WORKER20230628);
select count(1) from worker_202306 PARTITION (WORKER20230629);
select count(1) from worker_202306 SUBPARTITION (worker_id_1);
select count(1) from worker_202306 SUBPARTITION (worker_id_2);
select count(1) from worker_202306 SUBPARTITION (worker_id_5);
select count(1) from worker_202306 SUBPARTITION (worker_id_6);

由於hash分割槽的緣故，資料分佈不均勻。

6、列表雜湊組合分割槽

（1）建表語句

create table WORKER_202307
(
  id        VARCHAR2(100) not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by list (technology) SUBPARTITION BY HASH (id) 
(
  partition technology_java values ('java')
  (
   SUBPARTITION worker_id_1,
     SUBPARTITION worker_id_2,
     SUBPARTITION worker_id_3,
   SUBPARTITION worker_id_4
    ),
  partition technology_python values ('python')
  (
   SUBPARTITION worker_id_5,
     SUBPARTITION worker_id_6,
     SUBPARTITION worker_id_7,
   SUBPARTITION worker_id_8
    )
);

（2）插入10條資料

insert into worker_202307 (id,name,technology,save_date) values ('101','哪吒','java',to_date('2023/7/28 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('102','雲韻','java',to_date('2023/7/28 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('103','美杜莎','java',to_date('2023/7/28 00:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('104','哪吒','java',to_date('2023/7/28 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('105','雲韻1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('106','美杜莎1','python',to_date('2023/7/29 00:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('107','哪吒1','python',to_date('2023/7/29 22:45:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('108','雲韻1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('109','雲韻1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));
insert into worker_202307 (id,name,technology,save_date) values ('110','雲韻1','python',to_date('2023/7/29 22:46:19','yyyy-MM-dd hh24:mi:ss'));

（3）查詢分割槽資料

select count(1) from worker_202307 PARTITION (technology_java);
select count(1) from worker_202307 PARTITION (technology_python);
select count(1) from worker_202307 SUBPARTITION (worker_id_1);
select count(1) from worker_202307 SUBPARTITION (worker_id_2);
select count(1) from worker_202307 SUBPARTITION (worker_id_5);
select count(1) from worker_202307 SUBPARTITION (worker_id_6);

五、對已有表進行分割槽

1、先建立一張表，再插入200條資料。

create table WORKER_202308
(
  id        number not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)

2、建立一張新表，建一個範圍分割槽

create table WORKER_202308_tab
(
  id        number not null,
  name      VARCHAR2(200),
  technology      VARCHAR2(100),
  save_date DATE
)
partition by range (id)
(
  partition WORKER1 values less than (201)
);

3、把原表資料插入到新表

select * from WORKER_202308;
select * from WORKER_202308_tab;

ALTER TABLE WORKER_202308_tab
  EXCHANGE PARTITION WORKER1
  WITH TABLE WORKER_202308
  WITHOUT VALIDATION;

成功轉移。

4、刪除原表、新表改名

DROP TABLE WORKER_202308;
RENAME WORKER_202308_tab TO WORKER_202308;

5、將一個分割槽拆分成多個分割槽，分割槽界限元素必須是一個：字串，日期時間或間隔文字。

AT括號內不能是欄位名稱，改為數字即可。

ALTER TABLE WORKER_202308 
SPLIT PARTITION WORKER1 AT (180) 
INTO (PARTITION WORKER2,PARTITION WORKER3);

注意：不能修改分割槽列的資料型別

透過sql查詢驗證分割槽是否成功。

SELECT * FROM USER_TAB_PARTITIONS 
WHERE TABLE_NAME='WORKER_202309';

透過sql查詢分割槽資料：

select count(1) from WORKER_202309 
PARTITION (WORKER1);//分割槽不存在 ok

select count(1) from WORKER_202309 
PARTITION (WORKER2);//179條資料 ok

select count(1) from WORKER_202309 
PARTITION (WORKER3);//21條資料 ok

6、截斷分割槽

截斷分割槽是指刪除某個分割槽中的資料，並不會刪除分割槽，也不會刪除其它分割槽中的資料。

ALTER TABLE WORKER_202309 
TRUNCATE PARTITION WORKER3;

7、合併分割槽

ALTER TABLE WORKER_202309 
MERGE PARTITIONS WORKER2,WORKER3 
INTO PARTITION WORKER3 ;

六、小結

使用Oracle這麼久，第一次系統的瞭解Oracle的儲存結構，Oracle -> 表空間 -> 段 -> 區 -> 邏輯資料塊。

瞭解了Oracle分割槽表技術適用於哪些場景、何時分割槽、分割槽表的分類，並透過SQL例項進行了實戰演練。