分区表概述

背景信息

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

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

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

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

注意事项

普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。
仅Vastbase G100 V2.2 Build 10(Patch No.7)及以后版本支持Range分区表使用like进行模糊查询。
Hash分区只支持1个分区键，分区键值能用表的普通字段来指定，分区范围的值只支持常量表达式、数值或字符串常量，不支持新增和删除分区。
system分区表仅在数据库兼容模式为Oracle时能够使用（即创建DB时DBCOMPATIBILITY='A'），在其他数据库兼容模式下不能使用该特性。
system分区表暂不支持二级分区、组合分区。
暂不支持对列存表、MOT表进行分区操作。

操作步骤

示例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 |    16707 | h
 system_par_tab2 |    16707 | s
(2 rows)

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

\x
select * from pg_partition where parentid='16707';

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

-[ RECORD 1 ]------+----------------------------------------------------------------
relname            | system_par_tab2
parttype           | r
parentid           | 16707
partitionid        | 0
rangenum           | 0
intervalnum        | 0
partstrategy       | s
subpartstrategy    | n
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            |
subpartkey         |
intervaltablespace |
interval           |
boundaries         |
transit            |
reloptions         | {orientation=row,compression=no,fillfactor=80,wait_clean_gpi=n}
subparttemplate    |
relfrozenxid64     | 0
relminmxid         | 0
-[ RECORD 2 ]------+----------------------------------------------------------------
relname            | system_par_tab2
parttype           | p
parentid           | 16707
partitionid        | 0
rangenum           | 0
intervalnum        | 0
partstrategy       | h
subpartstrategy    | n
relfilenode        | 16711
reltablespace      | 0
relpages           | 0
reltuples          | 0
relallvisible      | 0
reltoastrelid      | 16712
reltoastidxid      | 0
indextblid         | 0
indisusable        | t
reldeltarelid      | 0
reldeltaidx        | 0
relcudescrelid     | 0
relcudescidx       | 0
relfrozenxid       | 14918
intspnum           |
partkey            |
subpartkey         |
intervaltablespace |
interval           |
boundaries         |
transit            |
reloptions         | {orientation=row,compression=no,fillfactor=80}
subparttemplate    |
relfrozenxid64     | 14918
relminmxid         | 2

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操作，且普通表不能是临时表。

VastbaseG100

分区表概述

背景信息

注意事项

操作步骤

弹框头部