前言

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

一、分區(qū)表概述

1.1 分區(qū)表概念

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

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

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

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

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

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

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

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

1.3.1 提升SQL查詢性能

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

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

1.3.2 提升表可管理性

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

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

當表分區(qū)后，每個分區(qū)都具有獨立性。在你操作某個分區(qū)時，不會影響其他分區(qū)數(shù)據(jù)的使用，即使某個分區(qū)因為故障不可用，也完全不會影響其他分區(qū)上運行的事務。同時分區(qū)可以存儲在不同的表空間/物理介質(zhì)上，分散I/O壓力。

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

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

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

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

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

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

范圍分區(qū)表是通過 create table 語句的 partition by range 子句來創(chuàng)建的，分區(qū)的范圍通過 values less than 子句指定，其指定的是分區(qū)的上限（不包含），所有大于等于指定值的數(shù)據(jù)被分配至下一個分區(qū)，除了第一個分區(qū)，每個分區(qū)的下限即前一個分區(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個分區(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中，如果沒有這個分區(qū)，那么插入大于等于'2023-03-01'的數(shù)據(jù)時，會因為沒有合適的存儲分區(qū)而報錯。

你也可以在定義分區(qū)時指定存儲特性，例如將分區(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ū)的一個擴展，它也是通過范圍來劃分分區(qū)，唯一的區(qū)別是：間隔分區(qū)可以在相應分區(qū)數(shù)據(jù)插入時自動創(chuàng)建分區(qū)，省去了普通范圍分區(qū)手動創(chuàng)建分區(qū)的操作。

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

間隔分區(qū)表的創(chuàng)建由在普通范圍分區(qū)定義上新增一個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個月創(chuàng)建一個分區(qū)。

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

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

我們在初始分區(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ù)時，Oracle自動為我們創(chuàng)建了1個對應的分區(qū)SYS_P327。

對于間隔分區(qū)，你也可以通過 store in 子句指定多個表空間，Oracle將以循環(huán)的方式在各個表空間中創(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ā)自動創(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自動以循環(huán)的方式在3個表空間中創(chuàng)建了分區(qū)。

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

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

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

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

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

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

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

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

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

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ū)是由你為每個分區(qū)指定一系列的離散值（列表），當分區(qū)鍵等于特定的離散值時，數(shù)據(jù)會被放到相應的分區(qū)。列表分區(qū)可以讓你自定義數(shù)據(jù)的組織方式，例如按照地域來分類數(shù)據(jù)。

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

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')
);

你可以選擇性的增加一個包含 default 值的分區(qū)，這樣所有沒有預先定義的分區(qū)鍵值都會放入該分區(qū)，否則會報錯：

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 即可。

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

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

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

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

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

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

其他兩種分區(qū)類型同理，因此復合分區(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ū)，每個范圍分區(qū)再分為4個哈希分區(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ū)通過年齡進行劃分
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ū)通過性別進行劃分
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ū)特別適合在需要自動維護子表，或者兩表頻繁連接查詢的場景，因為他們的分區(qū)策略是相同的，兩表連接通常會被轉(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)建子表時，如果要采用引用分區(qū)，則定義外鍵的列要非空，子表會通過外鍵繼承主表的分區(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);

下面我們驗證一下引用分區(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ū)的自動新建分區(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ù)的時候，對應的分區(qū)也自動創(chuàng)建了出來（且分區(qū)編號都相同）。

當我們在主表上刪除分區(qū)時，對應的子表上的分區(qū)也被自動刪除了：

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ū)鍵可以定義在一個表達式上，這個表達式會被保存為元數(shù)據(jù)，而列并不實際存在于數(shù)據(jù)庫中。虛擬列分區(qū)可以與任何分區(qū)策略結(jié)合使用。

下面示例中，表 virtual_part 上通過salary和bonus定義了一個虛擬列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ù)庫來決定數(shù)據(jù)放在哪個分區(qū)，分區(qū)對應用都是透明的。而系統(tǒng)分區(qū)可以僅建立一個分區(qū)表，但不指定分區(qū)策略，因此它沒有分區(qū)鍵和分區(qū)規(guī)則。系統(tǒng)分區(qū)對上層應用不是透明的，應用往系統(tǒng)分區(qū)插入數(shù)據(jù)時，SQL必須顯式的指定分區(qū)名，否則會報錯。

系統(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ù)存儲完全由應用決定，因此在插入數(shù)據(jù)時，必須顯示指定數(shù)據(jù)保存的分區(qū)：

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

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

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

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

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

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

4.1 新增分區(qū)

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

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

alter table members drop partition pmax; --由于建表時定義了p_max，要先刪除才能演示，實際應用中要注意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ù)會重新在各分區(qū)中進行分布，可能需要一些時間：

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

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

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

4.2 刪除分區(qū)

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

alter table members drop partition p3; 

引用分區(qū)無法顯式刪除，因為它的分區(qū)策略繼承自父表，只有當父表刪除分區(qū)時，子表上的引用分區(qū)才會級聯(lián)刪除（前面演示過）。

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

alter table hash_part1 coalesce partition;

4.3 置換分區(qū)

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

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

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

創(chuàng)建一個與members結(jié)構(gòu)一樣的表，并插入幾條測試數(shù)據(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)建一個與分區(qū)表完全匹配的非分區(qū)表：

create table mem_ext for exchange with table members;

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

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

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

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

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

4.4 合并分區(qū)

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

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

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

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

間隔分區(qū)限制同范圍分區(qū)，你也只能合并相鄰的分區(qū)，而且合并還回會導致所有低于合并分區(qū)的間隔分區(qū)都轉(zhuǎn)換為范圍分區(qū)，合并分區(qū)的上沿就是范圍分區(qū)和間隔分區(qū)的分界點，以下面的interval_part表示例，每月1個分區(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ū)進行合并（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消失了，生成了一個新的分區(qū)SYS_P451，原先邊界范圍在合并分區(qū)之下的3月分區(qū)（SYS_P447）也被轉(zhuǎn)換成了范圍分區(qū)（interval=NO），而合并分區(qū)之上11月的分區(qū)（SYS_P450）依然是間隔分區(qū)(interval=YES)。

列表分區(qū)由于分區(qū)之間沒有順序，因此你可以合并任意兩個分區(qū)，合并后的分區(qū)包含兩個分區(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ū)

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

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

分裂范圍分區(qū)，我們需要指定一個分裂點（包含在分區(qū)內(nèi)），整個分區(qū)將以這個分裂點為邊界拆分為2個分區(qū)，分裂點會作為第一個分區(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ū)類似，我們也需要指定一個分裂點。且分裂間隔分區(qū)和和合并間隔分區(qū)一樣，也會導致所有低于被分裂分區(qū)上限的間隔分區(qū)都轉(zhuǎn)換為范圍分區(qū)，被分裂分區(qū)的上限即范圍分區(qū)和間隔分區(qū)的分界點。我們將上面示例的最后一個間隔分區(qū) - 11月的分區(qū)（SYS_P450）從11月15號分裂為2個分區(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，同時低于原分區(qū)上限的所有分區(qū)都會被轉(zhuǎn)換為范圍分區(qū)（interval=NO）。

分裂列表分區(qū)，你需要指定需要分裂出去的值，這些指定的值會分配到第一個分區(qū)，原分區(qū)剩余的值會分配到第二個分區(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，剩余的值會被放入p3。

4.6 移動分區(qū)

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

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

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

移動分區(qū)實際是在新目的地新建一個分區(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 截斷分區(qū)

需要徹底清除某個分區(qū)數(shù)據(jù)時，你可以用 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，這些視圖都有三個級別，分別以dba_，all_，user_開頭：

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

5.1 dba_/all_/user_part_tables

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

select * from all_part_tables;

主要字段含義解釋：

5.2 dba_/all_/user_tab_partitions

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

select * from all_tab_partitions;

主要字段含義解釋：

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

5.3 dba_/all_/user_part_key_columns

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

select * from all_part_key_columns;

主要字段含義解釋：

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

5.4 dba_/all_/user_part_col_statistics

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

select * from all_part_col_statistics;

主要字段含義解釋：

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

六、總結(jié)導圖

總結(jié) 

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

最新評論