大家好，我是哪吒，最近項目有一個新的需求，??按月建表，按天分區??。
不都是分庫分表嗎？怎么又來個分區？
讓我們一起，一探究竟，深入理解一下Oracle分區表技術，實現快速入門，豐富個人簡歷，提高面試level，給自己增加一點談資，秒變面試小達人，BAT不是夢。
三分鐘你將學會：
- Oracle是如何存儲數據的？
- Oracle分區是什么？
- 何時分區？
- 分區表的分類都有哪些？
- Oracle分區技術實戰演練
一、Oracle是如何存儲數據的？?
1、邏輯存儲與物理存儲
在國企或者一線大廠，一般都會選擇使用Oracle數據庫，程序通過mybatis等持久層框架訪問Oracle數據庫，指定表空間，表空間內包含若干張表，表中存有行數據，行數據以行片段的形式存儲在數據庫塊中，① 當插入的行太大，無法裝入單個塊時；② 或因為更新的緣故，導致現有行超出了當前空間時 -> 就會發生整個行不存儲在一個位置的情況。
Oracle在邏輯上將數據存儲在表空間中，在物理上將數據存儲在數據文件中。
表空間包括若干個數據文件，這些表空間使用與運行Oracle軟件的操作系統一致的物理結構。數據庫的數據存儲在構成數據庫表空間的數據文件中。
臨時文件是一個臨時表空間的文件；它是通過TEMPFILE選項創建的。臨時表空間不包含表，通常用于排序。
2、進一步分析它們之間的關系
- 數據庫包含若干個表空間（邏輯存儲單元）。
- 每一個表空間包含很多的Oracle 邏輯數據塊，邏輯數據塊的大小一般在2 KB 至32 KB，默認8 KB。
- Oracle 數據塊是邏輯I/O的最小單位。
- 特定數目的相鄰邏輯塊構成了“區”。
- 特定邏輯結構分配的一組區構成了一個段。
3、Oracle邏輯數據塊
數據庫塊包含塊頭、行數據、可用空間。
（1）塊頭
塊頭包含段類型（如表或索引）、數據塊地址、表目錄、行目錄和事務處理插槽。
每個插槽的大小為24 字節，修改塊中的行時會使用這些插槽。
（2）行數據
塊中行的實際數據。
（3）可用空間
可用空間位于塊的中部，允許頭和行數據空間在必要時進行增長。當插入新行或用更大的值更新現有行的列時，行數據會占用可用空間。
（4）致塊頭增長的原因有：
- 行目錄需要更多的行條目；
- 需要的事務處理插槽數多于最初配置的數目；
塊中的可用空間最初是相鄰的。但是，刪除和更新操作可能會使塊中的可用空間變成碎片，需要時Oracle 服務器會接合塊中的空閑空間。
二、Oracle分區表技術?
分區是指表和索引可以被分成若干個部分，它們擁有相同的邏輯屬性和數據結構。所有分區的字段和索引都是一樣的。
分區表是將表數據分為若干個可以被單獨管理的片，每個片就是一個分區，分一個分區都可以擁有自己的物理屬性，比如表空間、事務槽、存儲參數、最小區段數等，通過建分區語句指定，提升可用性和存儲效率。
每個分區可以被單獨管理，降低管理成本和備份成本，提高容錯率，避免“一榮既榮，一損俱損”的問題。
1、分區表的優缺點
（1）優點
- 可以通過指定分區提高查詢性能；
- 提高容錯率，避免“一榮既榮，一損俱損”的問題；
- 降低管理成本；
- 降低備份成本；
（2）缺點
普通表和分區表不能直接轉換，可以通過數據遷移，再重命名的方式實現，需要重建約束、索引，在創建表時可以添加關鍵字“parallel compress”并行執行，提高效率，下面會通過SQL實例介紹。
2、何時分區？
單表的數據量如果過大，會影響SQL的讀寫性能，我們可以通過分庫分表的方式解決表性能的問題，Oracle的分區表是將一張大表在物理上分成幾個較小的表，從邏輯上看仍然是一張完整的表。這樣，每次DML操作只考慮其中一張分區表即可。
那么，臨界點是多少呢？
- 數量量超過500萬且空間占用超過2GB的時候
必須分區； - 數量量高于100萬，低于500萬時建議分區；
注意：單個分區的數據可以超過500萬，但存儲空間不建議超過2GB。
三、分區相關的數據字典?
根據數據字典表的前綴不同，可查詢的內容及權限有所差異：
- DBA_開頭：需要DBA權限，查詢全庫內容；
- ALL_開頭：查詢當前用戶權限下的內容；
- USER_開頭：查詢當前用戶下的內容；
以下是分區表的一些相關字典表，前綴是“DBA_”、“ALL_”、“USER_”；
- 分區表信息字典表：*_PART_TABLES；
- 分區信息字典表：*_TAB_PARTITIONS；
- 子分區信息字典表：*_TAB_SUBPARTITIONS；
- 分區表的分區字段信息字典表：*_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）建表語句分析
- NLS_CALENDAR=GREGORIAN：用于指定Oracle所使用的日歷體系，其取值為Arabic Hijrah、English Hijrah、Gregorian、Japanese Imperial、Persian、ROC Official、Thai Buddha。
- tablespace：指定表空間；
- pctfree：塊保留10%的空間留給更新該塊數據使用
- initrans：初始化事務槽的個數；
- maxtrans：最大事務槽的個數；
- storage：存儲參數
- initial：區段(extent)一次擴展64k
- minextents：最小區段數
- 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分區？
- 分區鍵的值最好是連續的；
- 分區數量最好是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實例進行了實戰演練。
本文轉載自微信公眾號「哪吒編程」，可以通過以下二維碼關注。轉載本文請聯系哪吒編程公眾號。
