做网站视频图片加载不出来,网页设计怎么建站点,在家帮别人做网站赚钱,信息手机网站模板下载软件文章目录 A. varchar2类型时间字段(20240102)分区实战1. 表要不要分区2. 将已经存在的表改造为分区表(时间字段#xff0c;varchar2类型)3. 增加分区3.1 增加分区3.2 置换分区#xff0c;不会复制索引#xff0c;不要用这种语法建表#xff0c;这是专门为置换分区用的3.3 分… 文章目录 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 Partition2.1.1 间隔分区Interval partition 2.2 哈希分区Hash Partition2.3 列表分区List partition 三、扩展分区策略3.1 复合分区Composite Partition3.2 引用分区Reference Partition3.3 虚拟列分区Virtual Column-based Partition3.4 系统分区System Partition 四、分区表运维Partition Maintenance4.1 新增分区4.2 删除分区4.3 置换分区4.4 合并分区4.5 分裂分区4.6 移动分区4.7 重命名分区4.8 截断分区 五、常用分区表视图5.1 dba_/all_/user_part_tables5.2 dba_/all_/user_tab_partitions5.3 dba_/all_/user_part_key_columns5.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 PruningB. 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_nameINV_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_nameMULTI_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*39种方案。
子分区是通过原来分区策略上通过新增 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_namePARENT_TABLE;select table_name, partition_name, tablespace_name from user_tab_partitions where table_nameCHILD_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_namePARENT_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_nameCHILD_TABLE;可以看到在子表插入数据的时候对应的分区也自动创建了出来且分区编号都相同。
当我们在主表上删除分区时对应的子表上的分区也被自动删除了
alter table parent_table drop partition SYS_P391;select table_name, partition_name, tablespace_name from user_tab_partitions where table_nameCHILD_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_nameMEMBERS;创建一个与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_time2023-03-03 00:00:00 where id3; -- 更新分区键会导致切换分区报错alter table members enable row movement;update members set create_time2023-03-03 00:00:00 where id3; -- 分区键更新后数据会被移动到正确的分区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、811月的间隔分区创建出来
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;可以看到我们插入数据触发的新建分区属于间隔分区intervalYES
select table_name, partition_name, interval from user_tab_partitions where table_nameINTERVAL_PART;下面将相邻的78月分区进行合并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_nameINTERVAL_PART;可以看到78月分区SYS_448, SYS_P449消失了生成了一个新的分区SYS_P451原先边界范围在合并分区之下的3月分区SYS_P447也被转换成了范围分区intervalNO而合并分区之上11月的分区SYS_P450依然是间隔分区(intervalYES)。
列表分区由于分区之间没有顺序因此你可以合并任意两个分区合并后的分区包含两个分区的所有数据以下面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_nameLIST_PART;alter table list_part merge partitions p1,p3 into partition p_merged;select table_name, partition_name from user_tab_partitions where table_nameLIST_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_nameINTERVAL_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_nameINTERVAL_PART;分区SYS_P450分裂成了SYS_P467和SYS_P468同时低于原分区上限的所有分区都会被转换为范围分区intervalNO。
分裂列表分区你需要指定需要分裂出去的值这些指定的值会分配到第一个分区原分区剩余的值会分配到第二个分区。
在上面一节列表分区合并操作中我们将p1和p3合并成了p_merged现在再将它们分开
select table_name, partition_name, high_value from user_tab_partitions where table_nameLIST_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_nameLIST_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 视图显示信息类似显示子分区级别的信息。
六、总结导图
