前言

當(dāng)單表數(shù)據(jù)量隨著時(shí)間變的越來越大時(shí)，會(huì)給數(shù)據(jù)的管理和查詢帶來不便。我們可以考慮對(duì)表進(jìn)行分區(qū)，利用分區(qū)表特性將數(shù)據(jù)分成小塊存儲(chǔ)，可以大幅提升查詢性能，管理便捷性及數(shù)據(jù)的可用性。

一、分區(qū)表概述

1.1 分區(qū)表概念

分區(qū)表就是將表在物理存儲(chǔ)層面分成多個(gè)小的片段，這些片段即稱為分區(qū)，每個(gè)分區(qū)保存表的一部分?jǐn)?shù)據(jù)，表的分區(qū)對(duì)上層應(yīng)用是完全透明的，從應(yīng)用的角度來看，表在邏輯上依然是一個(gè)整體。

每個(gè)分區(qū)都有自己的名字并可以擁有不同的存儲(chǔ)特性，例如可以將分區(qū)保存在不同的磁盤以上分散I/O，或者分散在不同的表空間（表空間需要有相同的block size）。

向分區(qū)表插入數(shù)據(jù)時(shí)，為了判斷每條數(shù)據(jù)應(yīng)該被分配至哪個(gè)分區(qū)，我們通常需要選擇定義一個(gè)分區(qū)鍵（Partition Key）。根據(jù)每條數(shù)據(jù)分區(qū)鍵的值或者對(duì)其運(yùn)算的結(jié)果來決定數(shù)據(jù)的分區(qū)歸屬，分區(qū)鍵可以由1或多個(gè)列組成（最多16個(gè)列）.

1.2 何時(shí)使用分區(qū)表

知道了分區(qū)表的概念，那么什么情況下應(yīng)該使用分區(qū)表呢？如果遇到如下幾個(gè)場(chǎng)景你可以考慮使用分區(qū)表：

• 表的大小超過2G
• 表中有大量的歷史數(shù)據(jù)，數(shù)據(jù)存在明顯的時(shí)間順序
• 表的存儲(chǔ)必須分散在不同的存儲(chǔ)設(shè)備上

1.3 分區(qū)表的優(yōu)點(diǎn)

分區(qū)表在結(jié)構(gòu)和管理上比普通表更復(fù)雜，但它也有一定的優(yōu)點(diǎn)，主要優(yōu)點(diǎn)有以下3類：

1.3.1 提升SQL查詢性能

對(duì)于SQL查詢，當(dāng)where條件涉及分區(qū)鍵時(shí)，可以快速定位需要掃描的分區(qū)，這樣可以將數(shù)據(jù)的掃描范圍限制在很小的范圍，極大的提升查詢性能。這個(gè)特性叫做分區(qū)裁剪（Partition Pruning）。

另外，在多表連接（join）時(shí)，如果在每個(gè)表在連接的鍵上都進(jìn)行了分區(qū)，那么Oracle可以將兩個(gè)大表之間的連接轉(zhuǎn)換成更小的分區(qū)級(jí)連接，極大提升連接速度，這個(gè)特性叫做分區(qū)連接（Partition-wise Join）。

1.3.2 提升表可管理性

使用分區(qū)表之后，原來表級(jí)別的管理操作也被分散為至“分區(qū)級(jí)”，各個(gè)分區(qū)上獨(dú)立的進(jìn)行運(yùn)維任務(wù)，原先一個(gè)大表上的運(yùn)維任務(wù)，現(xiàn)在可以拆開成一系列小任務(wù)分散在不同的時(shí)間窗口執(zhí)行。例如，平時(shí)備份表的操作，現(xiàn)在可以備份單個(gè)分區(qū)。

1.3.3 提升數(shù)據(jù)可用性

當(dāng)表分區(qū)后，每個(gè)分區(qū)都具有獨(dú)立性。在你操作某個(gè)分區(qū)時(shí)，不會(huì)影響其他分區(qū)數(shù)據(jù)的使用，即使某個(gè)分區(qū)因?yàn)楣收喜豢捎?，也完全不?huì)影響其他分區(qū)上運(yùn)行的事務(wù)。同時(shí)分區(qū)可以存儲(chǔ)在不同的表空間/物理介質(zhì)上，分散I/O壓力。

二、基礎(chǔ)分區(qū)策略

根據(jù)不同的應(yīng)用場(chǎng)景，你可以為表選擇不同的分區(qū)策略，Oracle提供的基礎(chǔ)分區(qū)策略有：

• 范圍分區(qū)（Range Partition）
• 哈希分區(qū)（Hash Partition）
• 列表分區(qū)（List partition）

在基礎(chǔ)分區(qū)策略的基礎(chǔ)上，還有一些其他的擴(kuò)展分區(qū)策略，后面再進(jìn)行討論。

2.1 范圍分區(qū)（Range Partition）

范圍分區(qū)根據(jù)預(yù)先定義的范圍來劃分分區(qū)，范圍分區(qū)最適合管理類似且有明顯順序的數(shù)據(jù)，根據(jù)數(shù)據(jù)的順序可以很容易劃定分區(qū)范圍。范圍分區(qū)最典型的應(yīng)用場(chǎng)景就是按時(shí)間對(duì)數(shù)據(jù)進(jìn)行分區(qū)，所以其經(jīng)常使用時(shí)間類型的分區(qū)鍵。

范圍分區(qū)表是通過 create table 語句的 partition by range 子句來創(chuàng)建的，分區(qū)的范圍通過 values less than 子句指定，其指定的是分區(qū)的上限（不包含），所有大于等于指定值的數(shù)據(jù)被分配至下一個(gè)分區(qū)，除了第一個(gè)分區(qū)，每個(gè)分區(qū)的下限即前一個(gè)分區(qū)的上限：

create table members (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')),
 partition p2 values less than (to_date('2023-03-01', 'yyyy-mm-dd')),
 partition pmax values less than (maxvalue)
);

上面的例子中定義了3個(gè)分區(qū)：

• 所有create_time小于'2023-02-01'的數(shù)據(jù)（不包含）被分配在分區(qū)p1中。
• 所有create_time小于'2023-03-01'的數(shù)據(jù)（不包含）被分配在p2中。
• 所有create_time大于等于'2023-03-01'的數(shù)據(jù)被分配在pmax中，如果沒有這個(gè)分區(qū)，那么插入大于等于'2023-03-01'的數(shù)據(jù)時(shí)，會(huì)因?yàn)闆]有合適的存儲(chǔ)分區(qū)而報(bào)錯(cuò)。

你也可以在定義分區(qū)時(shí)指定存儲(chǔ)特性，例如將分區(qū)分散到不同的表空間（表空間可以放到不同的物理磁盤上）：

create tablespace tbs1;
create tablespace tbs2;
create tablespace tbs3;
 
create table members (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')) tablespace tbs1,  -- 指定分區(qū)p1放在tbs1中
 partition p2 values less than (to_date('2023-03-01', 'yyyy-mm-dd')) tablespace tbs2,
 partition pmax values less than (maxvalue) tablespace tbs3
);

2.1.1 間隔分區(qū)（Interval partition）

間隔分區(qū)是范圍分區(qū)的一個(gè)擴(kuò)展，它也是通過范圍來劃分分區(qū)，唯一的區(qū)別是：間隔分區(qū)可以在相應(yīng)分區(qū)數(shù)據(jù)插入時(shí)自動(dòng)創(chuàng)建分區(qū)，省去了普通范圍分區(qū)手動(dòng)創(chuàng)建分區(qū)的操作。

如果不是需要?jiǎng)?chuàng)建不規(guī)則的范圍分區(qū)，那么更推薦使用間隔分區(qū)來替代范圍分區(qū)，你只需要指定一個(gè)分區(qū)間隔及初始分區(qū)，后續(xù)的分區(qū)創(chuàng)建將由Oracle自動(dòng)完成。

間隔分區(qū)表的創(chuàng)建由在普通范圍分區(qū)定義上新增一個(gè)interval子句創(chuàng)建：

create table inv_part (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH'))     -- 指定分區(qū)間隔
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd'))
);

上面的例子指定在初始分區(qū)p1的基礎(chǔ)上，每隔1個(gè)月創(chuàng)建一個(gè)分區(qū)。

通過視圖user_tab_partitions可以看到目前只有1個(gè)分區(qū)p1:

select table_name, partition_name from user_tab_partitions where table_name='INV_PART';

我們?cè)诔跏挤謪^(qū)的上限之上插入一條數(shù)據(jù)：

insert into inv_part values(1, 'Vincent', date '2023-02-02');
commit;
select table_name, partition_name from user_tab_partitions where table_name='INV_PART';

在現(xiàn)有分區(qū)之上插入數(shù)據(jù)時(shí)，Oracle自動(dòng)為我們創(chuàng)建了1個(gè)對(duì)應(yīng)的分區(qū)SYS_P327。

對(duì)于間隔分區(qū)，你也可以通過 store in 子句指定多個(gè)表空間，Oracle將以循環(huán)的方式在各個(gè)表空間中創(chuàng)建分區(qū)。

create table multi_tbs (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH'))  store in (tbs1, tbs2, tbs3)
(
 partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd')) tablespace tbs1
);

查詢初始分區(qū)的所屬表空間：

select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='MULTI_TBS';

插入兩條數(shù)據(jù)，觸發(fā)自動(dòng)創(chuàng)建新的分區(qū)：

insert into multi_tbs values(1, 'Vincent', date '2023-02-02');
insert into multi_tbs values(2, 'Victor', date '2023-03-02');
commit;
 
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='MULTI_TBS';

可以看到Oracle自動(dòng)以循環(huán)的方式在3個(gè)表空間中創(chuàng)建了分區(qū)。

2.2 哈希分區(qū)（Hash Partition）

哈希分區(qū)是對(duì)指定的分區(qū)鍵（Partition Key）運(yùn)行哈希算法來決定數(shù)據(jù)存儲(chǔ)在哪個(gè)分區(qū)。哈希分區(qū)會(huì)隨機(jī)的將數(shù)據(jù)分配到各個(gè)分區(qū)中，并盡量平均，保證各個(gè)分區(qū)的大小差不多一致。

由于數(shù)據(jù)是隨機(jī)分布，所以哈希分區(qū)并不適合管理有明顯時(shí)間順序的歷史數(shù)據(jù)。它更適合需要將數(shù)據(jù)平均的分布到各個(gè)不同存儲(chǔ)設(shè)備上的場(chǎng)景。同時(shí)在選用哈希分區(qū)時(shí)建議滿足下列條件：

• 選取分區(qū)鍵時(shí)盡量選取唯一列（Unique）或列中有大量唯一值（Almost Unique）的列。
• 創(chuàng)建哈希分區(qū)時(shí)，分區(qū)的數(shù)量盡量是2的冪，例如2,4,8,16等。

哈希分區(qū)表是通過 create table 語句的 partition by hash 子句來創(chuàng)建的，創(chuàng)建時(shí)你可以顯式的指定每個(gè)分區(qū)名稱，所屬表空間。

create table hash_part1 (
id number,
name varchar2(32))
partition by hash(id)
(
partition p1 tablespace tbs1,
partition p2 tablespace tbs2
);

也可以僅指定哈希分區(qū)的數(shù)量，此時(shí)Oracle會(huì)自動(dòng)為每個(gè)分區(qū)生成名字：

create table hash_part2 (
id number,
name varchar2(32))
partition by hash(id)
partitions 2;    -- 指定哈希分區(qū)數(shù)量，不用指定分區(qū)名

你也可以用 store in 子句讓分區(qū)以循環(huán)的方式建立在各個(gè)表空間中：

create table hash_part3 (
id number,
name varchar2(32))
partition by hash(id) 
partitions 4
store in (tbs1, tbs2, tbs3);

2.3 列表分區(qū)（List partition）

列表分區(qū)是由你為每個(gè)分區(qū)指定一系列的離散值（列表），當(dāng)分區(qū)鍵等于特定的離散值時(shí)，數(shù)據(jù)會(huì)被放到相應(yīng)的分區(qū)。列表分區(qū)可以讓你自定義數(shù)據(jù)的組織方式，例如按照地域來分類數(shù)據(jù)。

列表分區(qū)表是通過 create table 語句的 partition by list 子句來創(chuàng)建的，創(chuàng)建時(shí)你需要為每個(gè)分區(qū)指定一個(gè)列表（離散值）。

create table list_part1 (
id number,
name varchar2(32),
city varchar2(32))
partition by list(city)
(
partition p_jiangsu values ('NanJing', 'SuZhou'),
partition p_zhejiang values('HangZhou', 'JiaXing')
);

你可以選擇性的增加一個(gè)包含 default 值的分區(qū)，這樣所有沒有預(yù)先定義的分區(qū)鍵值都會(huì)放入該分區(qū)，否則會(huì)報(bào)錯(cuò)：

create table list_part2 (
id number,
name varchar2(32),
city varchar2(32))
partition by list(city)
(
partition p_jiangsu values ('NanJing', 'SuZhou'),
partition p_zhejiang values('HangZhou', 'JiaXing'),
partition p_def values (default)
);

列表分區(qū)建立完成后，你可以很方便的利用 alter table … modify partition … add/drop values ( … ) 來修改列表分區(qū)的枚舉值：

alter table list_part2 modify partition p_jiangsu add values('YangZhou');
alter table list_part2 modify partition p_jiangsu drop values('YangZhou');

如果列表分區(qū)是子分區(qū)，只需要將 modify partition 替換為 modify subpartition 即可。

三、擴(kuò)展分區(qū)策略

除了前面介紹的3種基礎(chǔ)分區(qū)策略，Oracle還提供一些其他的分區(qū)策略，它們都是在基礎(chǔ)分區(qū)策略上進(jìn)行某種功能的擴(kuò)充。

3.1 復(fù)合分區(qū)（Composite Partition）

復(fù)合分局，顧名思義，就是將多種分區(qū)策略結(jié)合起來使用，在基礎(chǔ)分區(qū)的策略上，對(duì)每個(gè)分區(qū)再一次應(yīng)用分區(qū)策略。例如，在基礎(chǔ)的范圍分區(qū)基礎(chǔ)上，還可以對(duì)每個(gè)分區(qū)再次應(yīng)用范圍分區(qū)，即每個(gè)分區(qū)又被劃分為若干個(gè)子分區(qū)。類似于中國(guó)可以劃分為很多?。ǚ謪^(qū)），每個(gè)省又可以劃分為很多市（子分區(qū)）。

在使用復(fù)合分區(qū)時(shí)，3種基礎(chǔ)分區(qū)策略可以隨意組合，例如，使用范圍分區(qū)作為基礎(chǔ)分區(qū)，其子分區(qū)可以使用范圍、哈希、列表分區(qū)策略，即：

• 范圍-范圍分區(qū)
• 范圍-哈希分區(qū)
• 范圍-列表分區(qū)

其他兩種分區(qū)類型同理，因此復(fù)合分區(qū)共有3*3=9種方案。

子分區(qū)是通過原來分區(qū)策略上通過新增 subpartition子句來定義的，下面我們以范圍分區(qū)（間隔分區(qū)）為基礎(chǔ)分區(qū)，演示三種子分區(qū)的創(chuàng)建方式

comp_part1的采用范圍-哈希分區(qū)策略：

create table comp_part1 (
id number,
name varchar2(32),
create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH'))  -- 范圍分區(qū)（間隔分區(qū)）
subpartition by hash(id) subpartitions 4    -- 子分區(qū)采用哈希分區(qū)，每個(gè)范圍分區(qū)再分為4個(gè)哈希分區(qū)
(
 partition p1 values less than  (to_date('2023-02-01', 'yyyy-mm-dd'))
);

comp_part2的采用范圍-范圍分區(qū)策略：

create table comp_part2 (
id number,
name varchar2(32),
age number,
create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH'))  -- 范圍分區(qū)（間隔分區(qū)）
subpartition by range(age)     -- 子分區(qū)通過年齡進(jìn)行劃分
subpartition template    -- 定義子分區(qū)模板
(
 subpartition p_children    values less than (12),
 subpartition p_adolescent values less than (30),
 subpartition p_adult         values less than (60),
 subpartition p_elder         values less than (100)
)
(
 partition p1 values less than  (to_date('2023-02-01', 'yyyy-mm-dd'))
);

comp_part3的采用范圍-列表分區(qū)策略：

create table comp_part3 (
id number,
name varchar2(32),
sex varchar2 (32),
create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH'))  -- 范圍分區(qū)（間隔分區(qū)）
subpartition by list(sex)     -- 子分區(qū)通過性別進(jìn)行劃分
subpartition template
(
 subpartition p_man    values  ('male'),
 subpartition p_women values ('female')
)
(
 partition p1 values less than  (to_date('2023-02-01', 'yyyy-mm-dd'))
);

3.2 引用分區(qū)（Reference Partition）

引用分區(qū)是一種基于主-外鍵引用關(guān)系的分區(qū)策略，如果兩張表上定義了外鍵引用，即兩張表存在父-子關(guān)系（Parent-Child Realtionship），那么基于這種主鍵-外鍵引用關(guān)系，可以使子表繼承主表的分區(qū)策略。

引用分區(qū)特別適合在需要自動(dòng)維護(hù)子表，或者兩表頻繁連接查詢的場(chǎng)景，因?yàn)樗麄兊姆謪^(qū)策略是相同的，兩表連接通常會(huì)被轉(zhuǎn)換為分區(qū)連接（partition-wise join），大大縮小連接的結(jié)果集。

引用分區(qū)是通過partition by reference創(chuàng)建的。例如，下面兩張表parent_table和child_table 定義了引用分區(qū)：

create table parent_table (
id number primary key,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH')) 
(
 partition p1 values less than  (to_date('2023-02-01', 'yyyy-mm-dd'))
);

創(chuàng)建子表時(shí)，如果要采用引用分區(qū)，則定義外鍵的列要非空，子表會(huì)通過外鍵繼承主表的分區(qū)方案。

create table child_table (
id number primary key,
parent_id number not null,  -- 定義外鍵的列要非空
sex varchar2(32),
constraint parent_id_fk foreign key (parent_id) references parent_table(id))  -- 定義外鍵約束
partition by reference (parent_id_fk);

下面我們驗(yàn)證一下引用分區(qū)的繼承，通過視圖 user_tab_partitions 可以看到，初始child_table也繼承了主表初始分區(qū)

select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='PARENT_TABLE';
 
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='CHILD_TABLE';

我們往 parent_table 中插入一條數(shù)據(jù)，觸發(fā)間隔分區(qū)的自動(dòng)新建分區(qū)特性：

insert into parent_table values(1, 'Vincent', date '2023-02-02');
commit;
 
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='PARENT_TABLE';

下面我們往child_table中插入一條數(shù)據(jù)：

insert into child_table values(1, 1,'male');
commit;
 
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='CHILD_TABLE';

可以看到，在子表插入數(shù)據(jù)的時(shí)候，對(duì)應(yīng)的分區(qū)也自動(dòng)創(chuàng)建了出來（且分區(qū)編號(hào)都相同）。

當(dāng)我們?cè)谥鞅砩蟿h除分區(qū)時(shí)，對(duì)應(yīng)的子表上的分區(qū)也被自動(dòng)刪除了：

alter table parent_table drop partition SYS_P391;
 
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='CHILD_TABLE';

3.3 虛擬列分區(qū)（Virtual Column-based Partition）

虛擬列分區(qū)即分區(qū)鍵可以定義在虛擬列上，虛擬列分區(qū)使分區(qū)鍵可以定義在一個(gè)表達(dá)式上，這個(gè)表達(dá)式會(huì)被保存為元數(shù)據(jù)，而列并不實(shí)際存在于數(shù)據(jù)庫(kù)中。虛擬列分區(qū)可以與任何分區(qū)策略結(jié)合使用。

下面示例中，表 virtual_part 上通過salary和bonus定義了一個(gè)虛擬列income，然后將income作為分區(qū)鍵：

create table virtual_part(
id number primary key,
name varchar2(32),
salary number,
bonus number,
income as (salary + bonus))
partition by range(income)
(
 partition p1 values less than (1000),
 partition p2 values less than (5000)
);

3.4 系統(tǒng)分區(qū)（System Partition）

前介紹的分區(qū)策略都是由數(shù)據(jù)庫(kù)來決定數(shù)據(jù)放在哪個(gè)分區(qū)，分區(qū)對(duì)應(yīng)用都是透明的。而系統(tǒng)分區(qū)可以僅建立一個(gè)分區(qū)表，但不指定分區(qū)策略，因此它沒有分區(qū)鍵和分區(qū)規(guī)則。系統(tǒng)分區(qū)對(duì)上層應(yīng)用不是透明的，應(yīng)用往系統(tǒng)分區(qū)插入數(shù)據(jù)時(shí)，SQL必須顯式的指定分區(qū)名，否則會(huì)報(bào)錯(cuò)。

系統(tǒng)分區(qū)通過 create table 的 partition by system 子句創(chuàng)建，后續(xù)只需要定義分區(qū)，不需要分區(qū)鍵：

create table system_part (
id number primary key,
name varchar2(32))
partition by system(
partition p1,
partition p2
);

系統(tǒng)分區(qū)的數(shù)據(jù)存儲(chǔ)完全由應(yīng)用決定，因此在插入數(shù)據(jù)時(shí)，必須顯示指定數(shù)據(jù)保存的分區(qū)：

insert into system_part values (1, 'Vincent');  

僅通過表名插入數(shù)據(jù)時(shí)報(bào)錯(cuò)：系統(tǒng)分區(qū)還需要提供分區(qū)擴(kuò)展名

insert into system_part partition(p1) values (1, 'Vincent'); 

插入時(shí)顯式指定分區(qū)，插入成功。

四、分區(qū)表運(yùn)維（Partition Maintenance）

在日常運(yùn)行中，我們有時(shí)候還需要對(duì)分區(qū)表進(jìn)行一些維護(hù)操作，下面是一些常見的運(yùn)維案例。

4.1 新增分區(qū)

手動(dòng)新增分區(qū)，不同的分區(qū)類型操作稍微有些不同。注意間隔分區(qū)和引用分區(qū)的分區(qū)都是自動(dòng)創(chuàng)建的，因此它們無法手動(dòng)新增分區(qū)。

范圍分區(qū)可以使用alter table … add partition 手動(dòng)新增分區(qū)，注意僅可以在范圍分區(qū)最大范圍的上面新增分區(qū)，如果已經(jīng)定義了最大值分區(qū)（maxvalue）或者想要在中間插入一個(gè)分區(qū)，則只可以使用分裂分區(qū)來完成（后面會(huì)介紹）：

alter table members drop partition pmax; --由于建表時(shí)定義了p_max，要先刪除才能演示，實(shí)際應(yīng)用中要注意p_max分區(qū)是否有數(shù)據(jù)

alter table members add partition p3 values less than (to_date('2023-04-01', 'yyyy-mm-dd'));

哈希分區(qū)直接alter table … add partition 即可，你可以指定分區(qū)名，也可以不指定分區(qū)名，數(shù)據(jù)會(huì)重新在各分區(qū)中進(jìn)行分布，可能需要一些時(shí)間：

alter table hash_part1 add partition p3 tablespace tbs3;
 
alter table hash_part2 add partition tablespace tbs3;

列表分區(qū)直接 alter table … add partition 新增一個(gè)分區(qū)定義：

alter table list_part1 add partition p_anhui values('HeFei', 'ChuZhou');

4.2 刪除分區(qū)

使用 alter table … drop partition 可以刪除指定的分區(qū)，對(duì)于范圍分區(qū)、間隔分區(qū)，列表分區(qū)，直接指定要?jiǎng)h除的分區(qū)名即可，間隔分區(qū)雖然無法顯式新增分區(qū)，但是可以顯式刪除：

alter table members drop partition p3; 

引用分區(qū)無法顯式刪除，因?yàn)樗姆謪^(qū)策略繼承自父表，只有當(dāng)父表刪除分區(qū)時(shí)，子表上的引用分區(qū)才會(huì)級(jí)聯(lián)刪除（前面演示過）。

對(duì)于哈希分區(qū)，我們無法直接刪除分區(qū)。如果要減少分區(qū)的數(shù)量，必須采用一個(gè)叫 coalesce partition （融合分區(qū)）的操作，下面的示例會(huì)將哈希分區(qū)的數(shù)量減少1個(gè)。這個(gè)操作雖然減少了一個(gè)分區(qū)，但是并不會(huì)丟失數(shù)據(jù)，數(shù)據(jù)會(huì)在剩下的分區(qū)中重新分布。

alter table hash_part1 coalesce partition;

4.3 置換分區(qū)

置換分區(qū)指可以用一個(gè)非分區(qū)表與分區(qū)表的某個(gè)分區(qū)/子分區(qū)進(jìn)行置換（數(shù)據(jù)段交換）。利用置換分區(qū)可以快速將數(shù)據(jù)載入或者移出分區(qū)表，且置換分區(qū)操作沒有類型限制，所有的分區(qū)策略都可以使用此特性。

要置換分區(qū)，首先你要?jiǎng)?chuàng)建一個(gè)與分區(qū)表結(jié)構(gòu)一樣的非分區(qū)表，我們以前面的范圍分區(qū)表members作為示例：

select table_name, partition_name, tablespace_name from user_tab_partitions where table_name='MEMBERS';

創(chuàng)建一個(gè)與members結(jié)構(gòu)一樣的表，并插入幾條測(cè)試數(shù)據(jù)，我們計(jì)劃置換members分區(qū)p2，但是第二條數(shù)據(jù)我們插入一條違反該分區(qū)規(guī)則（create_time <'2023-03-01'）的數(shù)據(jù)。

create table mem_ext (
id number,
name varchar2(32),
create_time date);
 
insert into mem_ext values (3, 'exchanged_data', date '2023-02-01');
insert into mem_ext values (4, 'exchanged_data', date '2023-03-01');
commit;

如果是12cR2以上的版本，你還可以用 create table … for exchange with table … 語句來快速創(chuàng)建一個(gè)與分區(qū)表完全匹配的非分區(qū)表：

create table mem_ext for exchange with table members;

將mem_ext表與members表的p2分區(qū)進(jìn)行置換：

alter table members exchange partition p2 with table mem_ext;    -- 由于預(yù)先插入違反分區(qū)規(guī)則的數(shù)據(jù)導(dǎo)致報(bào)錯(cuò)
 
alter table members exchange partition p2 with table mem_ext without validation;

如果置換的分區(qū)中有不符合分區(qū)規(guī)則的數(shù)據(jù)（第二條），可以用 without validation 子句跳過數(shù)據(jù)驗(yàn)證（僅更新數(shù)據(jù)字典）。

當(dāng)交換分區(qū)或者更新分區(qū)鍵時(shí)，可能會(huì)導(dǎo)致數(shù)據(jù)的分區(qū)歸屬變化（下面第一個(gè)報(bào)錯(cuò)），這時(shí)候Oracle就需要在不同分區(qū)移動(dòng)數(shù)據(jù)，我們可以在建表的時(shí)候開啟行移動(dòng)（row movement），或者手動(dòng)打開，這樣當(dāng)分區(qū)鍵被更新且需要移動(dòng)分區(qū)時(shí)，Oracle會(huì)自動(dòng)將數(shù)據(jù)移動(dòng)到正確的分區(qū)：

update members set create_time='2023-03-03 00:00:00' where id=3;  -- 更新分區(qū)鍵會(huì)導(dǎo)致切換分區(qū)，報(bào)錯(cuò)
alter table members enable row movement;
update members set create_time='2023-03-03 00:00:00' where id=3;  -- 分區(qū)鍵更新后，數(shù)據(jù)會(huì)被移動(dòng)到正確的分區(qū)

4.4 合并分區(qū)

利用 alter table 的 merge partition/subpartion 子句，你可以將兩個(gè)分區(qū)合并成一個(gè)。合并分區(qū)僅適用于范圍、間隔、列表分區(qū)類型，哈希和引用分區(qū)不適用。

對(duì)于范圍分區(qū)，你只能將相鄰兩個(gè)的分區(qū)進(jìn)行合并，且只能合并到邊界高的分區(qū)，例如下面，由于p2分區(qū)上限更高，只能將分區(qū)p1合并至p2，不能將p2合并至p1:

alter table members merge partitions p1, p2 into partition p2 update indexes;

合并分區(qū)時(shí)，建議帶上update indexes來更新索引，或合并后重建。

間隔分區(qū)限制同范圍分區(qū)，你也只能合并相鄰的分區(qū)，而且合并還回會(huì)導(dǎo)致所有低于合并分區(qū)的間隔分區(qū)都轉(zhuǎn)換為范圍分區(qū)，合并分區(qū)的上沿就是范圍分區(qū)和間隔分區(qū)的分界點(diǎn)，以下面的interval_part表示例，每月1個(gè)分區(qū)，我們插入數(shù)據(jù)讓3、7、8，11月的間隔分區(qū)創(chuàng)建出來

create table interval_part (
id number,
name varchar2(32),
create_time date)
partition by range(create_time)
interval (numtoyminterval(1, 'MONTH'))
(
 partition p1 values less than (to_date('2023-01-01', 'yyyy-mm-dd'))
);
 
 
insert into interval_part values(1,'abc', date '2023-03-10');
insert into interval_part values(1,'abc', date '2023-07-10');
insert into interval_part values(1,'abc', date '2023-08-10');
insert into interval_part values(1,'abc', date '2023-11-10');
commit;

可以看到我們插入數(shù)據(jù)觸發(fā)的新建分區(qū)屬于間隔分區(qū)（interval=YES）：

select table_name, partition_name, interval from user_tab_partitions where table_name='INTERVAL_PART';

下面將相鄰的7，8月分區(qū)進(jìn)行合并（SYS_448, SYS_P449）:

alter table interval_part merge partitions for (to_date('2023-07-10', 'yyyy-mm-dd')), for(to_date('2023-08-10', 'yyyy-mm-dd')) ;
 
select table_name, partition_name, interval from user_tab_partitions where table_name='INTERVAL_PART';

可以看到7，8月分區(qū)SYS_448, SYS_P449消失了，生成了一個(gè)新的分區(qū)SYS_P451，原先邊界范圍在合并分區(qū)之下的3月分區(qū)（SYS_P447）也被轉(zhuǎn)換成了范圍分區(qū)（interval=NO），而合并分區(qū)之上11月的分區(qū)（SYS_P450）依然是間隔分區(qū)(interval=YES)。

列表分區(qū)由于分區(qū)之間沒有順序，因此你可以合并任意兩個(gè)分區(qū)，合并后的分區(qū)包含兩個(gè)分區(qū)的所有數(shù)據(jù)，以下面list_part表舉例：

create table list_part (
id number,
name varchar2(32))
partition by list(name)
(
partition p1 values ('a', 'b'),
partition p2 values('c', 'd'),
partition p3 values('e', 'f')
);

我們將不相鄰的分區(qū)p1,p3合并成了p_merged：

select table_name, partition_name from user_tab_partitions where table_name='LIST_PART';
alter table list_part merge partitions p1,p3 into partition p_merged;
select table_name, partition_name from user_tab_partitions where table_name='LIST_PART';

4.5 分裂分區(qū)

當(dāng)某個(gè)分區(qū)過大時(shí)，你可能想要將它分裂成2個(gè)分區(qū)。分裂分區(qū)是合并分區(qū)的逆向操作，和合并分區(qū)的限制一樣，分裂分區(qū)也僅適用于范圍、間隔、列表分區(qū)類型，哈希和引用分區(qū)不適用。

分裂操作會(huì)重新將數(shù)據(jù)在2個(gè)分區(qū)中進(jìn)行分布，現(xiàn)在以上面一節(jié)合并的分區(qū)為示例，再將它們分開。

分裂范圍分區(qū)，我們需要指定一個(gè)分裂點(diǎn)（包含在分區(qū)內(nèi)），整個(gè)分區(qū)將以這個(gè)分裂點(diǎn)為邊界拆分為2個(gè)分區(qū)，分裂點(diǎn)會(huì)作為第一個(gè)分區(qū)的上限（不包含），下面示例將范圍分區(qū)p2拆分為p1和p2：

alter table members split partition p2 at (to_date('2023-02-01', 'yyyy-mm-dd')) into (partition p1, partition p2) update indexes;

分裂間隔分區(qū)和分裂范圍分區(qū)類似，我們也需要指定一個(gè)分裂點(diǎn)。且分裂間隔分區(qū)和和合并間隔分區(qū)一樣，也會(huì)導(dǎo)致所有低于被分裂分區(qū)上限的間隔分區(qū)都轉(zhuǎn)換為范圍分區(qū)，被分裂分區(qū)的上限即范圍分區(qū)和間隔分區(qū)的分界點(diǎn)。我們將上面示例的最后一個(gè)間隔分區(qū) - 11月的分區(qū)（SYS_P450）從11月15號(hào)分裂為2個(gè)分區(qū)：

select table_name, partition_name, interval from user_tab_partitions where table_name='INTERVAL_PART';
alter table interval_part split partition for(date '2023-11-10') at (date '2023-11-15') update indexes;
select table_name, partition_name, interval from user_tab_partitions where table_name='INTERVAL_PART';

分區(qū)SYS_P450分裂成了SYS_P467和SYS_P468，同時(shí)低于原分區(qū)上限的所有分區(qū)都會(huì)被轉(zhuǎn)換為范圍分區(qū)（interval=NO）。

分裂列表分區(qū)，你需要指定需要分裂出去的值，這些指定的值會(huì)分配到第一個(gè)分區(qū)，原分區(qū)剩余的值會(huì)分配到第二個(gè)分區(qū)。

在上面一節(jié)列表分區(qū)合并操作中，我們將p1和p3合并成了p_merged，現(xiàn)在再將它們分開：

select table_name, partition_name, high_value from user_tab_partitions where table_name='LIST_PART';
 
alter table list_part split partition p_merged values('a', 'b') into
(
 partition p1,
 partition p3
);
 
select table_name, partition_name, high_value from user_tab_partitions where table_name='LIST_PART';

觀察分裂前后的分區(qū)枚舉值，我們指定'a', 'b'被分裂出去，那么它們將被放入p1，剩余的值會(huì)被放入p3。

4.6 移動(dòng)分區(qū)

移動(dòng)分區(qū)可以讓你隨意將某個(gè)分區(qū)移動(dòng)其他表空間，這種情況通常用在需要將分區(qū)遷移到另一個(gè)存儲(chǔ)設(shè)備上。同時(shí)也可以順便對(duì)分區(qū)進(jìn)行一些其他操作，例如壓縮。所有類型的分區(qū)策略都支持移動(dòng)分區(qū)。

要移動(dòng)分區(qū)至其他表空間，使用alter table的 move partition 子句，：

alter table interval_part move partition p1 tablespace tbs1 update indexes compress;

移動(dòng)分區(qū)實(shí)際是在新目的地新建一個(gè)分區(qū)，并將原分區(qū)刪除（drop），即使目的地是相同的表空間也是如此。

4.7 重命名分區(qū)

你可以用 alter table … rename partition … to … 來給指定的分區(qū)重命名，重命名沒有限制，所有分區(qū)策略都可以使用：

alter table interval_part rename partition sys_p447 to p2;

4.8 截?cái)喾謪^(qū)

需要徹底清除某個(gè)分區(qū)數(shù)據(jù)時(shí)，你可以用 alter table … truncate partition … 來徹底清除該分區(qū)的數(shù)據(jù)（所有分區(qū)策略都適用）。

alter table interval_part truncate partition p2 update indexes;

五、常用分區(qū)表視圖

分區(qū)表有一組相關(guān)視圖，可以供我們查詢分區(qū)信息，例如前面用到的user_table_partitions，這些視圖都有三個(gè)級(jí)別，分別以dba_，all_，user_開頭：

• dba_ 開頭的視圖可以查詢所有信息
• all_ 開頭的時(shí)候可以查詢有權(quán)限訪問的信息（歸屬自己 + 被賦權(quán)的）
• user_ 開頭的視圖可以查詢歸屬自己對(duì)象的信息

5.1 dba_/all_/user_part_tables

該組視圖顯示表級(jí)別的分區(qū)信息（每個(gè)分區(qū)表一條數(shù)據(jù)）：

select * from all_part_tables;

主要字段含義解釋：

5.2 dba_/all_/user_tab_partitions

該組視圖顯示分區(qū)級(jí)別的分區(qū)信息（每個(gè)分區(qū)一條數(shù)據(jù)）：

select * from all_tab_partitions;

主要字段含義解釋：

另外 dba_/all_/user_tab_subpartitions 視圖顯示信息類似，顯示子分區(qū)級(jí)別的信息。

5.3 dba_/all_/user_part_key_columns

該組視圖顯示分區(qū)鍵信息：

select * from all_part_key_columns;

主要字段含義解釋：

另外 dba_/all_/user_subpart_key_columns 視圖顯示信息類似，顯示子分區(qū)級(jí)別的信息。

5.4 dba_/all_/user_part_col_statistics

改組視圖顯示列相關(guān)的統(tǒng)計(jì)信息

select * from all_part_col_statistics;

主要字段含義解釋：

另外 dba_/all_/user_subpart_col_statistics 視圖顯示信息類似，顯示子分區(qū)級(jí)別的信息。

六、總結(jié)導(dǎo)圖

總結(jié) 

到此這篇關(guān)于Oracle分區(qū)表(Oracle Partitioned Tables)的文章就介紹到這了,更多相關(guān)Oracle分區(qū)表詳解內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論