Oracle 表分区-易微帮

1.概述

分区表就是将表在物理存储层面分成多个小的片段，这些片段即称为分区，每个分区保存表的一部分数据，表的分区对上层应用是完全透明的，从应用的角度来看，表在逻辑上依然是一个整体。

目的：提高大表的查询效率

概念：将一个表划分为多个分区表，"分而治之"

优缺点
优点：

'改善查询性能': 分区对象的查询仅搜索自己关系的分区
'增强可用性': 如果某个分区出现故障，其它分区的数据仍然可用
'维护方便': 如果某个分区出现故障，仅修复该分区即可
'均衡I/O': 将不同的分区放置不同的磁盘，以均衡 I/O，改善整个系统性能

缺点：

已经存在的表无法直接转化为分区表 -- 不过有很多间接方法，如：重定义表

适用情况

表的大小超过2G
表中有大量的历史数据，数据存在明显的时间顺序
表的存储必须分散在不同的存储设备上

2. 基础分区策略

根据不同的应用场景，你可以为表选择不同的分区策略，Oracle提供的基础分区策略有：

范围分区（Range Partition）
哈希分区（Hash Partition）
列表分区（List partition）
间隔分区（Interval partition）

2.1 范围分区（Range Partition）

范围分区根据预先定义的范围来划分分区，范围分区最适合管理类似且有明显顺序的数据，根据数据的顺序可以很容易划定分区范围。范围分区最典型的应用场景就是按时间对数据进行分区，所以其经常使用时间类型的分区键

每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。

建表语句:
create table material_transactions ( 
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300),
transaction_date date not null 
) 
partition by range (transaction_id) ( 
partition part_01 values less than(30000000) tablespace ma_tra01,
partition part_02 values less than(60000000) tablespace ma_tra02, 
partition part_03 values less than(maxvalue) tablespace ma_tra03);

2.2间隔分区（Interval partition）

间隔分区是范围分区的一个扩展，它也是通过范围来划分分区，唯一的区别是：间隔分区可以在相应分区数据插入时自动创建分区，省去了普通范围分区手动创建分区的操作。

如果不是需要创建不规则的范围分区，那么更推荐使用间隔分区来替代范围分区，你只需要指定一个分区间隔及初始分区，后续的分区创建将由Oracle自动完成。

numtoyminterval(n,'year|month'):主要设置年和月的间隔
numtodsinterval(n,'day|hour|minuts|second'):设置天，小时，分钟，秒之间的间隔

create table inv_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-02-01', 'yyyy-mm-dd')));

2.3.3.列表分区

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。。列表分区可以让你自定义数据的组织方式,例如按照地域来分类数据

CREATE TABLE PROBLEM_TICKETS(PROBLEM_ID NUMBER(7) NOT NULL PRIMARY KEY,
DESCRIPTION VARCHAR2(2000), CUSTOMER_ID NUMBER(7) NOT NULL,DATE_ENTERED DATE NOT NULL, STATUS VARCHAR2(20))PARTITION BY LIST (STATUS)
(PARTITION PROB_ACTIVE VALUES ('ACTIVE') TABLESPACE PROB_TS01,
PARTITION PROB_INACTIVE VALUES ('INACTIVE') TABLESPACE PROB_TS02)

2.2.4 哈希分区（Hash Partition）

这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。

选取分区键时尽量选取唯一列（Unique）或列中有大量唯一值（Almost Unique）的列。
创建哈希分区时，分区的数量尽量是2的幂，例如2,4,8,16等。

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

3.复杂分区

3.1复合分区（Composite Partition）

在基础分区的策略上，对每个分区再一次应用分区策略。例如，在基础的范围分区基础上，还可以对每个分区再次应用范围分区，即每个分区又被划分为若干个子分区。类似于中国可以划分为很多省（分区），每个省又可以划分为很多市（子分区）。

在使用复合分区时，3种基础分区策略可以随意组合

范围-范围分区
范围-哈希分区
范围-列表分区

子分区是通过原来分区策略上通过新增 subpartition子句来定义的，下面我们以范围分区（间隔分区）为基础分区，演示三种子分区的创建方式.

create table 表名(
		   ..........
		  )partition by range|list|hash(主分区列名)
		  subpartition by range|list|hash(子分区列名)
		  (
		    partition 主分区名1 [values less than(上限)]|[values (值)] [tablespace 表空间名]
			(
			  subpartition 子分区名1 [values less than(上限)]|[values (值)] [tablespace 表空间名],
			  subpartition 子分区名2 [values less than(上限)]|[values (值)] [tablespace 表空间名],
			  .....
			)
			,
			partition 主分区名2 [values less than(上限)]|[values (值)][tablespace 表空间名]
			(
			  subpartition 子分区名1 [values less than(上限)]|[values (值)] [tablespace 表空间名],
			  subpartition 子分区名2 [values less than(上限)]|[values (值)] [tablespace 表空间名],
			  .....
			)
			,
			........
		  );
--范围-散列分区
create table material_transactions_test  (
transaction_id number primary key,
item_id number(8) not null,
item_description varchar2(300), 
transaction_date date ) 
partition by range(transaction_date) subpartition by hash(transaction_id) 
subpartitions 3 store in (ma_tra01,ma_tra02,ma_tra03) (
partition part_01 values less than(to_date('2006-01-01','yyyy-mm-dd')), 
partition part_02 values less than(to_date('2010-01-01','yyyy-mm-dd')), 
partition part_03 values less than(maxvalue) ); 

--范围,列表分区
CREATE TABLE SALES
(PRODUCT_ID VARCHAR2(5),SALES_DATE DATE,SALES_COST NUMBER(10),STATUS VARCHAR2(20))
PARTITION BY RANGE(SALES_DATE) SUBPARTITION BY LIST (STATUS)
(
PARTITION P1 VALUES LESS THAN(TO_DATE('2003-01-01','YYYY-MM-DD'))TABLESPACE rptfact2009
    (SUBPARTITION P1SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
     SUBPARTITION P1SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009),
   PARTITION P2 VALUES LESS THAN (TO_DATE('2003-03-01','YYYY-MM-DD')) TABLESPACE rptfact2009
(SUBPARTITION P2SUB1 VALUES ('ACTIVE') TABLESPACE rptfact2009,
 SUBPARTITION P2SUB2 VALUES ('INACTIVE') TABLESPACE rptfact2009)
)

--间隔-范围分区

create table comp_part2 (
id number,name varchar2(32),age number,create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH'))  -- 范围分区（间隔分区）
subpartition by range(age)     -- 子分区通过年龄进行划分
subpartition template    -- 定义子分区模板
(
 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')));

3.2 引用分区（Reference Partition) 了解

引用分区是一种基于主-外键引用关系的分区策略，如果两张表上定义了外键引用，即两张表存在父-子关系（Parent-Child Realtionship），那么基于这种主键-外键引用关系，可以使子表继承主表的分区策略。

引用分区特别适合在需要自动维护子表，或者两表频繁连接查询的场景，因为他们的分区策略是相同的，两表连接通常会被转换为分区连接（partition-wise join），大大缩小连接的结果集。

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

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

4.分区操作

4.1 新增分区

手动新增分区，不同的分区类型操作稍微有些不同。注意间隔分区和引用分区的分区都是自动创建的，因此它们无法手动新增分区。

范围分区可以使用alter table … add partition 手动新增分区，注意仅可以在范围分区最大范围的上面新增分区，如果已经定义了最大值分区（maxvalue）或者想要在中间插入一个分区，则只可以使用分裂分区来完成

新增主分区：
alter table 表名 add partition 分区名[values less than(上限)]|[values(值)] [tablespace 表空间];

哈希分区直接alter table … add partition 即可，你可以指定分区名，也可以不指定分区名，数据会重新在各分区中进行分布，可能需要一些时间：

--添加分区
ALTER TABLE list_table ADD PARTITION par4 VALUES('湖北');

列表分区直接 alter table … add partition 新增一个分区定义：

--添加散列分区
ALTER TABLE hash_table1 ADD PARTITION par4;

新增子分区：
语法：alter table 表名 modify partition 主分区名 add subpartition 子分区名 [values less than(上限)]|[values(值)] [tablespace 表空间];
--在主分区par1中添加一个子分区
ALTER TABLE sales_t1 MODIFY PARTITION par1 ADD SUBPARTITION par1_sub4 VALUES('湖北');

4.2 删除分区

使用 alter table … drop partition 可以删除指定的分区，对于范围分区、间隔分区，列表分区，直接指定要删除的分区名即可，间隔分区虽然无法显式新增分区，但是可以显式删除：

1.散列分区不允许删除。
2.删除某一个分区则分区中的数据也会被删除。
3.删除分区的时候一张表中至少要留有一个分区，如果要删除表中的所有的分区，则直接drop表。

4.3 置换分区

置换分区指可以用一个非分区表与分区表的某个分区/子分区进行置换（数据段交换）。利用置换分区可以快速将数据载入或者移出分区表，且置换分区操作没有类型限制，所有的分区策略都可以使用此特性。

要置换分区，首先你要创建一个与分区表结构一样的非分区表，我们以前面的范围分区表members作为示例

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

创建一个与members结构一样的表，并插入几条测试数据，我们计划置换members分区p2，但是第二条数据我们插入一条违反该分区规则（create_time <'2023-03-01'）的数据。

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;

将mem_ext表与members表的p2分区进行置换：如果置换的分区中有不符合分区规则的数据（第二条），可以用 without validation 子句跳过数据验证（仅更新数据字典）。

alter table members exchange partition p2 with table mem_ext;    -- 由于预先插入违反分区规则的数据导致报错
 
alter table members exchange partition p2 with table mem_ext without validation;

我们可以在建表的时候开启行移动（row movement），或者手动打开，这样当分区键被更新且需要移动分区时，Oracle会自动将数据移动到正确的分区：

update members set create_time='2023-03-03 00:00:00' where id=3;  -- 更新分区键会导致切换分区，报错
 
alter table members enable row movement;
 
update members set create_time='2023-03-03 00:00:00' where id=3;  -- 分区键更新后，数据会被移动到正确的分区

4.4 合并分区

利用 alter table 的 merge partition/subpartion 子句，你可以将两个分区合并成一个。合并分区仅适用于范围、间隔、列表分区类型，哈希和引用分区不适用。对于范围分区，你只能将相邻两个的分区进行合并，且只能合并到边界高的分区;合并分区时，建议带上update indexes来更新索引，或合并后重建

alter table 表名 merge partitions|subpartitions 分区1，分区2 into partition|subpartition 分区2；
alter table members merge partitions p1, p2 into partition p2 update indexes;

间隔分区限制同范围分区，你也只能合并相邻的分区，而且合并还回会导致所有低于合并分区的间隔分区都转换为范围分区，合并分区的上沿就是范围分区和间隔分区的分界点，以下面的interval_part表示例，每月1个分区，我们插入数据让3、7、8，11月的间隔分区创建出来

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;
--可以看到我们插入数据触发的新建分区属于间隔分区（interval=YES）：

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

下面将相邻的7，8月分区进行合并（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月分区SYS_448, SYS_P449消失了，生成了一个新的分区SYS_P451，原先边界范围在合并分区之下的3月分区（SYS_P447）也被转换成了范围分区（interval=NO），而合并分区之上11月的分区（SYS_P450）依然是间隔分区(interval=YES)。

列表分区由于分区之间没有顺序，因此你可以合并任意两个分区，合并后的分区包含两个分区的所有数据，

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

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 拆分分区

当某个分区过大时，你可能想要将它分裂成2个分区。拆分分区是合并分区的逆向操作，和合并分区的限制一样，分裂分区也仅适用于范围、间隔、列表分区类型，哈希和引用分区不适用。

分裂范围分区，我们需要指定一个分裂点（包含在分区内），整个分区将以这个分裂点为边界拆分为2个分区，分裂点会作为第一个分区的上限（不包含），下面示例将范围分区p2拆分为p1和p2：

alter table 表名 split partition 分区名 at(拆分上限)|values(值) into (partition 新分区名,partition 旧分区名)

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

分裂间隔分区和分裂范围分区类似，我们也需要指定一个分裂点。且分裂间隔分区和和合并间隔分区一样，也会导致所有低于被分裂分区上限的间隔分区都转换为范围分区，被分裂分区的上限即范围分区和间隔分区的分界点。我们将上面示例的最后一个间隔分区 - 11月的分区（SYS_P450）从11月15号分裂为2个分区：

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

分区SYS_P450分裂成了SYS_P467和SYS_P468，同时低于原分区上限的所有分区都会被转换为范围分区（interval=NO）。分裂列表分区，你需要指定需要分裂出去的值，这些指定的值会分配到第一个分区，原分区剩余的值会分配到第二个分区。

4.6 移动分区

移动分区可以让你随意将某个分区移动其他表空间，这种情况通常用在需要将分区迁移到另一个存储设备上。同时也可以顺便对分区进行一些其他操作，例如压缩。所有类型的分区策略都支持移动分区。(降低水位)

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

移动分区实际是在新目的地新建一个分区，并将原分区删除（drop），即使目的地是相同的表空间也是如此。

4.7 截断分区

需要彻底清除某个分区数据时，你可以用 alter table … truncate partition … 来彻底清除该分区的数据（所有分区策略都适用）

alter table interval_part truncate partition p2 update indexes;

Oracle 表分区