Oracle分区表
文章目录
- A. varchar2类型时间字段('20240102')分区实战
- 1. 表要不要分区
- 2. 将已经存在的表改造为分区表(时间字段,varchar2类型)
- 3. 增加分区
- 3.1 增加分区
- 3.2 置换分区,不会复制索引,不要用这种语法建表,这是专门为置换分区用的
- 3.3 分裂分区,20231202分裂点作为第一个分区的上限(不包含),分区中有没有数据都可以分裂
- 4. 查询分区信息
- B. oracle分区表详解(引用)
- 一、分区表概述
- 1.1 分区表概念
- 1.2 何时使用分区表
- 1.3 分区表的优点
- 1.3.1 提升SQL查询性能
- 1.3.2 提升表可管理性
- 1.3.3 提升数据可用性
- 二、基础分区策略
- 2.1 范围分区(Range Partition)
- 2.1.1 间隔分区(Interval partition)
- 2.2 哈希分区(Hash Partition)
- 2.3 列表分区(List partition)
- 三、扩展分区策略
- 3.1 复合分区(Composite Partition)
- 3.2 引用分区(Reference Partition)
- 3.3 虚拟列分区(Virtual Column-based Partition)
- 3.4 系统分区(System Partition)
- 四、分区表运维(Partition Maintenance)
- 4.1 新增分区
- 4.2 删除分区
- 4.3 置换分区
- 4.4 合并分区
- 4.5 分裂分区
- 4.6 移动分区
- 4.7 重命名分区
- 4.8 截断分区
- 五、常用分区表视图
- 5.1 dba_/all_/user_part_tables
- 5.2 dba_/all_/user_tab_partitions
- 5.3 dba_/all_/user_part_key_columns
- 5.4 dba_/all_/user_part_col_statistics
- 六、总结导图
A. varchar2类型时间字段(‘20240102’)分区实战
1. 表要不要分区
oracle表中数据量不超过一千万不需要加分区,没效果;
mysql表中数据量不超过五百万不需要加分区,没效果。
可以等到表中数据量达到这些量级再将表改造为分区表
分区定义是在表创建时(给表加分区是在表定义时就定义分区字段,可以给已经存在的表加分区是指创建表时已经有了分区定义,然后新加个分区范围)
2. 将已经存在的表改造为分区表(时间字段,varchar2类型)
如果按照时间类型字段进行分区,那么直接用间隔分区,不需要手动创建分区名了
对于已经存在的表加分区,可以先将表table_a重命名为table_a_bak,然后重新创建有分区定义的table_a表,最后将表table_a_bak数据迁移到table_a即可
-- 删除表(除非表中的数据不要了,不然应该选择给表重命名,而不是删除表)
BEGINexecute immediate 'DROP TABLE xin_teacher_t'; --引号里不能加分号,会报错,引号里语句不会执行成功EXCEPTIONWHEN OTHERS THENNULL;
END;
/-- 表重命名
ALTER TABLE xin_teacher_t RENAME TO xin_teacher_t_bak20231228;-- 重新定义表CREATE TABLE xin_teacher_t ("id" varchar2(20) NOT NULL ENABLE,"teacher_name" varchar2(20) NOT NULL ENABLE,"time_str" varchar2(20) NOT NULL ENABLE,"create_time" TIMESTAMP(6) NOT NULL ENABLE,constraint "XIN_TEACHER_T_IDX_ID" primary key ("ID")
) partition by RANGE(time_str)
(
partition TEACHER_202312 values LESS THAN ('20240101'),
partition TEACHER_202401 values LESS THAN ('20240201'),
partition TEACHER_202402 values LESS THAN ('20240301'),
partition TEACHER_202403 values LESS THAN ('20240401'),
partition TEACHER_202404 values LESS THAN ('20240501'),
partition TEACHER_202405 values LESS THAN ('20240601'),
partition TEACHER_202406 values LESS THAN ('20240701'),
partition TEACHER_202407 values LESS THAN ('20240801'),
partition TEACHER_202408 values LESS THAN ('20240901'),
partition TEACHER_202409 values LESS THAN ('20241001'),
partition TEACHER_202410 values LESS THAN ('20241101'),
partition TEACHER_202411 values LESS THAN ('20241201'),
partition TEACHER_202412 values LESS THAN ('20250101'),
partition TEACHER_MAX values LESS THAN (MAXVALUE)
);-- 插入表数据 65万数据大约耗时25秒
insert into xin_teacher_t select * from xin_teacher_t_bak20231228;
3. 增加分区
3.1 增加分区
alter table xin_teacher_t ADD PARTITION pp1 values less than (to_timestamp('20240401', 'yyyyMMdd'));
3.2 置换分区,不会复制索引,不要用这种语法建表,这是专门为置换分区用的
create table xin_teacher_t_ex for exchange with table xin_teacher_t;
3.3 分裂分区,20231202分裂点作为第一个分区的上限(不包含),分区中有没有数据都可以分裂
alter table xin_teacher_t split partition TEACHER_MAX at ('20250201') into (partition TEACHER_202501, partition TEACHER_MAX) update indexes;
alter table xin_teacher_t split partition TEACHER_MAX at ('20250301') into (partition TEACHER_202502, partition TEACHER_MAX) update indexes;
alter table xin_teacher_t split partition TEACHER_MAX at ('20250401') into (partition TEACHER_202503, partition TEACHER_MAX) update indexes;
4. 查询分区信息
-- 查询表中分区数
-- dba_ 所有、all_ 自己的+被赋权的、user_ 自己的
select * from USER_part_tables where table_name = 'xin_teacher_t';-- 查询表中的所有分区
select table_name, partition_name from USER_TAB_PARTITIONS WHERE table_name = 'xin_teacher_t';-- 查询所有的分区键信息
select * from ALL_PART_KEY_COLUMNS WHERE name = 'xin_teacher_t';-- 表的统计信息
select * from user_part_col_statistics where table_name = 'xin_teacher_t';-- 查询分区数据
select * from xin_teacher_t PARTITION (分区名);-- 查询语句条件中有分区字段即可,不需要指定分区名,所以我们要确保查询语句条件中带有分区字段。
-- 普通表改为分区表后,语句不需要额外改动,不需要加partition关键字
对于SQL查询,当where条件涉及分区键时,可以快速定位需要扫描的分区,这样可以将数据的扫描范围限制在很小的范围,极大的提升查询性能。
这个特性叫做分区裁剪(Partition Pruning)
B. oracle分区表详解(引用)
以下内容为引用
当单表数据量随着时间变的越来越大时,会给数据的管理和查询带来不便。我们可以考虑对表进行分区,利用分区表特性将数据分成小块存储,可以大幅提升查询性能,管理便捷性及数据的可用性。
一、分区表概述
1.1 分区表概念
分区表就是将表在物理存储层面分成多个小的片段,这些片段即称为分区,每个分区保存表的一部分数据,表的分区对上层应用是完全透明的,从应用的角度来看,表在逻辑上依然是一个整体。
每个分区都有自己的名字并可以拥有不同的存储特性,例如可以将分区保存在不同的磁盘以上分散I/O,或者分散在不同的表空间(表空间需要有相同的block size)。
向分区表插入数据时,为了判断每条数据应该被分配至哪个分区,我们通常需要选择定义一个分区键(Partition Key)。根据每条数据分区键的值或者对其运算的结果来决定数据的分区归属,分区键可以由1或多个列组成(最多16个列).
1.2 何时使用分区表
知道了分区表的概念,那么什么情况下应该使用分区表呢?如果遇到如下几个场景你可以考虑使用分区表:
- 表的大小超过2G
- 表中有大量的历史数据,数据存在明显的时间顺序
- 表的存储必须分散在不同的存储设备上
1.3 分区表的优点
分区表在结构和管理上比普通表更复杂,但它也有一定的优点,主要优点有以下3类:
1.3.1 提升SQL查询性能
对于SQL查询,当where条件涉及分区键时,可以快速定位需要扫描的分区,这样可以将数据的扫描范围限制在很小的范围,极大的提升查询性能。这个特性叫做分区裁剪(Partition Pruning)。
另外,在多表连接(join)时,如果在每个表在连接的键上都进行了分区,那么Oracle可以将两个大表之间的连接转换成更小的分区级连接,极大提升连接速度,这个特性叫做分区连接(Partition-wise Join)。
1.3.2 提升表可管理性
使用分区表之后,原来表级别的管理操作也被分散为至“分区级”,各个分区上独立的进行运维任务,原先一个大表上的运维任务,现在可以拆开成一系列小任务分散在不同的时间窗口执行。例如,平时备份表的操作,现在可以备份单个分区。
1.3.3 提升数据可用性
当表分区后,每个分区都具有独立性。在你操作某个分区时,不会影响其他分区数据的使用,即使某个分区因为故障不可用,也完全不会影响其他分区上运行的事务。同时分区可以存储在不同的表空间/物理介质上,分散I/O压力。
二、基础分区策略
根据不同的应用场景,你可以为表选择不同的分区策略,Oracle提供的基础分区策略有:
- 范围分区(Range Partition)
- 哈希分区(Hash Partition)
- 列表分区(List partition)
在基础分区策略的基础上,还有一些其他的扩展分区策略,后面再进行讨论。
2.1 范围分区(Range Partition)
范围分区根据预先定义的范围来划分分区,范围分区最适合管理类似且有明显顺序的数据,根据数据的顺序可以很容易划定分区范围。范围分区最典型的应用场景就是按时间对数据进行分区,所以其经常使用时间类型的分区键。
范围分区表是通过 create table 语句的 partition by range 子句来创建的,分区的范围通过 values less than 子句指定,其指定的是分区的上限(不包含),所有大于等于指定值的数据被分配至下一个分区,除了第一个分区,每个分区的下限即前一个分区的上限:
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个分区:
- 所有create_time小于’2023-02-01’的数据(不包含)被分配在分区p1中。
- 所有create_time小于’2023-03-01’的数据(不包含)被分配在p2中。
- 所有create_time大于等于’2023-03-01’的数据被分配在pmax中,如果没有这个分区,那么插入大于等于’2023-03-01’的数据时,会因为没有合适的存储分区而报错。
你也可以在定义分区时指定存储特性,例如将分区分散到不同的表空间(表空间可以放到不同的物理磁盘上):
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, -- 指定分区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 间隔分区(Interval partition)
间隔分区是范围分区的一个扩展,它也是通过范围来划分分区,唯一的区别是:间隔分区可以在相应分区数据插入时自动创建分区,省去了普通范围分区手动创建分区的操作。
如果不是需要创建不规则的范围分区,那么更推荐使用间隔分区来替代范围分区,你只需要指定一个分区间隔及初始分区,后续的分区创建将由Oracle自动完成。
间隔分区表的创建由在普通范围分区定义上新增一个interval子句创建:
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'))
);
上面的例子指定在初始分区p1的基础上,每隔1个月创建一个分区。
通过视图user_tab_partitions可以看到目前只有1个分区p1:
select table_name, partition_name from user_tab_partitions where table_name=‘INV_PART’;
我们在初始分区的上限之上插入一条数据:
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';
在现有分区之上插入数据时,Oracle自动为我们创建了1个对应的分区SYS_P327。
对于间隔分区,你也可以通过 store in 子句指定多个表空间,Oracle将以循环的方式在各个表空间中创建分区。
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
);
查询初始分区的所属表空间:
select table_name, partition_name, tablespace_name from user_tab_partitions where table_name=‘MULTI_TBS’;
插入两条数据,触发自动创建新的分区:
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自动以循环的方式在3个表空间中创建了分区。
2.2 哈希分区(Hash Partition)
哈希分区是对指定的分区键(Partition Key)运行哈希算法来决定数据存储在哪个分区。哈希分区会随机的将数据分配到各个分区中,并尽量平均,保证各个分区的大小差不多一致。
由于数据是随机分布,所以哈希分区并不适合管理有明显时间顺序的历史数据。它更适合需要将数据平均的分布到各个不同存储设备上的场景。同时在选用哈希分区时建议满足下列条件:
- 选取分区键时尽量选取唯一列(Unique)或列中有大量唯一值(Almost Unique)的列。
- 创建哈希分区时,分区的数量尽量是2的幂,例如2,4,8,16等。
哈希分区表是通过 create table 语句的 partition by hash 子句来创建的,创建时你可以显式的指定每个分区名称,所属表空间。
create table hash_part1 (
id number,
name varchar2(32))
partition by hash(id)
(
partition p1 tablespace tbs1,
partition p2 tablespace tbs2
);
也可以仅指定哈希分区的数量,此时Oracle会自动为每个分区生成名字:
create table hash_part2 (
id number,
name varchar2(32))
partition by hash(id)
partitions 2; -- 指定哈希分区数量,不用指定分区名
你也可以用 store in 子句让分区以循环的方式建立在各个表空间中:
create table hash_part3 (
id number,
name varchar2(32))
partition by hash(id)
partitions 4
store in (tbs1, tbs2, tbs3);
2.3 列表分区(List partition)
列表分区是由你为每个分区指定一系列的离散值(列表),当分区键等于特定的离散值时,数据会被放到相应的分区。列表分区可以让你自定义数据的组织方式,例如按照地域来分类数据。
列表分区表是通过 create table 语句的 partition by list 子句来创建的,创建时你需要为每个分区指定一个列表(离散值)。
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 值的分区,这样所有没有预先定义的分区键值都会放入该分区,否则会报错:
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)
);
列表分区建立完成后,你可以很方便的利用 alter table … modify partition … add/drop values ( … ) 来修改列表分区的枚举值:
alter table list_part2 modify partition p_jiangsu add values('YangZhou');
alter table list_part2 modify partition p_jiangsu drop values('YangZhou');
如果列表分区是子分区,只需要将 modify partition 替换为 modify subpartition 即可。
三、扩展分区策略
除了前面介绍的3种基础分区策略,Oracle还提供一些其他的分区策略,它们都是在基础分区策略上进行某种功能的扩充。
3.1 复合分区(Composite Partition)
复合分局,顾名思义,就是将多种分区策略结合起来使用,在基础分区的策略上,对每个分区再一次应用分区策略。例如,在基础的范围分区基础上,还可以对每个分区再次应用范围分区,即每个分区又被划分为若干个子分区。类似于中国可以划分为很多省(分区),每个省又可以划分为很多市(子分区)。
在使用复合分区时,3种基础分区策略可以随意组合,例如,使用范围分区作为基础分区,其子分区可以使用范围、哈希、列表分区策略,即:
- 范围-范围分区
- 范围-哈希分区
- 范围-列表分区
其他两种分区类型同理,因此复合分区共有3*3=9种方案。
子分区是通过原来分区策略上通过新增 subpartition子句来定义的,下面我们以范围分区(间隔分区)为基础分区,演示三种子分区的创建方式
comp_part1的采用范围-哈希分区策略:
create table comp_part1 (
id number,
name varchar2(32),
create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH')) -- 范围分区(间隔分区)
subpartition by hash(id) subpartitions 4 -- 子分区采用哈希分区,每个范围分区再分为4个哈希分区
(partition p1 values less than (to_date('2023-02-01', 'yyyy-mm-dd'))
);
comp_part2的采用范围-范围分区策略:
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'))
);
comp_part3的采用范围-列表分区策略:
create table comp_part3 (
id number,
name varchar2(32),
sex varchar2 (32),
create_time date)
partition by range(create_time) interval (numtoyminterval(1, 'MONTH')) -- 范围分区(间隔分区)
subpartition by list(sex) -- 子分区通过性别进行划分
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 引用分区(Reference Partition)
引用分区是一种基于主-外键引用关系的分区策略,如果两张表上定义了外键引用,即两张表存在父-子关系(Parent-Child Realtionship),那么基于这种主键-外键引用关系,可以使子表继承主表的分区策略。
引用分区特别适合在需要自动维护子表,或者两表频繁连接查询的场景,因为他们的分区策略是相同的,两表连接通常会被转换为分区连接(partition-wise join),大大缩小连接的结果集。
引用分区是通过partition by reference创建的。例如,下面两张表parent_table和child_table 定义了引用分区:
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);
下面我们验证一下引用分区的继承,通过视图 user_tab_partitions 可以看到,初始child_table也继承了主表初始分区
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 中插入一条数据,触发间隔分区的自动新建分区特性:
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中插入一条数据:
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';
可以看到,在子表插入数据的时候,对应的分区也自动创建了出来(且分区编号都相同)。
当我们在主表上删除分区时,对应的子表上的分区也被自动删除了:
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 虚拟列分区(Virtual Column-based Partition)
虚拟列分区即分区键可以定义在虚拟列上,虚拟列分区使分区键可以定义在一个表达式上,这个表达式会被保存为元数据,而列并不实际存在于数据库中。虚拟列分区可以与任何分区策略结合使用。
下面示例中,表 virtual_part 上通过salary和bonus定义了一个虚拟列income,然后将income作为分区键:
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 系统分区(System Partition)
前介绍的分区策略都是由数据库来决定数据放在哪个分区,分区对应用都是透明的。而系统分区可以仅建立一个分区表,但不指定分区策略,因此它没有分区键和分区规则。系统分区对上层应用不是透明的,应用往系统分区插入数据时,SQL必须显式的指定分区名,否则会报错。
系统分区通过 create table 的 partition by system 子句创建,后续只需要定义分区,不需要分区键:
create table system_part (
id number primary key,
name varchar2(32))
partition by system(
partition p1,
partition p2
);
系统分区的数据存储完全由应用决定,因此在插入数据时,必须显示指定数据保存的分区:
insert into system_part values (1, 'Vincent');
仅通过表名插入数据时报错:系统分区还需要提供分区扩展名
insert into system_part partition(p1) values (1, 'Vincent');
插入时显式指定分区,插入成功。
四、分区表运维(Partition Maintenance)
在日常运行中,我们有时候还需要对分区表进行一些维护操作,下面是一些常见的运维案例。
4.1 新增分区
手动新增分区,不同的分区类型操作稍微有些不同。注意间隔分区和引用分区的分区都是自动创建的,因此它们无法手动新增分区。
范围分区可以使用alter table … add partition 手动新增分区,注意仅可以在范围分区最大范围的上面新增分区,如果已经定义了最大值分区(maxvalue)或者想要在中间插入一个分区,则只可以使用分裂分区来完成(后面会介绍):
alter table members drop partition pmax; --由于建表时定义了p_max,要先删除才能演示,实际应用中要注意p_max分区是否有数据
alter table members add partition p3 values less than (to_date('2023-04-01', 'yyyy-mm-dd'));
哈希分区直接alter table … add partition 即可,你可以指定分区名,也可以不指定分区名,数据会重新在各分区中进行分布,可能需要一些时间:
alter table hash_part1 add partition p3 tablespace tbs3;alter table hash_part2 add partition tablespace tbs3;
列表分区直接 alter table … add partition 新增一个分区定义:
alter table list_part1 add partition p_anhui values('HeFei', 'ChuZhou');
4.2 删除分区
使用 alter table … drop partition 可以删除指定的分区,对于范围分区、间隔分区,列表分区,直接指定要删除的分区名即可,间隔分区虽然无法显式新增分区,但是可以显式删除:
alter table members drop partition p3;
引用分区无法显式删除,因为它的分区策略继承自父表,只有当父表删除分区时,子表上的引用分区才会级联删除(前面演示过)。
对于哈希分区,我们无法直接删除分区。如果要减少分区的数量,必须采用一个叫 coalesce partition (融合分区)的操作,下面的示例会将哈希分区的数量减少1个。这个操作虽然减少了一个分区,但是并不会丢失数据,数据会在剩下的分区中重新分布。
alter table hash_part1 coalesce partition;
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;
如果是12cR2以上的版本,你还可以用 create table … for exchange with table … 语句来快速创建一个与分区表完全匹配的非分区表:
create table mem_ext for exchange with table members;
将mem_ext表与members表的p2分区进行置换:
alter table members exchange partition p2 with table mem_ext; -- 由于预先插入违反分区规则的数据导致报错alter table members exchange partition p2 with table mem_ext without validation;
如果置换的分区中有不符合分区规则的数据(第二条),可以用 without validation 子句跳过数据验证(仅更新数据字典)。
当交换分区或者更新分区键时,可能会导致数据的分区归属变化(下面第一个报错),这时候Oracle就需要在不同分区移动数据,我们可以在建表的时候开启行移动(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 子句,你可以将两个分区合并成一个。合并分区仅适用于范围、间隔、列表分区类型,哈希和引用分区不适用。
对于范围分区,你只能将相邻两个的分区进行合并,且只能合并到边界高的分区,例如下面,由于p2分区上限更高,只能将分区p1合并至p2,不能将p2合并至p1:
alter table members merge partitions p1, p2 into partition p2 update indexes;
合并分区时,建议带上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)。
列表分区由于分区之间没有顺序,因此你可以合并任意两个分区,合并后的分区包含两个分区的所有数据,以下面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')
);
我们将不相邻的分区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 分裂分区
当某个分区过大时,你可能想要将它分裂成2个分区。分裂分区是合并分区的逆向操作,和合并分区的限制一样,分裂分区也仅适用于范围、间隔、列表分区类型,哈希和引用分区不适用。
分裂操作会重新将数据在2个分区中进行分布,现在以上面一节合并的分区为示例,再将它们分开。
分裂范围分区,我们需要指定一个分裂点(包含在分区内),整个分区将以这个分裂点为边界拆分为2个分区,分裂点会作为第一个分区的上限(不包含),下面示例将范围分区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;
分裂间隔分区和分裂范围分区类似,我们也需要指定一个分裂点。且分裂间隔分区和和合并间隔分区一样,也会导致所有低于被分裂分区上限的间隔分区都转换为范围分区,被分裂分区的上限即范围分区和间隔分区的分界点。我们将上面示例的最后一个间隔分区 - 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)。
分裂列表分区,你需要指定需要分裂出去的值,这些指定的值会分配到第一个分区,原分区剩余的值会分配到第二个分区。
在上面一节列表分区合并操作中,我们将p1和p3合并成了p_merged,现在再将它们分开:
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';
观察分裂前后的分区枚举值,我们指定’a’, 'b’被分裂出去,那么它们将被放入p1,剩余的值会被放入p3。
4.6 移动分区
移动分区可以让你随意将某个分区移动其他表空间,这种情况通常用在需要将分区迁移到另一个存储设备上。同时也可以顺便对分区进行一些其他操作,例如压缩。所有类型的分区策略都支持移动分区。
要移动分区至其他表空间,使用alter table的 move partition 子句,:
alter table interval_part move partition p1 tablespace tbs1 update indexes compress;
移动分区实际是在新目的地新建一个分区,并将原分区删除(drop),即使目的地是相同的表空间也是如此。
4.7 重命名分区
你可以用 alter table … rename partition … to … 来给指定的分区重命名,重命名没有限制,所有分区策略都可以使用:
alter table interval_part rename partition sys_p447 to p2;
4.8 截断分区
需要彻底清除某个分区数据时,你可以用 alter table … truncate partition … 来彻底清除该分区的数据(所有分区策略都适用)。
alter table interval_part truncate partition p2 update indexes;
五、常用分区表视图
分区表有一组相关视图,可以供我们查询分区信息,例如前面用到的user_table_partitions,这些视图都有三个级别,分别以dba_,all_,user_开头:
- dba_ 开头的视图可以查询所有信息
- all_ 开头的时候可以查询有权限访问的信息(归属自己 + 被赋权的)
- user_ 开头的视图可以查询归属自己对象的信息
5.1 dba_/all_/user_part_tables
该组视图显示表级别的分区信息(每个分区表一条数据):
select * from all_part_tables;
主要字段含义解释:
5.2 dba_/all_/user_tab_partitions
该组视图显示分区级别的分区信息(每个分区一条数据):
select * from all_tab_partitions;
主要字段含义解释:
另外 dba_/all_/user_tab_subpartitions 视图显示信息类似,显示子分区级别的信息。
5.3 dba_/all_/user_part_key_columns
该组视图显示分区键信息:
select * from all_part_key_columns;
主要字段含义解释:
另外 dba_/all_/user_subpart_key_columns 视图显示信息类似,显示子分区级别的信息。
5.4 dba_/all_/user_part_col_statistics
改组视图显示列相关的统计信息
select * from all_part_col_statistics;
主要字段含义解释:
另外 dba_/all_/user_subpart_col_statistics 视图显示信息类似,显示子分区级别的信息。
六、总结导图
相关文章:

Oracle分区表
文章目录 A. varchar2类型时间字段(20240102)分区实战1. 表要不要分区2. 将已经存在的表改造为分区表(时间字段,varchar2类型)3. 增加分区3.1 增加分区3.2 置换分区,不会复制索引,不要用这种语法建表,这是专门为置换分区用的3.3 分…...

【leetcode】力扣算法之旋转图像【难度中等】
题目描述 给定一个 n n 的二维矩阵 matrix 表示一个图像。请你将图像顺时针旋转 90 度。 你必须在 原地 旋转图像,这意味着你需要直接修改输入的二维矩阵。请不要 使用另一个矩阵来旋转图像。 用例 输入: matrix [[1,2,3],[4,5,6],[7,8,9]] 输出&…...

【Java集合类篇】HashMap的数据结构是怎样的?
HashMap的数据结构是怎样的? ✔️HashMap的数据结构✔️ 数组✔️ 链表 ✔️HashMap的数据结构 在Java中,保存数据有两种比较简单的数据结构: 数组和链表(或红黑树)。 HashMap是 Java 中常用的数据结构,它实现了 Map 接口。Has…...
Spring 应用合并之路(一):摸石头过河 | 京东云技术团队
公司在推进降本增效,在尝试多种手段之后,发现应用太多,每个应用都做跨机房容灾部署,则最少需要 4 台机器(称为容器更合适)。那么,将相近应用做一个合并,减少维护项目,提高…...
Android13配置selinux让system应用可读sys,proc,SN号
system权限应用读sys,proc目录及SN号 Android13预置的system应用,需要读/sys, /proc目录,读(SN)serial number号, 需要修改selinux配置,否则会报avc错. 其修改方法会比Android11复杂一些. 实现 system_app.te中添加…...
防勒索病毒攻击的关键措施
【作者】朱向东 中原银行 高级工程师 在当今数字化时代,勒索病毒成为了企业和个人面临的一项严峻威胁。勒索病毒攻击可以导致数据丢失、系统瘫痪以及经济损失。为了保护自己和组织的利益,采取一系列的防范措施是至关重要的。下面是一些关键的措施&#…...

代表团坐车 - 华为OD统一考试
OD统一考试(B卷) 分值: 100分 题解: Java / Python / C++ 题目描述 某组织举行会议,来了多个代表团同时到达,接待处只有一辆汽车可以同时接待多个代表团,为了提高车辆利用率,请帮接待员计算可以坐满车的接待方案输出方案数量。 约束: 一个团只能上一辆车,并且代表团…...

运用Jmeter进行登录测试
开始了解Jmeter,写篇关于Jmeter的博客做备忘,这里以苏宁易购网站的登录请求为例实战来说明测试计划元件,创建一个 Web 测试计划。 今天简单介绍Jemeter的入门,Jmeter 的安装这边就跳过,直接讲述如何使用JMETER,如何运用Jmeter进行测试。 a.下载jmeter软件 b.安装…...

Docker学习与应用(四)-容器数据卷
1、容器数据卷 1)什么是容器数据卷 docker的理念回顾 将应用和环境打包成一个镜像! 数据?如果数据都在容器中,那么我们容器删除,数据就会丢失!需求:数据可以持久化 MySQL,容器删…...

CentOS 7.6下HTTP隧道代理的安全性考虑
在CentOS 7.6上配置HTTP隧道代理时,安全性是一个不可忽视的重要因素。以下是对HTTP隧道代理安全性的一些关键考虑因素: 1. 加密和数据安全 使用强加密算法:确保您使用的是经过广泛认可和强化的加密算法,如AES-256-GCM。数据完整…...
Mockito+junit5搞定单元测试
目录 一、简介1.1 单元测试的特点1.2 Mock类框架的使用场景1.3 常见的Mock框架1.3.1 Mockito1.3.2 EasyMock1.3.3 PowerMock1.3.4 Testable1.3.5 比较 二、Mockito的使用2.1 导入pom文件2.2 mock对象和spy对象2.3 初始化mock/spy对象的方式2.4 参数匹配2.5 方法插桩2.6 InjectM…...
PostgreSQL获取当天、昨天、本月、上个月、本年、去年的数据
gps_time为timestamp类型日期字段 获取当天的数据 WHERE DATE_TRUNC(day, gps_time) CURRENT_DATE --或 WHERE DATE(gps_time) CURRENT_DATE获取昨天的数据 WHERE DATE_TRUNC(day, gps_time) CURRENT_DATE - INTERVAL 1 day获取本月的数据 WHERE DATE_TRUNC(month, gps_…...

XCTF:stage1[WriteUP]
从题目中下载到图片: 考虑图片是png,隐写方式有可能是高宽修改,也可能是色相隐藏,色彩通道位隐藏等等 使用stegsolve对图片进行一下伽马、颜色转换 在图片的左上角就显示出了一个二维码 使用QR_Rresearch工具对二维码扫描 获得一…...

STM32CubeMX教程13 ADC - 单通道转换
目录 1、准备材料 2、实验目标 3、ADC概述 4、实验流程 4.0、前提知识 4.1、CubeMX相关配置 4.1.1、时钟树配置 4.1.2、外设参数配置 4.1.3、外设中断配置 4.2、生成代码 4.2.1、外设初始化调用流程 4.2.2、外设中断调用流程 4.2.3、添加其他必要代码 5、常用函数…...

矩阵的乘法
首先矩阵的乘法定义如下: #include <stdio.h> int main() { int i 0; int j 0; int arr[20][20] { 0 }; int str[20][20] { 0 }; int s[20][20] { 0 }; int n1 0; int n2 0; int m2 0; int z 0; int m1 0;…...
python爬取招聘网站数据
这段代码是使用Selenium自动化测试模块进行网页爬取的示例代码。它通过模拟人的行为在浏览器中操作网页来实现爬取。具体的流程如下: 导入所需的模块,包括Selenium、时间、随机、csv等模块。打开浏览器,创建一个Chrome浏览器实例。设置要爬取…...

灌区信息化方案(什么是现代化灌区,如何一步到位)
一、系统概述 详情:https://www.key-iot.com.cn/ 本灌区信息化方案以星创易联公司的各类智能设备为基础,通过其产品完成水文、雨情、土壤等多源异构数据的采集,以无线自组网的方式实现数据传输,并在后台管理中心建立信息化软件平台,对数据进行融合处理。系统实现对…...

jmeter自动录制脚本功能
问题排查: 建议用 google浏览器; 重启一下jmeter; 过滤规则重新检查下; 看下代理设置是否正常; 注意:下面的的过滤设置中 用的都是正则表达式的规则。...

十一、工具盒类(MyQQ)(Qt5 GUI系列)
目录 编辑 一、设计需求 二、实现代码 三、代码解析 四、总结 一、设计需求 抽屉效果是软件界面设计中的一种常用形式,可以以一种动态直观的方式在有限大小的界面上扩展出更多的功能。本例要求实现类似 QQ 抽屉效果。 二、实现代码 #include "dialog.…...
postgresql 查询字段 信息
SELECT base.“column_name”, col_description ( t1.oid, t2.attnum ), base.udt_name, COALESCE(character_maximum_length, numeric_precision, datetime_precision), (CASE WHEN ( SELECT t2.attnum ANY ( conkey ) FROM pg_constraint WHERE conrelid t1.oid AND contyp…...

MPNet:旋转机械轻量化故障诊断模型详解python代码复现
目录 一、问题背景与挑战 二、MPNet核心架构 2.1 多分支特征融合模块(MBFM) 2.2 残差注意力金字塔模块(RAPM) 2.2.1 空间金字塔注意力(SPA) 2.2.2 金字塔残差块(PRBlock) 2.3 分类器设计 三、关键技术突破 3.1 多尺度特征融合 3.2 轻量化设计策略 3.3 抗噪声…...
SciencePlots——绘制论文中的图片
文章目录 安装一、风格二、1 资源 安装 # 安装最新版 pip install githttps://github.com/garrettj403/SciencePlots.git# 安装稳定版 pip install SciencePlots一、风格 简单好用的深度学习论文绘图专用工具包–Science Plot 二、 1 资源 论文绘图神器来了:一行…...

UDP(Echoserver)
网络命令 Ping 命令 检测网络是否连通 使用方法: ping -c 次数 网址ping -c 3 www.baidu.comnetstat 命令 netstat 是一个用来查看网络状态的重要工具. 语法:netstat [选项] 功能:查看网络状态 常用选项: n 拒绝显示别名&#…...

第 86 场周赛:矩阵中的幻方、钥匙和房间、将数组拆分成斐波那契序列、猜猜这个单词
Q1、[中等] 矩阵中的幻方 1、题目描述 3 x 3 的幻方是一个填充有 从 1 到 9 的不同数字的 3 x 3 矩阵,其中每行,每列以及两条对角线上的各数之和都相等。 给定一个由整数组成的row x col 的 grid,其中有多少个 3 3 的 “幻方” 子矩阵&am…...

中医有效性探讨
文章目录 西医是如何发展到以生物化学为药理基础的现代医学?传统医学奠基期(远古 - 17 世纪)近代医学转型期(17 世纪 - 19 世纪末)现代医学成熟期(20世纪至今) 中医的源远流长和一脉相承远古至…...
A2A JS SDK 完整教程:快速入门指南
目录 什么是 A2A JS SDK?A2A JS 安装与设置A2A JS 核心概念创建你的第一个 A2A JS 代理A2A JS 服务端开发A2A JS 客户端使用A2A JS 高级特性A2A JS 最佳实践A2A JS 故障排除 什么是 A2A JS SDK? A2A JS SDK 是一个专为 JavaScript/TypeScript 开发者设计的强大库ÿ…...

C# 表达式和运算符(求值顺序)
求值顺序 表达式可以由许多嵌套的子表达式构成。子表达式的求值顺序可以使表达式的最终值发生 变化。 例如,已知表达式3*52,依照子表达式的求值顺序,有两种可能的结果,如图9-3所示。 如果乘法先执行,结果是17。如果5…...
比较数据迁移后MySQL数据库和OceanBase数据仓库中的表
设计一个MySQL数据库和OceanBase数据仓库的表数据比较的详细程序流程,两张表是相同的结构,都有整型主键id字段,需要每次从数据库分批取得2000条数据,用于比较,比较操作的同时可以再取2000条数据,等上一次比较完成之后,开始比较,直到比较完所有的数据。比较操作需要比较…...

给网站添加live2d看板娘
给网站添加live2d看板娘 参考文献: stevenjoezhang/live2d-widget: 把萌萌哒的看板娘抱回家 (ノ≧∇≦)ノ | Live2D widget for web platformEikanya/Live2d-model: Live2d model collectionzenghongtu/live2d-model-assets 前言 网站环境如下,文章也主…...

嵌入式学习之系统编程(九)OSI模型、TCP/IP模型、UDP协议网络相关编程(6.3)
目录 一、网络编程--OSI模型 二、网络编程--TCP/IP模型 三、网络接口 四、UDP网络相关编程及主要函数 编辑编辑 UDP的特征 socke函数 bind函数 recvfrom函数(接收函数) sendto函数(发送函数) 五、网络编程之 UDP 用…...