测试说明

本测试用于当分区表包含全局索引或是分区索引时，是否会影响删除静态数据分区操作（DDL）。

测试流程

建立测试表
测试4组分区表分区裁剪功能
测试4组分区表静态数据分区删除功能

测试结论

序号	测试大项	测试结论
1	无索引表分区裁剪功能	通过，可以正常使用分区裁剪
2	全局索引表分区裁剪功能	通过，可以正常使用分区裁剪
3	本地普通索引表分区裁剪功能	通过，可以正常使用分区裁剪
4	本地主键索引表分区裁剪功能	通过，可以正常使用分区裁剪
5	删除无索引静态分区是否上锁	通过，无论分区表存在DDL还是DML，都可以删除静态分区
6	删除全局索引静态分区是否上锁	通过，无论分区表存在DDL还是DML，都可以删除静态分区
7	删除本地索引静态分区是否上锁	通过，无论分区表存在DDL还是DML，都可以删除静态分区
8	删除本地主键静态分区是否上锁	通过，无论分区表存在DDL还是DML，都可以删除静态分区

版本信息

数据库版本：MogDB v3.0.3

数据库兼容模式：Oracle

测试步骤

1、建立无索引分区表全局索引分区表本地索引分区本地主键分区表

创建全局索引分区表：

CREATE TABLE t_part_noindex (


    id int not null,


    remark varchar,


    ctime date


) PARTITION BY RANGE (ctime)


(


    partition p1 values less than('2023-04-01'),


    partition p2 values less than('2023-07-01'),


    partition p3 values less than('2023-10-01'),


    partition p4 values less than(maxvalue)


)


ENABLE ROW MOVEMENT;

插入数据：

insert into t_part_noindex select generate_series(1,364),'global test',generate_series('2023-01-01'::date,'2023-12-30'::date,'1 day');

查看表结构：

\d+ t_part_noindex

创建全局索引分区表：

CREATE TABLE t_part_noindex (


    id int not null,


    remark varchar,


    ctime date


) PARTITION BY RANGE (ctime)


(


    partition p1 values less than('2023-04-01'),


    partition p2 values less than('2023-07-01'),


    partition p3 values less than('2023-10-01'),


    partition p4 values less than(maxvalue)


)


ENABLE ROW MOVEMENT;

插入数据：

insert into t_part_global select generate_series(1,364),'global test',generate_series('2023-01-01'::date,'2023-12-30'::date,'1 day');

创建全局索引：

create index ind_global on t_part_global (ctime) GLOBAL;

查看表结构：

\d+ t_part_global

创建本地索引分区表：

CREATE TABLE t_part_local (


    id int not null,


    remark varchar,


    ctime date


) PARTITION BY RANGE (ctime)


(


    partition p1 values less than('2023-04-01'),


    partition p2 values less than('2023-07-01'),


    partition p3 values less than('2023-10-01'),


    partition p4 values less than(maxvalue)


)


ENABLE ROW MOVEMENT;

插入数据：

insert into t_part_local select generate_series(1,364),'global test',generate_series('2023-01-01'::date,'2023-12-30'::date,'1 day');

创建本地索引（注意如果建立带有unique属性的本地索引，字段需要引用partition key）：

create index ind_local on t_part_local (ctime) LOCAL;

查看表结构：

\d+ t_part_local

创建本地索引分区表：

CREATE TABLE t_part_primary (


    id int not null,


    remark varchar,


    ctime date


) PARTITION BY RANGE (ctime)


(


    partition p1 values less than('2023-04-01'),


    partition p2 values less than('2023-07-01'),


    partition p3 values less than('2023-10-01'),


    partition p4 values less than(maxvalue)


)


ENABLE ROW MOVEMENT;

插入数据：

insert into t_part_primary select generate_series(1,364),'global test',generate_series('2023-01-01'::date,'2023-12-30'::date,'1 day');

创建主键（注意当主键索引列带partition key，会建立本地索引；如果不带partition key，则建立全局索引，本次测试建立带有local属性的主键）：

alter table t_part_primary add constraint pk_primary primary key(ctime);

查看表结构：

\d+ t_part_primary

查看分区索引：

select c.schemaname||'.'||b.parentid::regclass    table_name,


       a.parentid::regclass                       index_name,


       b.relname                                  table_partition_name,


       a.relname                                  index_partition_name,     


       a.indisusable                              index_partition_status


  from pg_partition a,pg_partition b,pg_stat_user_tables c


  where a.indextblid = b.oid(+)


  and b.parentid=c.relid


  and a.parttype='x'


  and a.parentid in (select indexrelid index_name from pg_index where indrelid in ('t_part_local'::regclass,'t_part_global'::regclass,'t_part_primary'::regclass))


  order by 1,2,3,4;

2、分区裁剪测试

收集统计信息：

analyze t_part_noindex;


analyze t_part_global;


analyze t_part_local;


analyze t_part_primary；

无索引表测试：

explain (analyze,verbose) select * from t_part_noindex where ctime='2023-01-15'::date;

全局索引表测试：

explain (analyze,verbose) select * from t_part_global where ctime='2023-01-15'::date;

本地分区表测试：

explain (analyze,verbose) select * from t_part_local where ctime='2023-04-15'::date;

本地主键表测试：

explain (analyze,verbose) select * from t_part_primary where ctime='2023-08-15'::date;

3、在线删除分区测试

模拟DQL在线事务：

begin;


explain (analyze,verbose) select * from t_part_noindx where ctime = '2023-04-01 00:00:00'::timestamp;


explain (analyze,verbose) select /*+ indexscan(t_part_global ind_global) */ * from t_part_global where ctime = '2023-04-01 00:00:00'::timestamp;


explain (analyze,verbose) select /*+ indexscan(t_part_local ind_local) */ * from t_part_local where ctime = '2023-04-01 00:00:00'::timestamp;


explain (analyze,verbose) select /*+ indexscan(t_part_local pk_primary) */ * from t_part_primary where ctime = '2023-04-01 00:00:00'::timestamp;

查询锁信息：

SELECT pid,database,locktype, relation::regclass,mode,granted FROM pg_locks where granted='t' and database is not null;

在线删除分区：

alter table t_part_noindex drop partition p1;


alter table t_part_global drop partition p1;


alter table t_part_local drop partition p1;


alter table t_part_primary drop partition p1;

模拟DML在线事务：

begin;


explain (analyze,verbose) update t_part_noindex set id = 1000 where ctime = '2023-04-01 00:00:00'::timestamp;


explain (analyze,verbose) update t_part_global set id = 1000 where ctime = '2023-04-01 00:00:00'::timestamp;


explain (analyze,verbose) update t_part_local set id = 1000 where ctime = '2023-04-01 00:00:00'::timestamp;


explain (analyze,verbose) update t_part_primary set id = 1000 where ctime = '2023-04-01 00:00:00'::timestamp;

查询锁信息：

SELECT pid,database,locktype, relation::regclass,mode,granted FROM pg_locks where granted='t' and database is not null;

在线删除分区：

alter table t_part_noindex drop partition p3;


alter table t_part_global drop partition p3;


alter table t_part_local drop partition p3;


alter table t_part_primary drop partition p3;

检查分区删除结果：

\d t_part_noindex


\d t_part_global


\d t_part_local


\d t_part_primary

注意事项

1、为分区表建立primary key时，如果索引字段有partition key，建立本地索引；如果没有partition key，则建立全局索引：

==>global primary key


alter table xx add constraint yy primary(普通列);


==>local primary key


alter table xx add constraint yy primary(普通列,分区列);

2、删除分区操作会使全局分区索引失效。

3、使用分区表+全局索引，在执行计划使用indexscan时，不进行分区裁剪。

关于作者

赵安琪，云和恩墨技术顾问，熟悉 openGauss MogDB / Oracle / MySQL，10年左右的金融、通信等行业关键业务系统的运维经验，目前主要负责 MogDB 数据库的国产化替代和信创改造工作。

ITPub博客

循序渐进丨MogDB 数据库分区表的静态数据分区删除测试

最新文章