大家好，我是哪吒，最近項(xiàng)目有一個(gè)新的需求，??按月建表，按天分區(qū)??。
不都是分庫分表嗎？怎么又來個(gè)分區(qū)？
讓我們一起，一探究竟，深入理解一下Oracle分區(qū)表技術(shù)，實(shí)現(xiàn)快速入門，豐富個(gè)人簡(jiǎn)歷，提高面試level，給自己增加一點(diǎn)談資，秒變面試小達(dá)人，BAT不是夢(mèng)。
三分鐘你將學(xué)會(huì)：
- Oracle是如何存儲(chǔ)數(shù)據(jù)的？
- Oracle分區(qū)是什么？
- 何時(shí)分區(qū)？
- 分區(qū)表的分類都有哪些？
- Oracle分區(qū)技術(shù)實(shí)戰(zhàn)演練
一、Oracle是如何存儲(chǔ)數(shù)據(jù)的？?
1、邏輯存儲(chǔ)與物理存儲(chǔ)
在國(guó)企或者一線大廠，一般都會(huì)選擇使用Oracle數(shù)據(jù)庫，程序通過mybatis等持久層框架訪問Oracle數(shù)據(jù)庫，指定表空間，表空間內(nèi)包含若干張表，表中存有行數(shù)據(jù)，行數(shù)據(jù)以行片段的形式存儲(chǔ)在數(shù)據(jù)庫塊中，① 當(dāng)插入的行太大，無法裝入單個(gè)塊時(shí)；② 或因?yàn)楦碌木壒?，?dǎo)致現(xiàn)有行超出了當(dāng)前空間時(shí) -> 就會(huì)發(fā)生整個(gè)行不存儲(chǔ)在一個(gè)位置的情況。
Oracle在邏輯上將數(shù)據(jù)存儲(chǔ)在表空間中，在物理上將數(shù)據(jù)存儲(chǔ)在數(shù)據(jù)文件中。
表空間包括若干個(gè)數(shù)據(jù)文件，這些表空間使用與運(yùn)行Oracle軟件的操作系統(tǒng)一致的物理結(jié)構(gòu)。數(shù)據(jù)庫的數(shù)據(jù)存儲(chǔ)在構(gòu)成數(shù)據(jù)庫表空間的數(shù)據(jù)文件中。
臨時(shí)文件是一個(gè)臨時(shí)表空間的文件；它是通過TEMPFILE選項(xiàng)創(chuàng)建的。臨時(shí)表空間不包含表，通常用于排序。
2、進(jìn)一步分析它們之間的關(guān)系
- 數(shù)據(jù)庫包含若干個(gè)表空間（邏輯存儲(chǔ)單元）。
- 每一個(gè)表空間包含很多的Oracle 邏輯數(shù)據(jù)塊，邏輯數(shù)據(jù)塊的大小一般在2 KB 至32 KB，默認(rèn)8 KB。
- Oracle 數(shù)據(jù)塊是邏輯I/O的最小單位。
- 特定數(shù)目的相鄰邏輯塊構(gòu)成了“區(qū)”。
- 特定邏輯結(jié)構(gòu)分配的一組區(qū)構(gòu)成了一個(gè)段。
3、Oracle邏輯數(shù)據(jù)塊
數(shù)據(jù)庫塊包含塊頭、行數(shù)據(jù)、可用空間。
（1）塊頭
塊頭包含段類型（如表或索引）、數(shù)據(jù)塊地址、表目錄、行目錄和事務(wù)處理插槽。
每個(gè)插槽的大小為24 字節(jié)，修改塊中的行時(shí)會(huì)使用這些插槽。
（2）行數(shù)據(jù)
塊中行的實(shí)際數(shù)據(jù)。
（3）可用空間
可用空間位于塊的中部，允許頭和行數(shù)據(jù)空間在必要時(shí)進(jìn)行增長(zhǎng)。當(dāng)插入新行或用更大的值更新現(xiàn)有行的列時(shí)，行數(shù)據(jù)會(huì)占用可用空間。
（4）致塊頭增長(zhǎng)的原因有：
- 行目錄需要更多的行條目；
- 需要的事務(wù)處理插槽數(shù)多于最初配置的數(shù)目；
塊中的可用空間最初是相鄰的。但是，刪除和更新操作可能會(huì)使塊中的可用空間變成碎片，需要時(shí)Oracle 服務(wù)器會(huì)接合塊中的空閑空間。
二、Oracle分區(qū)表技術(shù)?
分區(qū)是指表和索引可以被分成若干個(gè)部分，它們擁有相同的邏輯屬性和數(shù)據(jù)結(jié)構(gòu)。所有分區(qū)的字段和索引都是一樣的。
分區(qū)表是將表數(shù)據(jù)分為若干個(gè)可以被單獨(dú)管理的片，每個(gè)片就是一個(gè)分區(qū)，分一個(gè)分區(qū)都可以擁有自己的物理屬性，比如表空間、事務(wù)槽、存儲(chǔ)參數(shù)、最小區(qū)段數(shù)等，通過建分區(qū)語句指定，提升可用性和存儲(chǔ)效率。
每個(gè)分區(qū)可以被單獨(dú)管理，降低管理成本和備份成本，提高容錯(cuò)率，避免“一榮既榮，一損俱損”的問題。
1、分區(qū)表的優(yōu)缺點(diǎn)
（1）優(yōu)點(diǎn)
- 可以通過指定分區(qū)提高查詢性能；
- 提高容錯(cuò)率，避免“一榮既榮，一損俱損”的問題；
- 降低管理成本；
- 降低備份成本；
（2）缺點(diǎn)
普通表和分區(qū)表不能直接轉(zhuǎn)換，可以通過數(shù)據(jù)遷移，再重命名的方式實(shí)現(xiàn)，需要重建約束、索引，在創(chuàng)建表時(shí)可以添加關(guān)鍵字“parallel compress”并行執(zhí)行，提高效率，下面會(huì)通過SQL實(shí)例介紹。
2、何時(shí)分區(qū)？
單表的數(shù)據(jù)量如果過大，會(huì)影響SQL的讀寫性能，我們可以通過分庫分表的方式解決表性能的問題，Oracle的分區(qū)表是將一張大表在物理上分成幾個(gè)較小的表，從邏輯上看仍然是一張完整的表。這樣，每次DML操作只考慮其中一張分區(qū)表即可。
那么，臨界點(diǎn)是多少呢？
- 數(shù)量量超過500萬且空間占用超過2GB的時(shí)候
必須分區(qū)； - 數(shù)量量高于100萬，低于500萬時(shí)建議分區(qū)；
注意：?jiǎn)蝹€(gè)分區(qū)的數(shù)據(jù)可以超過500萬，但存儲(chǔ)空間不建議超過2GB。
三、分區(qū)相關(guān)的數(shù)據(jù)字典?
根據(jù)數(shù)據(jù)字典表的前綴不同，可查詢的內(nèi)容及權(quán)限有所差異：
- DBA_開頭：需要DBA權(quán)限，查詢?nèi)珟靸?nèi)容；
- ALL_開頭：查詢當(dāng)前用戶權(quán)限下的內(nèi)容；
- USER_開頭：查詢當(dāng)前用戶下的內(nèi)容；
以下是分區(qū)表的一些相關(guān)字典表，前綴是“DBA_”、“ALL_”、“USER_”；
- 分區(qū)表信息字典表：*_PART_TABLES；
- 分區(qū)信息字典表：*_TAB_PARTITIONS；
- 子分區(qū)信息字典表：*_TAB_SUBPARTITIONS；
- 分區(qū)表的分區(qū)字段信息字典表：*_PART_KEY_COLUMNS；
四、分區(qū)表的分類
1、范圍分區(qū)
將數(shù)據(jù)基于范圍映射到每一個(gè)分區(qū)，這個(gè)范圍是由創(chuàng)建分區(qū)表時(shí)指定的分區(qū)鍵決定。
一般選取id或者時(shí)間作為范圍分區(qū)的分區(qū)鍵。
（1）按月建表，按天分區(qū)
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%的空間留給更新該塊數(shù)據(jù)使用
- initrans：初始化事務(wù)槽的個(gè)數(shù)；
- maxtrans：最大事務(wù)槽的個(gè)數(shù)；
- storage：存儲(chǔ)參數(shù)
- initial：區(qū)段(extent)一次擴(kuò)展64k
- minextents：最小區(qū)段數(shù)
- maxextents unlimited：最大區(qū)段無限制
?
每個(gè)塊都有一個(gè)塊首部。這個(gè)塊首部中有一個(gè)事務(wù)表。事務(wù)表中會(huì)建立一些條目來描述哪些事務(wù)將塊上的哪些行/元素鎖定。這個(gè)事務(wù)表的初始大小由對(duì)象的INITRANS 設(shè)置指定。對(duì)于表，這個(gè)值默認(rèn)為2（索引的INITRANS 也默認(rèn)為2）。事務(wù)表會(huì)根據(jù)需要?jiǎng)討B(tài)擴(kuò)展，最大達(dá)到MAXTRANS 個(gè)條目（假設(shè)塊上有足夠的自由空間）。所分配的每個(gè)事務(wù)條目需要占用塊首部中的23～24 字節(jié)的存儲(chǔ)空間。注意，對(duì)于Oracle 10g，MAXTRANS 則會(huì)忽略，所有段的MAXTRANS 都是255。
由于oracle塊里有一個(gè)PCT_free的概念，即oracle會(huì)預(yù)留塊大小的10%作為緩沖，當(dāng)修改oracle的事務(wù)增加時(shí)，事務(wù)槽向下增長(zhǎng)，當(dāng)更新oracle塊的數(shù)據(jù)時(shí)，數(shù)據(jù)向上增長(zhǎng)，PCT_free的空間被壓縮。
?
local索引是針對(duì)單個(gè)分區(qū)表的索引。
在對(duì)分區(qū)表進(jìn)行維護(hù)操作時(shí)需檢查索引是否失效，索引失效除了會(huì)導(dǎo)致查詢慢，還會(huì)導(dǎo)致數(shù)據(jù)寫入失敗，在ALTER TBALE語法中也可以添加關(guān)鍵字“UPDATE INDEXES”避免維護(hù)表時(shí)索引失效。
（3）插入三條數(shù)據(jù)
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）查詢指定分區(qū)
select * from worker_202301
partition (WORKER20230129);
跨分區(qū)查詢時(shí)，查詢每個(gè)分區(qū)的數(shù)據(jù)后使用UNION ALL關(guān)鍵字做集合查詢，提高查詢效率。
（5）添加分區(qū)
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）刪除分區(qū)
ALTER TABLE worker_202301
DROP PARTITION WORKER20230131;
2、列表分區(qū)
（1）列表分區(qū)適用于一個(gè)字段只有「固定」的幾個(gè)值，比如類型、月份、課程等。
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）插入三條數(shù)據(jù)
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）查詢列表分區(qū)數(shù)據(jù)
（4）如果一個(gè)分區(qū)的數(shù)據(jù)量不大，可以合并分區(qū)
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、哈希分區(qū)
范圍分區(qū)和列表分區(qū)都是使用某一個(gè)字段進(jìn)行分區(qū)，此字段的分區(qū)度大才行，但也會(huì)產(chǎn)生諸多問題，比如上述的按技術(shù)列表分區(qū)，現(xiàn)階段，Java開發(fā)人員明顯高于C，此時(shí)就會(huì)導(dǎo)致分區(qū)不均勻的問題。
此時(shí)，hash分區(qū)閃亮登場(chǎng)，hash分區(qū)的好處是讓分區(qū)更均勻一些。
（1）上面的諸多參數(shù)都可以省略。
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,
);
此時(shí)，插入200條數(shù)據(jù)，id從1到200，驗(yàn)證一下是否均勻。數(shù)據(jù)條數(shù)分別是51、55、61、33。
（2）何時(shí)使用hash分區(qū)？
- 分區(qū)鍵的值最好是連續(xù)的；
- 分區(qū)數(shù)量最好是2的n次方，對(duì)hash運(yùn)算更加友好；
（3）添加hash分區(qū)：
ALTER TABLE worker_202304
ADD PARTITION worker_id_5;
剛創(chuàng)建好分區(qū)，worker_id_5就有數(shù)據(jù)了，why？匪夷所思。
添加分區(qū)時(shí)，所有數(shù)據(jù)會(huì)重新計(jì)算hash值，重新分配到不同的分區(qū)表中。
（4）不可以刪除hash分區(qū)
4、范圍列表組合分區(qū)
（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條數(shù)據(jù)
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）查詢分區(qū)數(shù)據(jù)
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）添加主分區(qū)
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添加子分區(qū)technology_go_29：
ALTER TABLE worker_202305
MODIFY PARTITION WORKER20230529
ADD SUBPARTITION technology_go_29 values('go');
（5）刪除子分區(qū)
ALTER TABLE worker_202305
DROP SUBPARTITION technology_go_29;
5、范圍哈希組合分區(qū)
（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條數(shù)據(jù)
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）查詢分區(qū)數(shù)據(jù)
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分區(qū)的緣故，數(shù)據(jù)分布不均勻。
6、列表哈希組合分區(qū)
（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條數(shù)據(jù)
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）查詢分區(qū)數(shù)據(jù)
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);
五、對(duì)已有表進(jìn)行分區(qū)?
1、先創(chuàng)建一張表，再插入200條數(shù)據(jù)。
create table WORKER_202308
(
id number not null,
name VARCHAR2(200),
technology VARCHAR2(100),
save_date DATE
)
2、創(chuàng)建一張新表，建一個(gè)范圍分區(qū)
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、把原表數(shù)據(jù)插入到新表
select * from WORKER_202308;
select * from WORKER_202308_tab;
ALTER TABLE WORKER_202308_tab
EXCHANGE PARTITION WORKER1
WITH TABLE WORKER_202308
WITHOUT VALIDATION;
成功轉(zhuǎn)移。
4、刪除原表、新表改名
DROP TABLE WORKER_202308;
RENAME WORKER_202308_tab TO WORKER_202308;
5、將一個(gè)分區(qū)拆分成多個(gè)分區(qū)，分區(qū)界限元素必須是一個(gè)：字符串，日期時(shí)間或間隔文字。
AT括號(hào)內(nèi)不能是字段名稱，改為數(shù)字即可。
ALTER TABLE WORKER_202308
SPLIT PARTITION WORKER1 AT (180)
INTO (PARTITION WORKER2,PARTITION WORKER3);
注意：不能修改分區(qū)列的數(shù)據(jù)類型
通過sql查詢驗(yàn)證分區(qū)是否成功。
SELECT * FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME='WORKER_202309';
通過sql查詢分區(qū)數(shù)據(jù)：
select count(1) from WORKER_202309
PARTITION (WORKER1);//分區(qū)不存在 ok
select count(1) from WORKER_202309
PARTITION (WORKER2);//179條數(shù)據(jù) ok
select count(1) from WORKER_202309
PARTITION (WORKER3);//21條數(shù)據(jù) ok
6、截?cái)喾謪^(qū)
截?cái)喾謪^(qū)是指刪除某個(gè)分區(qū)中的數(shù)據(jù)，并不會(huì)刪除分區(qū)，也不會(huì)刪除其它分區(qū)中的數(shù)據(jù)。
ALTER TABLE WORKER_202309
TRUNCATE PARTITION WORKER3;
7、合并分區(qū)
ALTER TABLE WORKER_202309
MERGE PARTITIONS WORKER2,WORKER3
INTO PARTITION WORKER3 ;
六、小結(jié)?
使用Oracle這么久，第一次系統(tǒng)的了解Oracle的存儲(chǔ)結(jié)構(gòu)，Oracle -> 表空間 -> 段 -> 區(qū) -> 邏輯數(shù)據(jù)塊。
了解了Oracle分區(qū)表技術(shù)適用于哪些場(chǎng)景、何時(shí)分區(qū)、分區(qū)表的分類，并通過SQL實(shí)例進(jìn)行了實(shí)戰(zhàn)演練。
本文轉(zhuǎn)載自微信公眾號(hào)「哪吒編程」，可以通過以下二維碼關(guān)注。轉(zhuǎn)載本文請(qǐng)聯(lián)系哪吒編程公眾號(hào)。
