openGauss 5.0.0 分区表和分区索引操作示例 -- cnDBA.cn

1 分区表

openGauss 5.0.0 中支持4种类型的分区表：

范围分区表：指定一个或多个列划分为多个范围，每个范围创建一个分区，用来存储相应的数据。例如可以采用日期划分范围，将销售数据按照月份进行分区。
列表分区表：直接按照一个列上的值来划分出分区。例如可以采用销售门店划分销售数据。
间隔分区表：是一种特殊的范围分区，新增了间隔值定义。当插入记录找不到匹配的分区时可以根据间隔值自动创建分区。
哈希分区表：根据表的一列，为每个分区指定模数和余数，将要插入表的记录划分到对应的分区中。

同时对这些分区表还支持如下操作：

查询分区表：按照分区名或者分区中的值查询数据。
导入数据：直接导入数据或从现有表格中导入。
修改分区表：包括增加分区、删除分区、切割分区、合并分区，以及修改分区名称等。
删除分区表：与删除普通表格相同。

1.1 范围分区表示例

范围分区表有两种类型：

VALUES LESS THAN：通过给出每个分区的上限来确定分区范围。上个分区的上限<=分区的范围<本分区的上限。
START END：通过以下方式划分：
分区的起点和终点；
仅给出分区起点；
仅给出分区终点；
给出分区起点和终点后，再给出该范围内的间隔值。
以上这些方式的综合应用。

1.1.1 VALUES LESS THAN 范围分区表

创建语法：

CREATE TABLE partition_table_name 
( [column_name data_type ] [, ... ] ) 
PARTITION BY RANGE (partition_key) 
( PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE}) 
[, ... ] 
);

对于VALUE LESS THAN的语法格式，范围分区策略的分区键最多支持4列。

创建范围分区表sales_table，含有4个分区，分区键为DATE类型。

分区的范围分别为：
sales_date<2021-04-01，
2021-04-01<= sales_date<2021-07-01，
2021-07-01<=sales_date< 2021-10-01，
2021-10-01 <= sales_date< MAXVALUE。

[dave@www.cndba.cn ~]$ gsql -d postgres -p 15500 -r
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# /l
                          List of databases
   Name    | Owner | Encoding  | Collate | Ctype | Access privileges 
-----------+-------+-----------+---------+-------+-------------------
 cndba     | omm   | UTF8      | C       | C     | 
 postgres  | omm   | SQL_ASCII | C       | C     | 
 template0 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
 template1 | omm   | SQL_ASCII | C       | C     | =c/omm           +
           |       |           |         |       | omm=CTc/omm
(4 rows)

openGauss=# /c cndba
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "cndba" as user "omm".
cndba=#CREATE TABLE sales_table
(
    order_no              INTEGER          NOT NULL,
    goods_name            CHAR(20)         NOT NULL,
    sales_date            DATE             NOT NULL,
    sales_volume          INTEGER,
    sales_store           CHAR(20)
)
PARTITION BY RANGE(sales_date)
(
        PARTITION season1 VALUES LESS THAN('2021-04-01 00:00:00'),
        PARTITION season2 VALUES LESS THAN('2021-07-01 00:00:00'),
        PARTITION season3 VALUES LESS THAN('2021-10-01 00:00:00'),
        PARTITION season4 VALUES LESS THAN(MAXVALUE)
);

cndba=# INSERT INTO sales_table VALUES(1, 'jacket', '2021-01-10 00:00:00', 3,'Alaska');
INSERT 0 1
cndba=# INSERT INTO sales_table VALUES(2, 'hat', '2021-05-06 00:00:00', 5,'Clolorado');
INSERT 0 1
cndba=# INSERT INTO sales_table VALUES(3, 'shirt', '2021-09-17 00:00:00', 7,'Florida');
INSERT 0 1
cndba=# INSERT INTO sales_table VALUES(4, 'coat', '2021-10-21 00:00:00', 9,'Hawaii');
INSERT 0 1
cndba=#

查询分区表数据：

cndba=# select * from sales_table;
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store      
----------+----------------------+---------------------+--------------+----------------------
        1 | jacket               | 2021-01-10 00:00:00 |            3 | Alaska              
        2 | hat                  | 2021-05-06 00:00:00 |            5 | Clolorado           
        3 | shirt                | 2021-09-17 00:00:00 |            7 | Florida             
        4 | coat                 | 2021-10-21 00:00:00 |            9 | Hawaii              
(4 rows)

查询某个分区的数据：

cndba=#  select * from sales_table PARTITION (season4);
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store      
----------+----------------------+---------------------+--------------+----------------------
        4 | coat                 | 2021-10-21 00:00:00 |            9 | Hawaii              
(1 row)

查询sales_table的1季度数据。这里采用“sales_table PARTITION FOR (‘2021-3-21 00:00:00’)”来引用第1季度数据所在分区。其中的’2021-3-21 00:00:00’处于第1季度所在分区。

cndba=# SELECT * FROM sales_table PARTITION FOR ('2021-3-21 00:00:00');
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store      
----------+----------------------+---------------------+--------------+----------------------
        1 | jacket               | 2021-01-10 00:00:00 |            3 | Alaska              
(1 row)

cndba=#

1.1.2 创建START END范围分区表语法格式

START END范围分区表有多种表达方式，而且这些方式可以在一个分区表内组合使用。

方式一：START(partition_value) END (partition_value | MAXVALUE)方式
CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY RANGE (partition_key) 
         (
         PARTITION partition_name START(partition_value) END (partition_value | MAXVALUE)
         [, ... ]
         ); 

注意：对于从句是START END的语法格式，范围分区策略的分区键仅支持1列。

方式二：START(partition_value)方式
CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
] )
     PARTITION BY RANGE (partition_key)  
         (
         PARTITION partition_name START(partition_value) 
         [, ... ]  
         ); 

方式三：END(partition_value | MAXVALUE)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
] )
     PARTITION BY RANGE (partition_key)  
         (
         PARTITION partition_name END(partition_value | MAXVALUE) 
         [, ... ]  
         ); 


方式四：START(partition_value) END (partition_value) EVERY (interval_value)方式

CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
] )
     PARTITION BY RANGE (partition_key)  
         (
         PARTITION partition_name START(partition_value) END (partition_value) EVERY (interval_value)
         [, ... ]  
         ); 

注意：对于“START(partition_value) END (partition_value) EVERY (interval_value)”从句，假定其中的partition_name是p1，则分区的名称依次为p1_1, p1_2, …。

比如：“PARTITION p1 START(1) END(4) EVERY(1)”，则生成的分区是：[1, 2), [2, 3) 和 [3, 4)，名称依次为p1_1, p1_2和p1_3，即此处的p1是名称前缀。

1.1.2.1 示例1

以“START(partition_value) END (partition_value | MAXVALUE)”方式创建START END范围分区表graderecord。含有3个分区，分区键为INTEGER类型。

分区的范围分别为：

0<= grade<60，
60<= grade<90，
90<=grade< MAXVALUE。

创建分区表：

[dave@www.cndba.cn ~]$ gsql -d postgres -p 15500 -r
gsql ((openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

openGauss=# /c cndba
Non-SSL connection (SSL connection is recommended when requiring high-security)
You are now connected to database "cndba" as user "omm".
cndba=# CREATE TABLE graderecord  
  (  
  number INTEGER,  
  name CHAR(20),  
  class CHAR(20),  
  grade INTEGER
  )  
  PARTITION BY RANGE(grade)  
  (  
  PARTITION pass START(60) END(90),  
  PARTITION excellent START(90) END(MAXVALUE)
  );

cndba=# insert into graderecord values('210101','Alan','21.01',92);  
INSERT 0 1
cndba=# insert into graderecord values('210102','Ben','21.01',62);  
INSERT 0 1
cndba=# insert into graderecord values('210103','Brain','21.01',26);  
INSERT 0 1
cndba=# insert into graderecord values('210204','Carl','21.02',77);  
INSERT 0 1
cndba=# insert into graderecord values('210205','David','21.02',47);  
INSERT 0 1
cndba=# insert into graderecord values('210206','Eric','21.02',97);  
INSERT 0 1
cndba=# insert into graderecord values('210307','Frank','21.03',90);  
INSERT 0 1
cndba=# insert into graderecord values('210308','Gavin','21.03',100);  
INSERT 0 1
cndba=# insert into graderecord values('210309','Henry','21.03',67);  
INSERT 0 1
cndba=# insert into graderecord values('210410','Jack','21.04',75);  
INSERT 0 1
cndba=# insert into graderecord values('210311','Jerry','21.04',60);  
INSERT 0 1

查询分区表：

cndba=# SELECT * FROM graderecord;
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210103 | Brain                | 21.01                |    26
 210205 | David                | 21.02                |    47
 210102 | Ben                  | 21.01                |    62
 210204 | Carl                 | 21.02                |    77
 210309 | Henry                | 21.03                |    67
 210410 | Jack                 | 21.04                |    75
 210410 | Jack                 | 21.04                |    75
 210311 | Jerry                | 21.04                |    60
 210101 | Alan                 | 21.01                |    92
 210206 | Eric                 | 21.02                |    97
 210307 | Frank                | 21.03                |    90
 210308 | Gavin                | 21.03                |   100
(12 rows)

cndba=# SELECT * FROM graderecord PARTITION (pass);
ERROR:  partition "pass" of relation "graderecord" does not exist
cndba=#

这里查看失败pass 分区失败是因为分区名不对，原因“PARTITION pass START(60) END(90),”是第一个分区定义，且该定义有START值，则范围（MINVALUE, 60）将自动作为第一个实际分区，其名称为“pass_0”。而该定义语义描述的“60<= grade<90”分区的名称为“pass_1”。

查看分区信息：

cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='graderecord') order by relname asc;
  relname  | partstrategy 
-----------+--------------
 excellent | r
 pass_0    | r
 pass_1    | r
(3 rows)

cndba=# SELECT * FROM graderecord PARTITION (pass_0);
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210103 | Brain                | 21.01                |    26
 210205 | David                | 21.02                |    47
(2 rows)

cndba=# SELECT * FROM graderecord PARTITION (pass_1);
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210102 | Ben                  | 21.01                |    62
 210204 | Carl                 | 21.02                |    77
 210309 | Henry                | 21.03                |    67
 210410 | Jack                 | 21.04                |    75
 210410 | Jack                 | 21.04                |    75
 210311 | Jerry                | 21.04                |    60
(6 rows)

cndba=#

1.1.2.2 示例2

以“START(partition_value) ”方式创建START END范围分区表graderecord。含有3个分区，分区键为INTEGER类型。分区的范围分别为：0<= grade<60，60<= grade<90，90<=grade< MAXVALUE。

cndba=# drop table graderecord;
DROP TABLE
cndba=# CREATE TABLE graderecord  
  (  
  number INTEGER,  
  name CHAR(20),  
  class CHAR(20),  
  grade INTEGER
  )  
  PARTITION BY RANGE(grade)  
  (  
  PARTITION pass START(60),  
  PARTITION excellent START(90)
  );

cndba=#  insert into graderecord values('210101','Alan','21.01',92);  
INSERT 0 1
cndba=#  insert into graderecord values('210102','Ben','21.01',62);  
INSERT 0 1
cndba=#  insert into graderecord values('210103','Brain','21.01',26);  
INSERT 0 1
cndba=#  insert into graderecord values('210204','Carl','21.02',77);  
INSERT 0 1
cndba=#  insert into graderecord values('210205','David','21.02',47);  
INSERT 0 1
cndba=#  insert into graderecord values('210206','Eric','21.02',97);  
INSERT 0 1
cndba=#  insert into graderecord values('210307','Frank','21.03',90);  
INSERT 0 1
cndba=#  insert into graderecord values('210308','Gavin','21.03',100);  
INSERT 0 1
cndba=#  insert into graderecord values('210309','Henry','21.03',67);  
INSERT 0 1
cndba=#  insert into graderecord values('210410','Jack','21.04',75);  
INSERT 0 1
cndba=#  insert into graderecord values('210311','Jerry','21.04',60);
INSERT 0 1
cndba=#

查看分区：

cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='graderecord') order by relname asc;
  relname  | partstrategy 
-----------+--------------
 excellent | r
 pass_0    | r
 pass_1    | r
(3 rows)

“PARTITION pass START(60),”是第一个分区定义，且该定义有START值，则范围（MINVALUE, 60）将自动作为第一个实际分区，其名称为“pass_0”。
而该定义语义描述的“60<= grade<90”分区的名称为“pass_1”。

1.1.2.3 示例3

以“END(partition_value | MAXVALUE) ”方式创建START END范围分区表graderecord。含有3个分区，分区键为INTEGER类型。分区的范围分别为：0<= grade<60，60<= grade<90，90<=grade< MAXVALUE。。

cndba=# drop table graderecord;
DROP TABLE
cndba=# CREATE TABLE graderecord  
  (  
  number INTEGER,  
  name CHAR(20),  
  class CHAR(20),  
  grade INTEGER
  )  
  PARTITION BY RANGE(grade)  
  (  
  PARTITION no_pass END(60),  
  PARTITION pass END(90),
  PARTITION excellent END(MAXVALUE)
  );

cndba=#  insert into graderecord values('210101','Alan','21.01',92);  
 insert into graderecord values('210102','Ben','21.01',62);  
INSERT 0 1
cndba=#  insert into graderecord values('210102','Ben','21.01',62);  
INSERT 0 1
cndba=#  insert into graderecord values('210103','Brain','21.01',26);  
INSERT 0 1
cndba=#  insert into graderecord values('210204','Carl','21.02',77);  
INSERT 0 1
cndba=#  insert into graderecord values('210205','David','21.02',47);  
INSERT 0 1
cndba=#  insert into graderecord values('210206','Eric','21.02',97);  
INSERT 0 1
cndba=#  insert into graderecord values('210307','Frank','21.03',90);  
INSERT 0 1
cndba=#  insert into graderecord values('210308','Gavin','21.03',100);  
INSERT 0 1
cndba=#  insert into graderecord values('210309','Henry','21.03',67);  
INSERT 0 1
cndba=#  insert into graderecord values('210410','Jack','21.04',75);  
INSERT 0 1
cndba=#  insert into graderecord values('210311','Jerry','21.04',60);
INSERT 0 1
cndba=#

查看分区:

cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='graderecord') order by relname asc;
  relname  | partstrategy 
-----------+--------------
 excellent | r
 no_pass   | r
 pass      | r
(3 rows)

cndba=#  SELECT * FROM graderecord PARTITION (no_pass);
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210103 | Brain                | 21.01                |    26
 210205 | David                | 21.02                |    47
(2 rows)

1.1.2.4 示例4

以“START(partition_value) END (partition_value) EVERY (interval_value)”方式创建START END范围分区表metro_ride_record。含有7个分区，分区键为INTEGER类型。总范围是ride_stations_number<21，每3站为一个分区。

cndba=# CREATE TABLE metro_ride_record  
  (  
  record_number INTEGER,  
  name CHAR(20),  
  enter_station CHAR(20),
  leave_station CHAR(20),
  ride_stations_number INTEGER
  )  
  PARTITION BY RANGE(ride_stations_number)  
  (  
  PARTITION cost START(3) END(21)  EVERY (3)
   );

cndba=# insert into metro_ride_record values('120101','Brain','Tung Chung','Tsing Yi',2);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120102','David','Po Lam','Yau Tong',4);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120103','Ben','Yau Ma Tei','Wong Tai Sin',6);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120104','Carl','Tai Wo Hau','Prince Edward',8);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120105','Henry','Admiralty','Lai King',10);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120106','Jack','Chai Wan','Central',12);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120107','Jerry','Central','Tai Wo Hau',14);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120108','Alan','Diamond Hill','Kwai Hing',16);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120109','Eric','Jordan','Shek Kip Mei',18);  
INSERT 0 1
cndba=# insert into metro_ride_record values('120110','Frank','Lok Fu','Sunny Bay',20);  
INSERT 0 1
cndba=#

查询分区：

“PARTITION cost START(3) END(21)  EVERY (3)”是第一个分区定义，且该定义有START值，则范围（MINVALUE, 3）将自动作为第一个实际分区，其名称为“cost_0”。
其余分区依次为“cost_1”、...、“cost_6”.

cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='metro_ride_record') order by relname asc;
 relname | partstrategy 
---------+--------------
 cost_0  | r
 cost_1  | r
 cost_2  | r
 cost_3  | r
 cost_4  | r
 cost_5  | r
 cost_6  | r
(7 rows)

cndba=# SELECT * FROM metro_ride_record PARTITION (cost_0);
 record_number |         name         |    enter_station     |    leave_station     | ride_stations_number 
---------------+----------------------+----------------------+----------------------+----------------------
        120101 | Brain                | Tung Chung           | Tsing Yi             |                    2
(1 row)

1.2 列表分区

1.2.1 创建语法

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY LIST (partition_key) 
         (
         PARTITION partition_name VALUES (list_values_clause)
         [, ... ]
         );

说明：

列表分区策略的分区键仅支持1列。
配置VALUES (DEFAULT)，加入的数据如有“list_values_clause”中未列出的键值，存放在VALUES (DEFAULT)对应的分区。

1.2.2 示例1

创建列表分区表graderecord。含有4个分区，分区键为CHAR类型。分区的范围分别为：21.01，21.02，21.03，21.04。

cndba=# drop table graderecord;
DROP TABLE
cndba=# CREATE TABLE graderecord  
  (  
  number INTEGER,  
  name CHAR(20),  
  class CHAR(20),  
  grade INTEGER
  )  
  PARTITION BY LIST(class)  
  (  
  PARTITION class_01 VALUES ('21.01'),  
  PARTITION class_02 VALUES ('21.02'),
  PARTITION class_03 VALUES ('21.03'),
  PARTITION class_04 VALUES ('21.04'),
PARTITION class_df VALUES (DEFAULT)
  );

cndba=#  insert into graderecord values('210101','Alan','21.01',92);  
INSERT 0 1
cndba=#  insert into graderecord values('210102','Ben','21.01',62);  
INSERT 0 1
cndba=#  insert into graderecord values('210103','Brain','21.01',26);  
INSERT 0 1
cndba=#  insert into graderecord values('210204','Carl','21.02',77);  
INSERT 0 1
cndba=#  insert into graderecord values('210205','David','21.02',47);  
INSERT 0 1
cndba=#  insert into graderecord values('210206','Eric','21.02',97);  
INSERT 0 1
cndba=#  insert into graderecord values('210307','Frank','21.03',90);  
INSERT 0 1
cndba=#  insert into graderecord values('210308','Gavin','21.03',100);  
INSERT 0 1
cndba=#  insert into graderecord values('210309','Henry','21.03',67);  
INSERT 0 1
cndba=#  insert into graderecord values('210410','Jack','21.04',75);  
INSERT 0 1
cndba=#  insert into graderecord values('210311','Jerry','21.04',60);
INSERT 0 1
cndba=#

查看分区:

cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='graderecord') order by relname asc;
 relname  | partstrategy 
----------+--------------
 class_01 | l
 class_02 | l
 class_03 | l
 class_04 | l
 class_df | l
(5 rows)


cndba=# SELECT * FROM graderecord PARTITION (class_01);
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210101 | Alan                 | 21.01                |    92
 210102 | Ben                  | 21.01                |    62
 210103 | Brain                | 21.01                |    26
(3 rows)


cndba=# insert into graderecord values('210311','Jerry','100',60);
INSERT 0 1
cndba=# SELECT * FROM graderecord PARTITION (class_df);
 number |         name         |        class         | grade 
--------+----------------------+----------------------+-------
 210311 | Jerry                | 100                  |    60
(1 row)

1.3 间隔分区

1.3.1 语法

间隔分区是在范围分区的基础上，增加了间隔值“PARTITION BY RANGE (partition_key)”的定义。

VALUES LESS THAN间隔分区语法格式：

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY RANGE (partition_key) 
         (
         INTERVAL ('interval_expr') 
         PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE})
         [, ... ]
         ); 

说明： INTERVAL ('interval_expr') 自动创建分区的间隔，支持：
INTERVAL ('2 hour') 
INTERVAL ('2 day') 
INTERVAL ('1 month') 
INTERVAL ('2 year') 
INTERVAL ('1 week')

START END间隔分区表语法格式：

方式一：START(partition_value) END (partition_value | MAXVALUE)方式
CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY RANGE (partition_key) 
         (
         INTERVAL ('interval_expr') 
         PARTITION partition_name START(partition_value) END (partition_value | MAXVALUE)
         [, ... ]
         ); 


方式二：START(partition_value) END (partition_value) EVERY (interval_value)方式
CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
] )
     PARTITION BY RANGE (partition_key)  
         (
         PARTITION partition_name START(partition_value) END (partition_value) EVERY (interval_value)
         [, ... ]  
         ); 


方式三：START(partition_value)方式
CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
] )
     PARTITION BY RANGE (partition_key)  
         (
         INTERVAL ('interval_expr') 
         PARTITION partition_name START(partition_value) 
         [, ... ]  
         ); 



方式四：END(partition_value | MAXVALUE)方式
CREATE TABLE partition_table_name
( [column_name data_type ]
    [, ... ]
] )
     PARTITION BY RANGE (partition_key)  
         INTERVAL ('interval_expr') 
         (
         PARTITION partition_name END(partition_value | MAXVALUE) 
         [, ... ]  
         );

1.3.2 示例

cndba=# drop table sales_table;
DROP TABLE
cndba=# CREATE TABLE sales_table
(
    order_no              INTEGER          NOT NULL,
    goods_name            CHAR(20)         NOT NULL,
    sales_date            DATE             NOT NULL,
    sales_volume          INTEGER,
    sales_store           CHAR(20)
)
PARTITION BY RANGE(sales_date)
        INTERVAL ('1 month') 
        (
        PARTITION start VALUES LESS THAN('2021-01-01 00:00:00'),
        PARTITION later VALUES LESS THAN('2021-01-10 00:00:00')
        );


cndba=# INSERT INTO sales_table VALUES(1, 'jacket', '2021-01-8 00:00:00', 3,'Alaska');
INSERT 0 1
cndba=# INSERT INTO sales_table VALUES(2, 'hat', '2021-04-06 00:00:00', 255,'Clolorado');
INSERT 0 1
cndba=# INSERT INTO sales_table VALUES(3, 'shirt', '2021-11-17 00:00:00', 7000,'Florida');
INSERT 0 1
cndba=# INSERT INTO sales_table VALUES(4, 'coat', '2020-10-21 00:00:00', 9000,'Hawaii');
INSERT 0 1

查看分区:

cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='sales_table') order by relname asc;
 relname | partstrategy 
---------+--------------
 later   | r
 start   | r
 sys_p1  | i
 sys_p2  | i
(4 rows)

这里新增了几个分区：

cndba=# SELECT * FROM sales_table PARTITION (sys_p1);
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store      
----------+----------------------+---------------------+--------------+----------------------
        2 | hat                  | 2021-04-06 00:00:00 |          255 | Clolorado

1.4 哈希分区

1.4.1 语法

CREATE TABLE partition_table_name
( [column_name data_type ]
  [, ... ]
)
     PARTITION BY HASH (partition_key) 
        (PARTITION partition_name )
        [, ... ]
);
注：partition_key为分区键的名称。哈希分区策略的分区键仅支持1列。

1.4.2 示例

cndba=# create table hash_partition_table (
col1 int, 
col2 int)
partition by hash(col1)
(
partition p1,
partition p2
);

cndba=# INSERT INTO hash_partition_table VALUES(1, 1);
INSERT 0 1
cndba=# INSERT INTO hash_partition_table VALUES(2, 2);
INSERT 0 1
cndba=# INSERT INTO hash_partition_table VALUES(3, 3);
INSERT 0 1
cndba=# INSERT INTO hash_partition_table VALUES(4, 4);
INSERT 0 1
cndba=#

查看分区:

 cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='hash_partition_table') order by relname asc;
 relname | partstrategy 
---------+--------------
 p1      | h
 p2      | h
(2 rows)

cndba=# select * from hash_partition_table partition (p1);
 col1 | col2 
------+------
    3 |    3
    4 |    4
(2 rows)

1.5 分区表维护操作

1.5.1 语法说明

删除分区：
ALTER TABLE partition_table_name DROP PARTITION partition_name;  

增加分区：
ALTER TABLE partition_table_name ADD {partition_less_than_item | partition_start_end_item| partition_list_item }; 

重命名分区：
ALTER TABLE partition_table_name RENAME PARTITION partition_name TO partition_new_name; 

分裂分区（指定切割点split_partition_value的语法）：
ALTER TABLE partition_table_name SPLIT PARTITION partition_name AT ( split_partition_value ) INTO ( PARTITION partition_new_name1, PARTITION partition_new_name2); 

分裂分区（指定分区范围的语法）：
ALTER TABLE partition_table_name SPLIT PARTITION partition_name INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) }; 

合并分区：
ALTER TABLE partition_table_name MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name;

1.5.2 示例

cndba=# CREATE TABLE employees_table
(
    employee_id              INTEGER          NOT NULL,
    employee_name            CHAR(20)         NOT NULL,
    onboarding_date          DATE             NOT NULL,
    position                 CHAR(20)         
)
PARTITION BY RANGE(onboarding_date)
(
        PARTITION founders VALUES LESS THAN('2000-01-01 00:00:00'),
        PARTITION senate VALUES LESS THAN('2010-01-01 00:00:00'),
        PARTITION seniors VALUES LESS THAN('2020-01-01 00:00:00'),
        PARTITION newcomer VALUES LESS THAN(MAXVALUE)
);


cndba=# INSERT INTO  employees_table VALUES
(1, 'SMITH', '1997-01-10 00:00:00','Manager'),
(2, 'JONES', '2001-05-06 00:00:00', 'Supervisor'),
(3, 'WILLIAMS', '2011-09-17 00:00:00', 'Engineer'),
(4, 'TAYLOR', '2021-10-21 00:00:00', 'Clerk');

删除分区：

cndba=# SELECT * FROM employees_table PARTITION (newcomer);
 employee_id |    employee_name     |   onboarding_date   |       position       
-------------+----------------------+---------------------+----------------------
           4 | TAYLOR               | 2021-10-21 00:00:00 | Clerk               
(1 row)

cndba=# ALTER TABLE employees_table DROP PARTITION newcomer;  
ALTER TABLE
cndba=# SELECT * FROM employees_table PARTITION (newcomer);
ERROR:  partition "newcomer" of relation "employees_table" does not exist
cndba=#

增加分区：

cndba=# ALTER TABLE employees_table ADD PARTITION fresh VALUES LESS THAN ('2040-01-01 00:00:00'); 
ALTER TABLE
cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='employees_table') order by relname asc;
 relname  | partstrategy 
----------+--------------
 founders | r
 fresh    | r
 senate   | r
 seniors  | r
(4 rows)

分割分区：

cndba=# ALTER TABLE employees_table SPLIT PARTITION fresh AT ('2030-01-01 00:00:00') INTO (PARTITION current, PARTITION future); 
ALTER TABLEALTER TABLE
cndba=# 
cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='employees_table') order by relname asc;
 relname  | partstrategy 
----------+--------------
 current  | r
 founders | r
 future   | r
 senate   | r
 seniors  | r
(5 rows)

重命名分区：

cndba=# ALTER TABLE employees_table RENAME PARTITION current TO now; 
ALTER TABLE
cndba=# select relname,partstrategy from pg_partition where parttype='p' and parentid in (select parentid from pg_partition where relname='employees_table') order by relname asc;
 relname  | partstrategy 
----------+--------------
 founders | r
 future   | r
 now      | r
 senate   | r
 seniors  | r
(5 rows)

2 分区索引

2.1 语法说明

openGauss分区表支持两种索引：全局（global）索引和本地（local）索引。

添加/删除分区不影响本地索引使用。
添加分区不影响全局索引使用，默认删除分区方式，全局索引失效，需要对全局索引重建；update global index方式删除分区，不影响全局索引使用。

分区表创建索引不支持concurrently语法，默认索引是全局索引，创建本地索引需要指定local。

创建主键约束和唯一约束必须要包含分区字段，创建本地唯一索引也必须要包含分区字段，但是创建全局唯一索引没有这个限制。

分区索引的相关约束限制如下：

分区表上不支持创建部分索引。
分区表创建GLOBAL索引时，存在以下约束条件：

不支持表达式索引、部分索引
不支持列存表
仅支持B-tree索引
在相同属性列上，分区LOCAL索引与GLOBAL索引不能共存。
GLOBAL索引，最大支持31列。
如果alter语句不带有UPDATE GLOBAL INDEX，那么原有的GLOBAL索引将失效，查询时将使用其他索引进行查询；如果alter语句带有UPDATE GLOBAL INDEX，原有的GLOBAL索引仍然有效，并且索引功能正确。

对于分区表的local unique索引，索引键必须包含所有的分区键。

语法：

CREATE [ UNIQUE ] INDEX [ IF NOT EXISTS ] [ [schema_name.]index_name ] ON table_name [ USING method ]
    ( {{ column_name [ ( length ) ] | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS LAST ] }[, ...] )
    [ LOCAL [ ( { PARTITION index_partition_name [ ( SUBPARTITION index_subpartition_name [, ...] ) ] [ TABLESPACE index_partition_tablespace ] } [, ...] ) ] | GLOBAL ]
    [ INCLUDE ( column_name [, ...] )]
    [ WITH ( { storage_parameter = value } [, ...] ) ]
    [ TABLESPACE tablespace_name ]
    [ WHERE predicate ];

说明：
USING method，指定创建索引的方法。取值范围：

btree：B-tree索引使用一种类似于B+树的结构来存储数据的键值，通过这种结构能够快速的查找索引。btree适合支持比较查询以及查询范围。
hash：Hash索引使用Hash函数对索引的关键字进行散列。只能处理简单等值比较，比较适合在索引值较长的情况下使用。
gin：GIN索引是倒排索引，可以处理包含多个键的值（比如数组）。
gist：Gist索引适用于几何和地理等多维数据类型和集合数据类型。目前支持的数据类型有box、point、poly、circle、tsvector、tsquery、range。
Psort：Psort索引。针对列存表进行局部排序索引。
ubtree：仅供ustore表使用的多版本B-tree索引，索引页面上包含事务信息，能并自主回收页面。

行存表（ASTORE存储引擎）支持的索引类型：btree（行存表缺省值）、hash、gin、gist。行存表（USTORE存储引擎）支持的索引类型：ubtree。
列存表支持的索引类型：Psort（列存表缺省值）、btree、gin。
全局临时表不支持GIN索引和Gist索引。

2.2 示例

cndba=# CREATE TABLE customer_address_p1
(
    CA_ADDRESS_SK             INTEGER               NOT NULL,
    CA_ADDRESS_ID             CHAR(16)              NOT NULL,
    CA_STREET_NUMBER          CHAR(10)                      ,
    CA_STREET_NAME            VARCHAR(60)                   ,
    CA_STREET_TYPE            CHAR(15)                      ,
    CA_SUITE_NUMBER           CHAR(10)                      ,
    CA_CITY                   VARCHAR(60)                   ,
    CA_COUNTY                 VARCHAR(30)                   ,
    CA_STATE                  CHAR(2)                       ,
    CA_ZIP                    CHAR(10)                      ,
    CA_COUNTRY                VARCHAR(20)                   ,
    CA_GMT_OFFSET             DECIMAL(5,2)                  ,
    CA_LOCATION_TYPE          CHAR(20)
)
PARTITION BY RANGE(CA_ADDRESS_SK)
( 
   PARTITION p1 VALUES LESS THAN (3000),
   PARTITION p2 VALUES LESS THAN (5000),
   PARTITION p3 VALUES LESS THAN (MAXVALUE)
)
ENABLE ROW MOVEMENT;

创建分区表索引ds_customer_address_p1_index1，不指定索引分区的名称。
cndba=# CREATE INDEX ds_customer_address_p1_index1 ON customer_address_p1(CA_ADDRESS_SK) LOCAL; 
CREATE INDEX

创建分区表索引ds_customer_address_p1_index2，并指定索引分区的名称。
cndba=# CREATE INDEX ds_customer_address_p1_index2 ON customer_address_p1(CA_ADDRESS_SK) LOCAL
(
    PARTITION CA_ADDRESS_SK_index1,
    PARTITION CA_ADDRESS_SK_index2,
    PARTITION CA_ADDRESS_SK_index3 
) ;


创建GLOBAL分区索引
cndba=# CREATE INDEX ds_customer_address_p1_index3 ON customer_address_p1(CA_ADDRESS_ID) GLOBAL;
CREATE INDEX

不指定关键字，默认创建GLOBAL分区索引
cndba=# CREATE INDEX ds_customer_address_p1_index4 ON customer_address_p1(CA_ADDRESS_ID);
CREATE INDEX

查看索引：
cndba=# select tablename,indexname from pg_indexes where tablename='customer_address_p1';
      tablename      |           indexname           
---------------------+-------------------------------
 customer_address_p1 | ds_customer_address_p1_index1
 customer_address_p1 | ds_customer_address_p1_index2
 customer_address_p1 | ds_customer_address_p1_index3
 customer_address_p1 | ds_customer_address_p1_index4
(4 rows)

cndba=# 


重命名索引：
cndba=# ALTER INDEX ds_customer_address_p1_index2 RENAME PARTITION CA_ADDRESS_SK_index1 TO CA_ADDRESS_SK_index4;

2.3 分区状态和类型查看说明

目前openGauss的版本还无法直接通过视图来查看分区索引的状态和类型。对于类型，可以通过pg_indexes 视图来直接查看indexdef 定义。而状态则只能通过执行计划来判断。

cndba=# /x
Expanded display is on.
cndba=# select tablename,indexname,indexdef from pg_indexes where tablename='customer_address_p1';
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tablename | customer_address_p1
indexname | ds_customer_address_p1_index1
indexdef  | CREATE INDEX ds_customer_address_p1_index1 ON customer_address_p1 USING btree (ca_address_sk) LOCAL(PARTITION p1_ca_address_sk_idx, PARTITION p2_ca_address_sk_idx, PARTITION p3_ca_address_sk_idx)  TABLESPACE pg_default
-[ RECORD 2 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tablename | customer_address_p1
indexname | ds_customer_address_p1_index2
indexdef  | CREATE INDEX ds_customer_address_p1_index2 ON customer_address_p1 USING btree (ca_address_sk) LOCAL(PARTITION ca_address_sk_index4, PARTITION ca_address_sk_index2, PARTITION ca_address_sk_index3)  TABLESPACE pg_default
-[ RECORD 3 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tablename | customer_address_p1
indexname | ds_customer_address_p1_index3
indexdef  | CREATE INDEX ds_customer_address_p1_index3 ON customer_address_p1 USING btree (ca_address_id) TABLESPACE pg_default
-[ RECORD 4 ]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
tablename | customer_address_p1
indexname | ds_customer_address_p1_index4
indexdef  | CREATE INDEX ds_customer_address_p1_index4 ON customer_address_p1 USING btree (ca_address_id) TABLESPACE pg_default

cndba=#

LOCAL 类型的索引，indexdef 中会有明确的显示，没有则是全局索引。

通过执行计划查看索引状态，如果索引时正常的，那么会在执行计划中使用：

cndba=# explain analyze select * from customer_address_p1 where CA_ADDRESS_ID < 80;

另外，前面提到，对于全局索引，在没有使用update global index的方式删除时，会导致全局索引失效。

cndba=# ALTER TABLE customer_address_p1 DROP PARTITION p1 update global index;  
ALTER TABLE

签到成功

CNDBA社区