【背景】公司业务系统的数据增长很快,需要进行定期进行归档,其中有些数据用分区表进行存储,归档完之后释放了大量的空间,之前已经有文档介绍普通表进行shrink操作的步骤,本文档记录对分区表和复合分区表进行shrink的操作;
点击( 此处 )折叠或打开
【进行删除数据的测试】
【测试环境的搭建】
数据库版本:11.2.0.3
分区表的创建
点击(此处)折叠或打开
- CREATE TABLE SCOTT.PTABLE
- (
- GG1DM VARCHAR2(9 BYTE),
- SL NUMBER(18,4) ,
- DJBH VARCHAR2(20 BYTE)
- )
- NOCOMPRESS
- PARTITION BY LIST (GG1DM)
- SUBPARTITION BY range (SL)
- (
- PARTITION PTABLE_P1 VALUES ('07')
- (
- SUBPARTITION PTABLE1_SUB1 VALUES less than (50) ,
- SUBPARTITION PTABLE1_SUB2 VALUES less than (100),
- SUBPARTITION PTABLE1_SUB3 VALUES less than (150),
- SUBPARTITION PTABLE1_SUB4 VALUES less than (250)
- )
- ,
- PARTITION PTABLE_P2 VALUES ('08')
- (SUBPARTITION PTABLE2_SUB1 VALUES less than (50) ,
- SUBPARTITION PTABLE2_SUB2 VALUES less than (100),
- SUBPARTITION PTABLE2_SUB3 VALUES less than (150),
- SUBPARTITION PTABLE2_SUB4 VALUES less than (250)
- ),
- PARTITION PTABLE_P3 VALUES ('09')
- ( SUBPARTITION PTABLE3_SUB1 VALUES less than (50) ,
- SUBPARTITION PTABLE3_SUB2 VALUES less than (100),
- SUBPARTITION PTABLE3_SUB3 VALUES less than (150),
- SUBPARTITION PTABLE3_SUB4 VALUES less than (250)
- )
- )
点击(此处)折叠或打开
- 插入数据后需要更新统计信息:
- analyze table PTABLE compute statistics;
-
- 分区表的查询
- select t3.table_name, t3.partition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed
- from dba_tab_partitions t3 where t3.table_name='PTABLE' order by t3.num_rows desc
![](https://yqfile.alicdn.com/img_d27369a33097197a8db7e2e86fca54dc.png?x-oss-process=image/resize,w_1400/format,webp)
点击( 此处 )折叠或打开
- 子分区表信息的查询
- select t3.partition_name,t3.subpartition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed
- from dba_tab_subpartitions t3 where t3.table_name='PTABLE' order by t3.num_rows desc
![](https://yqfile.alicdn.com/img_2a92b55e06a56d1fbafcd6a93e0383ad.png?x-oss-process=image/resize,w_1400/format,webp)
【进行删除数据的测试】
点击(此处)折叠或打开
- DELETE FROM SCOTT.PTABLE WHERE GG1DM='07' AND SL<=50 删除PTABLE1_SUB1里面的数据
- analyze table PTABLE compute statistics; 进行统计信息的更新;
然后查看此时子分区的高水位线情况
当前PTABLE_P1表的数据是0,但是HMW线还是没有变;
【进行高水位线的回收】
显示当前的高水位线已经回收了;
【延伸】
【总结】经过以上操作可以得出其实分区表的shrink操作和普通表的操作是一样的,原理也都是一样的;
点击(此处)折叠或打开
- select t3.partition_name,t3.subpartition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed
- from dba_tab_subpartitions t3 where t3.table_name='PTABLE' order by t3.num_rows desc
![](https://yqfile.alicdn.com/img_53099cb88d959b575c13ac1f08114eed.png?x-oss-process=image/resize,w_1400/format,webp)
当前PTABLE_P1表的数据是0,但是HMW线还是没有变;
【进行高水位线的回收】
点击(此处)折叠或打开
- alter table PTABLE enable row movement; #开启行迁移
- alter table PTABLE MODIFY SUBPARTITION PTABLE1_SUB1 shrink space; #进行子分区空间的收缩
- analyze table PTABLE compute statistics; #再次更新统计信息
- select t3.partition_name,t3.subpartition_name,t3.high_value,t3.num_rows,t3.blocks,t3.empty_blocks,t3.last_analyzed
- from dba_tab_subpartitions t3 where t3.table_name='PTABLE' order by t3.num_rows desc; #查看子分区的情况
![](https://yqfile.alicdn.com/img_422a689f141879014c6e5328c757ed08.png?x-oss-process=image/resize,w_1400/format,webp)
显示当前的高水位线已经回收了;
1、如果是分区里面的多个子分区需要回收高水位线,就可以单独的对这个子分区进行回收的操作;
点击(此处)折叠或打开
- alter table PTABLE MODIFY PARTITION PTABLE_P2 shrink space
2、如果单个子分区里面的数据都不要了,还是可以用truncate进行操作,直接删除高水位线;
点击(此处)折叠或打开
- ALTER TABLE PTABLE TRUNCATE SUBPARTITION PTABLE3_SUB1 ; 子分区的truncate
- ALTER TABLE PTABLE TRUNCATE PARTITION PTABLE_P; 分区表的truncate;
【总结】经过以上操作可以得出其实分区表的shrink操作和普通表的操作是一样的,原理也都是一样的;