【磐维数据库】分区表之分区表概述

Darcy 2024-06-01

748

一. 背景信息

Panwei数据库支持的分区表：Range分区表、List分区表、Hash分区表、system分区表和Interval分区表。

Range分区表：范围分区根据用户为每个分区建立的分区键值的范围将数据映射到分区。这种分区方式是最常见的，并且分区键值经常采用日期作为分区键，例如将销售数据按照月份进行分区。Range分区支持使用like进行模糊查询（即like的前缀匹配），参见示例2。当前使用like进行模糊查询支持字符串相关类型的分区键，如name、text、bpchar等。
List分区表：将数据中包含的键值分别存储在不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定，键值最多不超过127个。
Hash分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。
system分区表：系统分区可以对没有分区键的表进行分区。创建分区时可以指定分区名称，也可以不指定分区名称，由系统自动分配。
Interval分区表：interval分区是range分区的扩展。对于连续数据类型的Range分区，如果插入的新数据值与当前分区均不匹配，自动interval分区可以自动创建分区。

分区表和普通表相比具有以下优点：

改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。

Panwei支持分区剪枝功能，分区剪枝是指优化器自动提取出需要扫描的分区，减少扫描的数据块，从而避免全表扫描，提高性能。。

二. 注意事项

普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。
支持Range分区表使用like进行模糊查询。
Hash分区只支持1个分区键，分区键值能用表的普通字段来指定，分区范围的值只支持常量表达式、数值或字符串常量，不支持新增和删除分区。
system分区表仅在数据库兼容模式为Oracle时支持（即数据库实例初始化时指定DBCOMPATIBILITY=‘A’）。
system分区表暂不支持二级分区、组合分区。
暂不支持对列存表、MOT表进行分区操作。
交换分区的普通表，必须与分区表的字段个数、字段类型都完全一致时才可以进行exchange操作，且普通表不能是临时表（支持增删列之后的表进行分区交换）。

三. 操作步骤

示例1

按照以下方式对Range分区表进行操作。

说明：创建列存分区表的数量建议不超过1000个。

1、创建表空间example1和example2。

CREATE TABLESPACE example1 RELATIVE LOCATION 'tablespace/tablespace\_1';

CREATE TABLESPACE example2 RELATIVE LOCATION 'tablespace/tablespace\_2';

CREATE TABLESPACE example3 RELATIVE LOCATION 'tablespace/tablespace\_3';

CREATE TABLESPACE example4 RELATIVE LOCATION 'tablespace/tablespace\_4';

2、创建分区表并插入数据。

CREATE TABLE customer\_address

(

ca\_address\_sk integer NOT NULL ,

ca\_address\_id character(16) NOT NULL ,

ca\_street\_number character(10) ,

ca\_street\_name character varying(60) ,

ca\_street\_type character(15) ,

ca\_suite\_number character(10) ,

ca\_city character varying(60) ,

ca\_county character varying(30) ,

ca\_state character(2) ,

ca\_zip character(10) ,

ca\_country character varying(20) ,

ca\_gmt\_offset numeric(5,2) ,

ca\_location\_type character(20)

)

TABLESPACE example1

PARTITION BY RANGE (ca\_address\_sk)

(

PARTITION P1 VALUES LESS THAN(5000),

PARTITION P2 VALUES LESS THAN(10000),

PARTITION P3 VALUES LESS THAN(15000),

PARTITION P4 VALUES LESS THAN(20000),

PARTITION P5 VALUES LESS THAN(25000),

PARTITION P6 VALUES LESS THAN(30000),

PARTITION P7 VALUES LESS THAN(40000),

PARTITION P8 VALUES LESS THAN(MAXVALUE)

TABLESPACE example2

)

ENABLE ROW MOVEMENT;

insert into customer\_address values(12000,'a4','a3'),(20000,'w1','w2'),(50000,'w1','w2');

3、插入数据。

将表customer_address的数据插入到表web_returns_p2中。

例如在数据库中创建了一个表customer_address的备份表web_returns_p2，现在需要将表customer_address中的数据插入到表web_returns_p2中，则可以执行如下命令。

创建备份表web_returns_p2并插入数据。

CREATE TABLE web\_returns\_p2

(

ca\_address\_sk integer NOT NULL ,

ca\_address\_id character(16) NOT NULL ,

ca\_street\_number character(10) ,

ca\_street\_name character varying(60) ,

ca\_street\_type character(15) ,

ca\_suite\_number character(10) ,

ca\_city character varying(60) ,

ca\_county character varying(30) ,

ca\_state character(2) ,

ca\_zip character(10) ,

ca\_country character varying(20) ,

ca\_gmt\_offset numeric(5,2) ,

ca\_location\_type character(20)

)

TABLESPACE example1

PARTITION BY RANGE (ca\_address\_sk)

(

PARTITION P1 VALUES LESS THAN(5000),

PARTITION P2 VALUES LESS THAN(10000),

PARTITION P3 VALUES LESS THAN(15000),

PARTITION P4 VALUES LESS THAN(20000),

PARTITION P5 VALUES LESS THAN(25000),

PARTITION P6 VALUES LESS THAN(30000),

PARTITION P7 VALUES LESS THAN(40000),

PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2

)

ENABLE ROW MOVEMENT;

INSERT INTO web\_returns\_p2 SELECT \* FROM customer\_address;

4、重命名分区。

ALTER TABLE web\_returns\_p2 RENAME PARTITION P8 TO P\_9;

ALTER TABLE web\_returns\_p2 RENAME PARTITION FOR (40000) TO P8;

5、查询分区P8。

SELECT \* FROM web\_returns\_p2 PARTITION (P8);

返回结果为：

ca\_address\_sk | ca\_address\_id | ca\_street\_number | ca\_street\_name | ca\_street\_type | ca\_suite\_number | ca\_city | ca\_county | c

\---------------+------------------+------------------+----------------+----------------+-----------------+---------+-----------+--

50000 | w1 | w2 | | | | | |

(1 row)

6、删除分区表。

DROP TABLE web\_returns\_p2;

DROP TABLE customer\_address;

示例2

按照以下方式对Range分区表使用like进行模糊查询操作。

1、创建分区表。

create table tab\_1130900(

client\_code varchar(50),

client\_name varchar(50),

data\_date char(20)) with (ORIENTATION=column)

partition by range(data\_date)

(

partition p\_1 values less than ('2023-01-10'),

partition p\_2 values less than ('2023-01-11'),

partition p\_3 values less than ('2023-01-12'),

partition p\_4 values less than ('2023-01-13'),

partition p\_5 values less than ('2023-01-14'),

partition p\_6 values less than ('2023-01-15'),

partition p\_7 values less than ('2023-01-16'),

partition p\_11 values less than ('2023-02-10'),

partition p\_12 values less than ('2023-02-11'),

partition p\_13 values less than ('2023-02-12'),

partition p\_14 values less than ('2023-02-13'),

partition p\_15 values less than ('2023-02-14'),

partition p\_16 values less than ('2023-02-15'),

partition p\_17 values less than ('2023-02-16'),

partition p\_18 values less than (MAXVALUE)

);

2、插入测试数据。

insert into tab\_1130900 values('ccode01','ccname01','2023-01-15 11:36:22');

insert into tab\_1130900 values('ccode02','ccname02','2023-01-15 12:36:22');

insert into tab\_1130900 values('ccode01','ccname01','2023-01-14 11:36:22');

insert into tab\_1130900 values('ccode02','ccname02','2023-01-14 12:36:22');

insert into tab\_1130900 values('ccode01','ccname01','2023-01-13 11:36:22');

insert into tab\_1130900 values('ccode02','ccname02','2023-01-13 12:36:22');

insert into tab\_1130900 values('ccode01','ccname01','2023-01-12 11:36:22');

insert into tab\_1130900 values('ccode02','ccname02','2023-01-12 12:36:22');

insert into tab\_1130900 values('ccode01','ccname01','2023-01-11 11:36:22');

insert into tab\_1130900 values('ccode02','ccname02','2023-01-11 12:36:22');

insert into tab\_1130900 values('ccode01','ccname01','2023-01-10 11:36:22');

insert into tab\_1130900 values('ccode02','ccname02','2023-01-10 12:36:22');

insert into tab\_1130900 values('ccode01','ccname01','2023-01-09 11:36:22');

insert into tab\_1130900 values('ccode02','ccname02','2023-01-09 12:36:22');

insert into tab\_1130900 values('ccode02','ccname02');

insert into tab\_1130900 values('ccode01','ccname01','2023-02-15 11:36:23');

insert into tab\_1130900 values('ccode02','ccname02','2023-02-15 12:36:23');

insert into tab\_1130900 values('ccode01','ccname01','2023-02-14 11:36:23');

insert into tab\_1130900 values('ccode02','ccname02','2023-02-14 12:36:23');

insert into tab\_1130900 values('ccode01','ccname01','2023-02-13 11:36:23');

insert into tab\_1130900 values('ccode02','ccname02','2023-02-13 12:36:23');

insert into tab\_1130900 values('ccode01','ccname01','2023-02-12 11:36:23');

insert into tab\_1130900 values('ccode02','ccname02','2023-02-12 12:36:23');

insert into tab\_1130900 values('ccode01','ccname01','2023-02-11 11:36:23');

insert into tab\_1130900 values('ccode02','ccname02','2023-02-11 12:36:23');

insert into tab\_1130900 values('ccode01','ccname01','2023-02-10 11:36:23');

insert into tab\_1130900 values('ccode02','ccname02','2023-02-10 12:36:23');

insert into tab\_1130900 values('ccode01','ccname01','2023-02-09 11:36:23');

insert into tab\_1130900 values('ccode02','ccname02','2023-02-09 12:36:23');

3、使用like对Range分区进行模糊查询。

select count(\*) from tab\_1130900 where data\_date like '%23-01-09%';

select count(\*) from tab\_1130900 where data\_date like '2023-01-09%';

select count(\*) from tab\_1130900 where data\_date like '2023-02-09%';

select count(\*) from tab\_1130900 where data\_date like '2023-01-09%' or data\_date like '2023-02-09%' ;

查询结果依次显示为：

count

\-------

2

(1 row)

count

\-------

2

(1 row)

count

\-------

2

(1 row)

count

\-------

4

(1 row)

示例3

按照以下方式对Hash分区表进行操作。

说明：创建分区表（ Hash分区只支持1个分区键，分区键值能用表的普通字段来指定，分区范围的值只支持常量表达式、数值或字符串常量，不支持新增和删除分区。）

语法格式如下：

CREATE TABLE \[IF NOT EXISTS\] table\_name

…

PARTITION BY HASH (columns\_name)

hash\_partition\_desc

…

1、创建分区表。

CREATE TABLE t\_hash\_1

(c1 integer,

c2 date,

c3 text)

PARTITION BY HASH (c1)

(

PARTITION t\_hash\_p1,

PARTITION t\_hash\_p2

);

2、插入数据并查询结果。

insert into t\_hash\_1 values(1,'2020-07-29','a');

SELECT \* FROM t\_hash\_1 PARTITION (t\_hash\_p1);

返回结果为：

c1 | c2 | c3

\----+---------------------+----

1 | 2020-07-29 00:00:00 | a

(1 row)

3、更新数据并查询结果。

UPDATE t\_hash\_1 set c1=3 where c1=1;

SELECT \* FROM t\_hash\_1 PARTITION (t\_hash\_p2);

返回结果为：

c1 | c2 | c3

\----+---------------------+----

3 | 2020-07-29 00:00:00 | a

(1 row)

4、删除数据并查询结果。

DELETE t\_hash\_1 where c1=3;

SELECT \* FROM t\_hash\_1;

返回结果为：

c1 | c2 | c3

\----+----+----

(0 rows)

示例4

按照以下方式对List分区表进行操作。

1、创建分区表。

CREATE TABLE t\_list

(c1 integer,

c2 date,

c3 text)

PARTITION BY LIST (c2)

(

PARTITION p1 VALUES ('2019-10-12'),

PARTITION p2 VALUES ('2019-10-13'),

PARTITION p3 VALUES ('2019-10-14')

);

2、修改分区表行迁移属性。

alter table t\_list enable row movement;

3、新增分区。

ALTER TABLE t\_list ADD PARTITION P4 VALUES ('2019-10-15');

4、删除分区。

ALTER TABLE t\_list DROP PARTITION p4;

5、插入数据并查询结果。

insert into t\_list values(1,'2019-10-13','test');

SELECT \* FROM t\_list PARTITION (p2);

返回结果为：

c1 | c2 | c3

\----+---------------------+------

1 | 2019-10-13 00:00:00 | test

(1 row)

6、查看其它的分区：

SELECT \* FROM t\_list PARTITION (p1);

返回结果为：

c1 | c2 | c3

\----+----+----

(0 rows)

7、更新数据并查询结果。

update t\_list set c2='2019-10-12' where c1=1;

SELECT \* FROM t\_list PARTITION (p2);

SELECT \* FROM t\_list PARTITION (p1);

返回结果为：

c1 | c2 | c3

\----+----+----

(0 rows)

c1 | c2 | c3

\----+---------------------+------

1 | 2019-10-12 00:00:00 | test

(1 row)

示例5

按照以下方式对system分区表进行操作。

1、创建分区表（指定分区名称），并向其中插入数据。

create table system\_par\_tab(

c1 integer,

c2 date,

c3 text

)

partition by system

(

partition part\_1,

partition part\_2,

partition part\_3

);

2、创建分区表（系统自动分配分区名称），并向其中插入数据。

create table system\_par\_tab2(

id number,

code varchar2(10),

description varchar2(50)

)

partition by system;

3、查询系统自动生成的分区名称。

（1）查询系统表pg_partition。

\\d+ system\_par\_tab2

结果显示为：

Table "public.system\_par\_tab2"

Column | Type | Modifiers | Storage | Stats target | Description

\-------------+-------------+-----------+----------+--------------+-------------

id | numeric | | main | |

code | varchar(10) | | extended | |

description | varchar(50) | | extended | |

Partition By SYSTEM

Number of partitions: 1 (View pg\_partition to check each partition range.)

Has OIDs: no

Options: orientation=row, compression=no, fillfactor=80

（2）查询表记录。

select relname,parentid,partstrategy from pg\_partition where relname='system\_par\_tab2';

返回结果为：

relname | parentid | partstrategy

\-----------------+----------+--------------

system\_par\_tab2 | 60390 | h

system\_par\_tab2 | 60390 | s

(2 rows)

（3)执行\x命令开始列式输出模式并获取上面的parentid进行查询。

\\x

select \* from pg\_partition where parentid=’20942’;

返回结果为如下，其中system_par_tab2 就是系统自动生成的分区名：

\-\[ RECORD 1 \]------+----------------------------------------------------------------

relname | system\_par\_tab2

parttype | r

parentid | 60390

rangenum | 0

intervalnum | 0

partstrategy | s

relfilenode | 0

reltablespace | 0

relpages | 0

reltuples | 0

relallvisible | 0

reltoastrelid | 0

reltoastidxid | 0

indextblid | 0

indisusable | t

reldeltarelid | 0

reldeltaidx | 0

relcudescrelid | 0

relcudescidx | 0

relfrozenxid | 0

intspnum |

partkey |

intervaltablespace |

interval |

boundaries |

transit |

reloptions | {orientation=row,compression=no,fillfactor=80,wait\_clean\_gpi=n}

relfrozenxid64 | 0

relminmxid | 0

partkeyexpr |

partitionno | -1

subpartitionno |

\-\[ RECORD 2 \]------+----------------------------------------------------------------

relname | system\_par\_tab2

parttype | p

parentid | 60390

rangenum | 0

intervalnum | 0

partstrategy | s

relfilenode | 60394

reltablespace | 0

relpages | 0

reltuples | 0

relallvisible | 0

reltoastrelid | 60395

reltoastidxid | 0

indextblid | 0

indisusable | t

reldeltarelid | 0

reldeltaidx | 0

relcudescrelid | 0

relcudescidx | 0

relfrozenxid | 0

intspnum |

partkey |

intervaltablespace |

interval |

boundaries |

transit |

reloptions | {orientation=row,compression=no,fillfactor=80}

relfrozenxid64 | 46245

relminmxid | 2

partkeyexpr |

partitionno | 1

subpartitionno |

4、关闭列式输出模式向分区表中插入数据。

\\x off

insert into system\_par\_tab partition(part\_1) values(1,'2022-01-01','p1');

insert into system\_par\_tab partition(part\_2) values(2,'2022-02-01','p2');

insert into system\_par\_tab partition(part\_3) values(3,'2022-03-01','p3');

说明：向一个system分区表中插入数据时必须指定其分区名称。

5、查询分区表。

select \* from system\_par\_tab;

返回结果为：

c1 | c2 | c3

\----+---------------------+----

1 | 2022-01-01 00:00:00 | p1

2 | 2022-02-01 00:00:00 | p2

3 | 2022-03-01 00:00:00 | p3

6、更新数据。

update system\_par\_tab partition(part\_2) set c3='p5' where c1 ='2';

7、新增分区。

alter table system\_par\_tab add partition part\_4;

8、删除分区。

alter table system\_par\_tab drop partition part\_4;

9、清空分区表。

truncate table system\_par\_tab2;

10、清空指定分区。

alter table system\_par\_tab truncate partition part\_1;

示例6

system分区交换。

1、创建分区表并插入数据。

create table system\_par3

(c1 integer,

c2 date,

c3 text)

partition by system

(

partition p1,

partition p2,

partition p3

);

insert into system\_par3 partition(p1) values(1,'2022-01-01','p1');

insert into system\_par3 partition(p2) values(2,'2022-02-01','p2');

insert into system\_par3 partition(p3) values(3,'2022-03-01','p3');

2、创建普通表并插入数据。

create table system\_part

(c1 integer,

c2 date,

c3 text);

insert into system\_part values(4,'2022-04-01','p2');

insert into system\_part values(5,'2022-05-01','p2');

3、交换分区。

alter table system\_par3 exchange partition p2 with table system\_part;

4、查看交换结果。

select \* from system\_par3;

select \* from system\_par3 partition(p2);

select \* from system\_part;

返回结果为：

c1 | c2 | c3

\----+---------------------+----

1 | 2022-01-01 00:00:00 | p1

4 | 2022-04-01 00:00:00 | p2

5 | 2022-05-01 00:00:00 | p2

3 | 2022-03-01 00:00:00 | p3

(4 rows)

c1 | c2 | c3

\----+---------------------+----

4 | 2022-04-01 00:00:00 | p2

5 | 2022-05-01 00:00:00 | p2

(2 rows)

select \* from system\_part;

c1 | c2 | c3

\----+---------------------+----

2 | 2022-02-01 00:00:00 | p2

(1 row)

交换分区的普通表，必须与分区表的字段个数、字段类型都完全一致时才可以进行exchange操作，且普通表不能是临时表。

磐维磐维数据库 panwei panweidb

最后修改时间：2025-09-30 00:43:13

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

【磐维数据库】分区表之分区表概述

一. 背景信息

二. 注意事项

三. 操作步骤

示例1

示例2

示例3

示例4

示例5

示例6

评论