Oracle表分区的创建、新增、拆分

Oracle中为了方便管理、查询数据当数据量大于500w或者2G时最好用分区表,常见的一种是使用时间作为分区。

分区表添加新的分区有 2 种情况:
(1) 原分区里边界是 maxvalue 或者 default。 这种情况下,我们需要把边界分区 drop 掉,加上新分区后,再添加上新的分区。 或者采用 split,对边界分区进行拆分
(2) 没有边界分区的。 这种情况下,直接添加分区就可以了。

创建分区表:

CREATE TABLE WJZ.wjz_t1
   (	ID NUMBER, 
	STR1 VARCHAR2(20), 
	STR2 VARCHAR2(20), 
	STR3 VARCHAR2(20), 
  tdate date,
  constraint pk_id_t1 primary key(id)
   ) 
  PARTITION BY RANGE (TDATE)
  (PARTITION P202212  VALUES LESS THAN (TO_DATE('2023-01-01','YYYY-MM-DD')),
   PARTITION POTHER VALUES LESS THAN (MAXVALUE) )

然后插入数据

insert into WJZ_T1 values(1,'str1','str2','abc',to_date('2022-11-10 00:00:01','yyyy-mm-dd hh24:mi:ss'));
insert into WJZ_T1 values(2,'str1','str2','abc',to_date('2022-12-10 00:00:01','yyyy-mm-dd hh24:mi:ss'));
insert into WJZ_T1 values(3,'str1','str2','abc',to_date('2023-1-10 00:00:01','yyyy-mm-dd hh24:mi:ss'));
insert into WJZ_T1 values(4,'str1','str2','abc',to_date('2023-1-12 00:00:01','yyyy-mm-dd hh24:mi:ss'));
insert into WJZ_T1 values(5,'str1','str2','abc',to_date('2023-2-10 00:00:01','yyyy-mm-dd hh24:mi:ss'));
insert into WJZ_T1 values(6,'str1','str2','abc',to_date('2023-2-14 00:00:01','yyyy-mm-dd hh24:mi:ss'));
insert into WJZ_T1 values(7,'str1','str2','abc',to_date('2023-3-10 00:00:01','yyyy-mm-dd hh24:mi:ss'));

其中增加POHTER分区是为了防止不符合分区条件是数据插入报错,但是如果直接新增分区会报错ORA-14074: 分区界限必须调整为高于最后一个分区界限,根本原因是存在最后maxvalue分区

ALTER TABLE wjz_t1 ADD PARTITION P202301 VALUES LESS THAN(TO_DATE('2023-02-01','YYYY-MM-DD'));

 解决方法是拆分分区,在split partition时,根据split point原来的partition分裂成两个partition。

同时,观察在拆分分区前查看索引情况: 

select a.index_name,a.status from user_indexes a where table_name in ('WJZ_T1') ;

拆分分区SQL:

ALTER TABLE wjz_t1 SPLIT PARTITION POTHER AT (TO_DATE('2023-02-01','YYYY-MM-DD')) INTO (PARTITION P202301, PARTITION POTHER);
ALTER TABLE wjz_t1 SPLIT PARTITION POTHER AT (TO_DATE('2023-04-01','YYYY-MM-DD')) INTO (PARTITION P202303, PARTITION POTHER);

 新增表分区成功,如下

 

查看表索引也是正常的:

 

 但是如果要新增P202302表分区的话,以下会报错:

ALTER TABLE wjz_t1 SPLIT PARTITION POTHER AT (TO_DATE('2023-03-01','YYYY-MM-DD')) INTO (PARTITION P202302, PARTITION POTHER);

 所以分区拆分的时候,必须从小的开始,否则会提示如下报错

ORA-14080:无法按指定的上限来分割分区

正确的是

ALTER TABLE wjz_t1 SPLIT PARTITION P202303 AT (TO_DATE('2023-03-01','YYYY-MM-DD')) INTO (PARTITION P202302, PARTITION P202303);

  

此时查看表索引有问题:

 需要重建索引:

ALTER INDEX PK_ID_T1 REBUILD ONLINE;

或者在拆分分区的时候使用以下也会重建索引:

ALTER TABLE wjz_t1 SPLIT PARTITION P202303 AT (TO_DATE('2023-03-01','YYYY-MM-DD')) INTO (PARTITION P202302, PARTITION P202303) UPDATE INDEXES ;

所以如果清理表时,如果时间分区忘了增加,可以拆分默认表分区来新增表分区,然后再通过删除分区方式来清理数据:

alter table WJZ_T1 drop partition P202302;

 但需要注意的是观察分区表的索引情况。

如果表分区split的时候将tablespace更换了,那么也需要rebuild index .

参考文章:

oracle11.2中add&split partition 对全局及本地index的影响 - 知乎

oracle分区表split,分区表split操作及maxvalue处理_科技体验者的博客-CSDN博客

Oracle Partition 分区详细总结_wounler的博客-CSDN博客 

  • 1
    点赞
  • 16
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
创建 Oracle 分区的一般步骤如下: 1. 创建分区分区策略。这个步骤包括选择分区键(用于决定如何分区)、选择分区方法(例如按范围或按列分区)以及选择分区数目。 2. 创建分区。在创建时,需要指定分区策略。在中定义分区键列。 3. 创建分区创建分区时,可以指定分区的名称、分区的范围(例如分区的日期范围)、分区的存储属性等。 以下是一个示例,展示如何创建一个基于时间范围的分区: ``` CREATE TABLE sales_data ( id NUMBER, sale_date DATE, product_name VARCHAR2(50), sale_amount NUMBER(12, 2) ) PARTITION BY RANGE(sale_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION sales_data_2019 VALUES LESS THAN (TO_DATE('2019-12-01', 'YYYY-MM-DD')), PARTITION sales_data_2020 VALUES LESS THAN (TO_DATE('2020-12-01', 'YYYY-MM-DD')), PARTITION sales_data_2021 VALUES LESS THAN (TO_DATE('2021-12-01', 'YYYY-MM-DD')) ); ``` 在上面的示例中,我们创建了一个基于 `sale_date` 列的分区。使用 `PARTITION BY RANGE` 语句来指定分区方法和分区键列。使用 `INTERVAL` 子句来指定新分区的时间间隔(这里设置为每个月)。我们还使用 `VALUES LESS THAN` 子句创建了三个分区,分别对应于 2019 年、2020 年和 2021 年。请注意,我们还可以在创建后动态添加新分区。 当分区创建后,我们可以像处理常规一样处理分区。例如,可以使用 `INSERT INTO` 语句向分区中插入数据。Oracle 自动将每个插入的行放入适当的分区中。可以使用 `SELECT` 语句查询分区中的数据,也可以使用其他常规操作来处理分区

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

大宇进阶之路

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值