Oracle 分区表概述
1、分区表是一张逻辑表对应多张物理表。如果查询条件中包含分区字段，那么 Oracle 能够快速地定位至分区物理表，缩小查找范围，提升查询性能。
2、分区表的优点：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
3、当表的数据量比较大时，就可以使用分区表。
4、已经存在的表无法直接转为分区表。主要分区类型如下：
分区类型 描述
范围分区 - range
是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的 range 分区中。
如果某些记录暂无法预测范围，可以创建 maxvalue 分区，所有不在指定范围内的记录都 会被存储到 maxvalue 所在分区中。
列表 分区 - list 也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像 range 或者 hash 分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。
在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入 更新就会失败， 因此通常建议使用 list 分区时，要创建一个 default 分区存储那些不在指定范围内的记录 ，类似 range 分区中的 maxvalue 分区。
组合分区
如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。
比如：range - range；list - range；list - list；list - hash
5、主分区使用关键字 PARTITION、子分区使用关键字 SUBPARTITION，列表分区使用关键字 List 与 VAUES (X,X,X)，范围分区使用关键字 range 与 VALUES LESS THAN (X)。
Oracle 分区表新建
1、假如有一张员工表，它是分区划和年度的，因为数据量比较大，现在对它进行新建分区表。
按区划新建分区表 & List 列表分区
– 按区划新建分区表 & List 列表分区
– 最好设置一个默认(default)分区，否则如果插入数据的 mof_div_code 值不在分区列表中，-
– 插入会报错: ORA-14400:插入的分区关键字未映射到任何分区
create table EMP_PART_BY_MOF_DIV (
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
mof_div_code VARCHAR2(9),
fiscal_year VARCHAR2(4)
)
partition by list(mof_div_code) (
partition mof4300 values (‘430000000’),
partition mof4301 values (‘430100000’,‘430101000’),
partition mof4302 values (‘430200000’,‘430201000’),
partition mof4310 values (‘431000000’),
partition mof4312 values (‘431200000’),
partition mofDefault values (default)
) ;
按年度新建分区表 & range 范围分区
– 按年度新建分区表 & range 范围分区
create table EMP_PART_BY_YEAR (
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
mof_div_code VARCHAR2(9),
fiscal_year VARCHAR2(4)
)
partition by range(fiscal_year) (
Partition year2020 values less than(2021),
Partition year2021 values less than(2022),
Partition year2022 values less than(2023),
Partition year2023 values less than(2024),
Partition year2024 values less than(2025),
Partition year2025 values less than(2026),
Partition year2026 values less than(2027),
Partition yearMax values less than(maxvalue)
);
comment on column EMP_PART_BY_YEAR.mof_div_code is ‘区划’;
comment on column EMP_PART_BY_YEAR.fiscal_year is ‘年度’;
alter table EMP_PART_BY_YEAR add constraint EMP_PART_BY_YEAR_PK primary key (EMPNO) ;
按年度 + 区划新建分区表(range + list)
– 按年度 + 区划新建分区表，年度做主分区(范围)，区划做子分区(列表)
create table EMP_PART_BY_YEAR_MOF (
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
mof_div_code VARCHAR2(9),
fiscal_year VARCHAR2(4)
)
partition by range(fiscal_year) subpartition by list (MOF_DIV_CODE)
– 子分区模板
subpartition template (
subpartition mof4300 values (‘430000000’),
subpartition mof4301 values (‘430100000’,‘430101000’),
subpartition mof4302 values (‘430200000’,‘430201000’),
subpartition mof4310 values (‘431000000’),
subpartition mof4312 values (‘431200000’),
subpartition mofDefault values (default)
)
– 主分区
(
Partition year2020 values less than(2021),
Partition year2021 values less than(2022),
Partition year2022 values less than(2023),
Partition year2023 values less than(2024),
Partition year2024 values less than(2025),
Partition year2025 values less than(2026),
Partition year2026 values less than(2027),
Partition yearMax values less than(maxvalue)
);
按区划 + 年度 新建分区表(list + range)
– 按区划 + 年度 新建分区表，区划做主分区(列表), 年度做子分区(范围)
create table EMP_PART_BY_MOF_YEAR (
empno NUMBER(4) not null,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2),
mof_div_code VARCHAR2(9),
fiscal_year VARCHAR2(4)
)
partition by list (MOF_DIV_CODE) subpartition by range(fiscal_year)
– 子分区模板
subpartition template (
subpartition year2020 values less than(2021),
subpartition year2021 values less than(2022),
subpartition year2022 values less than(2023),
subpartition year2023 values less than(2024),
subpartition year2024 values less than(2025),
subpartition year2025 values less than(2026),
subpartition year2026 values less than(2027),
subpartition yearMax values less than(maxvalue)
)
– 主分区
(
Partition mof4300 values (‘430000000’),
Partition mof4301 values (‘430100000’,‘430101000’),
Partition mof4302 values (‘430200000’,‘430201000’),
Partition mof4310 values (‘431000000’),
Partition mof4312 values (‘431200000’),
Partition mofDefault values (default)
);
comment on column EMP_PART_BY_MOF_YEAR.mof_div_code is ‘区划’;
comment on column EMP_PART_BY_MOF_YEAR.fiscal_year is ‘年度’;
alter table EMP_PART_BY_MOF_YEAR add constraint EMP_PART_BY_MOF_YEAR_PK primary key (EMPNO) ;
Oracle 分区表信息查询
select * from USER_PART_TABLES; 查看当前用户的分区表信息
select * from ALL_PART_TABLES; 查看当前用户可访问的所有分区表信息
select * from dba_part_tables; 查看数据库所有分区表信息
select * from DBA_TAB_PARTITIONS; 查看数据库所有分区表的详细分区信息
select * from ALL_TAB_PARTITIONS; 查看当前用户可访问的所有分区表的详细分区信息
select * from USER_TAB_PARTITIONS; 查看当前用户的所有分区表的详细分区信息
select * from DBA_TAB_SUBPARTITIONS; 查看数据库所有组合分区表的子分区信息
select * from ALL_TAB_SUBPARTITIONS; 查看前用户可访问的所有组合分区表的子分区信息
select * from USER_TAB_SUBPARTITIONS; 查看前用户的所有组合分区表的子分区信息
select * from DBA_PART_KEY_COLUMNS; 查看数据库所有分区表的分区列信息
select * from ALL_PART_KEY_COLUMNS; 查看当前用户可访问的所有分区表的分区列信息
select * from USER_PART_KEY_COLUMNS; 查看当前用户的所有分区表的分区列信息
Oracle 分区表数据查询
1、查询格式：select * from 分区表名 partition(分区名) T WHERE 条件;
– 查询 mof4300 分区中 年度等于 2022 的数据
select * from EMP_PART_BY_MOF_DIV partition(mof4300) T WHERE t.fiscal_year=2022;
– 等价于
select * from EMP_PART_BY_MOF_DIV t where t.mof_div_code= ‘430000000’ and t.fiscal_year=2022;
Oracle 追加分区、删除分区、截断分区
– 给 EMP_PART_BY_YEAR_MOF 表添加一个分区/主分区: YEAR2028
– 注意，如果主分区是range分区，且设置了最大值maxvalue，或者是list分区，设置了default，则直接追加会报错，此时必须’拆分分区’：
– ORA-14074: 分区界限必须调整为高于最后一个分区界限
– ORA-14621: 在 DEFAULT 子分区已存在时无法添加子分区
ALTER TABLE EMP_PART_BY_YEAR_MOF ADD PARTITION year2027 VALUES LESS THAN (2028);
– 添加子分区(为主分区year2027追加子分区year2027_mof4322)
– 注意，如果子分区是range分区，且设置了最大值maxvalue，或者是list分区，设置了default，则直接追加会报错，此时必须’拆分分区’：
– ORA-14074: 分区界限必须调整为高于最后一个分区界限
– ORA-14621: 在 DEFAULT 子分区已存在时无法添加子分区
ALTER TABLE EMP_PART_BY_YEAR_MOF MODIFY PARTITION year2027 ADD SUBPARTITION year2027_mof4322 VALUES (‘432200000’,‘432201000’);
– 删除指定名称的(主)分区(表中主分区对应的数据也会一并被删除)
– 如果被删除的分区是表中唯一的分区，则无法直接删除(此时报错)，必须删除表：ORA-14083: 无法删除分区表的唯一分区
ALTER TABLE EMP_PART_BY_YEAR_MOF DROP PARTITION YEAR2020 ;
–删除指定名称的子分区(表中子分区对应的数据也会一并被删除)
ALTER TABLE EMP_PART_BY_YEAR_MOF DROP SUBPARTITION YEAR2026_MOF4312;
– 截断某个分区是指删除某个分区中的数据，并不删除分区，也不会删除其它分区中的数据。
ALTER TABLE EMP_PART_BY_YEAR_MOF TRUNCATE PARTITION YEAR2028;
– 截断子分区 YEAR2028_MOF4312(删除子分区中的数据，保留分区)
ALTER TABLE EMP_PART_BY_YEAR_MOF TRUNCATE SUBPARTITION YEAR2028_MOF4312;
Oracle 拆分分区、合并分区
1、拆分分区将一个分区拆分两个新分区，拆分后原来分区不再存在。注意不能对HASH类型的分区进行拆分。
2、分区拆分之后，default 以及 maxvalue 中的数据也会重新整理，会自动进入匹配的新分区中。
3、比如范围分区一开始有范围，p1:(1)，p2:(2)，p3:(3)，pDafaule，其中 pDafaule中有数据 4，当将 pDafaule 拆分为 p4:(4) 和 pDafaule时，原先 pDafaule 中的数据4会自动进入了范围 p4 中。
– 创建 RANGE 分区表的时候指定了 maxvalue (最大值)时，追加分区时，必须 split(分割/拆分) 。
– 如下所示：表示将最大值分区 yearMax 使用 2028 进行分割，2028 分配给分区 year2027，maxvalue 仍然分配给自己(yearMax)
alter table EMP_PART_BY_YEAR_MOF split partition yearMax at (2028) into (partition year2027, partition yearMax);
– 创建 List 分区表的时候指定了 default (默认值)时，追加分区时，必须 split(分割/拆分) 。
– 如下所示：表示将子分区 year2027_mofDefault 拆分为 (‘432200000’,‘432201000’) 与 default，前者分配给 year2027_mof4322，后者分配给 year2027_mofDefault
ALTER TABLE EMP_PART_BY_YEAR_MOF SPLIT SUBPARTITION year2027_mofDefault VALUES (‘432200000’,‘432201000’) INTO (SUBPARTITION year2027_mof4322, SUBPARTITION year2027_mofDefault);
– 将主分区 YEAR20281(2029) 的值拆分为 2028，2029，前者分配给 YEAR2027 ，后者分配给 YEAR2028
ALTER TABLE EMP_PART_BY_YEAR_MOF split PARTITION YEAR20281 AT(2028) INTO (PARTITION YEAR2027,PARTITION YEAR2028);
3、合并分区是将相邻分区合并成一个分区，结果分区将采用较高分区的界限。
4、注意不能将分区合并到界限较低的分区，否则报错：ORA-14275: 不能将下界分区作为结果分区重用。
– 合并主分区(YEAR2026,YEAR2027,YEAR2028)-> YEAR2028(新分区名称可以随意定义)
ALTER TABLE EMP_PART_BY_YEAR_MOF MERGE PARTITIONS YEAR2026,YEAR2027,YEAR2028 INTO PARTITION YEAR2028;
– 合并子分区(YEAR20281_MOF4300,YEAR20281_MOF4301)-> YEAR20281_MOF4301(新分区名称可以随意定义)
ALTER TABLE EMP_PART_BY_YEAR_MOF MERGE SUBPARTITIONS YEAR20281_MOF4300, YEAR20281_MOF4301 INTO SUBPARTITION YEAR20281_MOF4301;
Oracle 重命名表分区
– 将主分区 YEAR2028 重命名为 YEAR_2028
ALTER TABLE EMP_PART_BY_YEAR_MOF RENAME PARTITION YEAR2028 TO YEAR_2028 ;
– 将子分区 SYS_SUBP3869 重命名为 YEAR2028_MOF4301
ALTER TABLE EMP_PART_BY_YEAR_MOF RENAME SUBPARTITION SYS_SUBP3869 TO YEAR2028_MOF4301 ;
Oracle 更新分区关键字列
1、Oracle 分区表创建之后，如果修改分区字段的值，默认就会报错：ORA-14402：更新分区关键字列将导致分区的更改
2、通常分区字段不需要修改，也不建议修改，如果一定要修改，则和 flashback 闪回数据一样，需要为表开启’行移动’功能。
–为指定表开启行移动功能
alter table tableName enable row movement;
–为指定表关闭行移动功能
alter table tableName disable row movement;
文章来源：添加链接描述