openGauss学习笔记-39 openGauss 高级数据管理-分区表

openGauss学习笔记-39 openGauss 高级数据管理-分区表39.1 范围分区表的分类39.2 创建范围分区39.2.1 创建VALUES LESS THAN范围分区表语法格式39.2.2 创建VALUES LESS THAN范围分区表参数说明39.2.3 创建VALUES LESS THAN范围分区表示例39.3 询分区表39.3.1 查询分区表语法格式39.3.2 查询分区表参数说明39.3.3 查询分区表语法示例39.4 创建START END范围分区表39.4.1 创建START END范围分区表语法格式39.4.2 创建START END范围分区表参数说明39.4.3 创建START END范围分区表示例39.5 创建列表分区表语法格式39.5.1 创建列表分区表语法格式39.5.2 创建列表分区表参数说明39.5.3 创建列表分区表示例39.6 创建间隔分区表语法格式39.6.1 创建间隔分区表语法格式39.6.2 间隔分区表参数说明39.6.3 间隔分区表语法示例39.7 哈希分区表语法格式39.7.1 哈希分区表语法格式39.7.2 哈希分区表参数说明39.7.3 哈希分区表示例39.8 导入数据39.8.1 导入数据语法格式39.8.2 导入数据参数说明39.8.3 导入数据示例39.9 修改分区39.9.1 修改分区表语法格式39.9.2 修改分区表参数说明39.9.3 修改分区表示例39.10 删除分区39.10.1 删除分区表语法格式39.10.2 删除分区表参数说明39.10.3 删除分区表示例

openGauss学习笔记-39 openGauss 高级数据管理-分区表

一张表内的数据过多时，就会严重影响到数据的查询和操作效率。openGauss支持把一张表从逻辑上分成多个小的分片，从而避免一次处理大量数据，提高处理效率。

openGauss数据库支持这些划分类型：

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

分区表的操作除了创建之外还有：

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

39.1 范围分区表的分类

范围分区表按照划分范围的方式，分为以下类别：

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

39.2 创建范围分区

39.2.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})
 [, ... ]
 );

39.2.2 创建VALUES LESS THAN范围分区表参数说明

partition_table_name

分区表的名称。
column_name

新表中要创建的字段名。
data_type

字段的数据类型。
partition_key

partition_key为分区键的名称。

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

partition_name为范围分区的名称。
VALUES LESS THAN

分区中的数值必须小于上边界值。
partition_value

partition_value为范围分区的上边界，取值依赖于partition_key的类型。
MAXVALUE

MAXVALUE表示分区的上边界，它通常用于设置最后一个范围分区的上边界。

39.2.3 创建VALUES LESS THAN范围分区表示例

示例1：创建范围分区表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。

--创建分区表sales_table。
openGauss=# 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)
);
-- 数据插入分区season1
openGauss=# INSERT INTO sales_table VALUES(1, 'jacket', '2021-01-10 00:00:00', 3,'Alaska');

-- 数据插入分区season2
openGauss=# INSERT INTO sales_table VALUES(2, 'hat', '2021-05-06 00:00:00', 5,'Clolorado');

-- 数据插入分区season3
openGauss=# INSERT INTO sales_table VALUES(3, 'shirt', '2021-09-17 00:00:00', 7,'Florida');

-- 数据插入分区season4
openGauss=# INSERT INTO sales_table VALUES(4, 'coat', '2021-10-21 00:00:00', 9,'Hawaii');

39.3 询分区表

39.3.1 查询分区表语法格式

SELECT * FROM partition_table_name PARTITION { ( partition_name ) | FOR (  partition_value [, ...] ) }

39.3.2 查询分区表参数说明

partition_table_name

分区表的名称。
partition_name

partition_name为分区的名称。
partition_value

用于指定分区的值。PARTITION FOR子句指定的值所在的分区，就是进行查询的分区。

39.3.3 查询分区表语法示例

示例2：查询示例1中建立的分区表sales_table。

--查询sales_table的数据。
openGauss=# SELECT * FROM sales_table;
 order_no |      goods_name      |     sales_date      | sales_volume |     sale
s_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)

--查询sales_table的4季度数据。这里采用“sales_table PARTITION (season4);”来引用第4季度数据所在分区。
openGauss=# 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季度所在分区。
openGauss=# 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)

39.4 创建START END范围分区表

39.4.1 创建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(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)
     [, ... ]
     );

39.4.2 创建START END范围分区表参数说明

partition_table_name

分区表的名称。
column_name

新表中要创建的字段名。
data_type

字段的数据类型。
partition_key

partition_key为分区键的名称。

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

partition_name为范围分区的名称或者范围分区的名称前缀。
- 若该定义是“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是名称前缀。
- 若该定义是第一个分区定义，且该定义有START值，则范围（MINVALUE, START）将自动作为第一个实际分区，其名称为p1_0，然后该定义语义描述的分区名称依次为p1_1, p1_2, …。
  
  例如对于完整定义“PARTITION p1 START(1), PARTITION p2 START(2)”，生成的分区是：(MINVALUE, 1), [1, 2) 和 [2, MAXVALUE)，其名称依次为p1_0, p1_1和p2，即此处p1是名称前缀，p2是分区名称。这里MINVALUE表示最小值。
- 其余的情况都是范围分区名称。
VALUES LESS THAN

分区中的数值必须小于上边界值。
partition_value

partition_value为范围分区的端点值（起始或终点），取值依赖于partition_key的类型。
interval_value：

对[START，END) 表示的范围进行切分，interval_value是指定切分后每个分区的宽度。如果（END-START）值不能整除以EVERY值，则仅最后一个分区的宽度小于EVERY值。
MAXVALUE

MAXVALUE表示分区的上边界，它通常用于设置最后一个范围分区的上边界。

39.4.3 创建START END范围分区表示例

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

--创建分区表graderecord。
openGauss=# 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)
 );

-- 数据插入分区。
openGauss=# insert into graderecord values('210101','Alan','21.01',92);  
openGauss=# insert into graderecord values('210102','Ben','21.01',62);  
openGauss=# insert into graderecord values('210103','Brain','21.01',26);  
openGauss=# insert into graderecord values('210204','Carl','21.02',77);  
openGauss=# insert into graderecord values('210205','David','21.02',47);  
openGauss=# insert into graderecord values('210206','Eric','21.02',97);  
openGauss=# insert into graderecord values('210307','Frank','21.03',90);  
openGauss=# insert into graderecord values('210308','Gavin','21.03',100);  
openGauss=# insert into graderecord values('210309','Henry','21.03',67);  
openGauss=# insert into graderecord values('210410','Jack','21.04',75);  
openGauss=# insert into graderecord values('210311','Jerry','21.04',60);  

--查询graderecord的数据。
openGauss=# 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
 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
(11 rows)

--查询graderecord的pass分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (pass);
ERROR:  partition "pass" of relation "graderecord" does not exist
查询失败。
原因是“PARTITION pass START(60) END(90),”是第一个分区定义，且该定义有START值，则范围（MINVALUE, 60）将自动作为第一个实际分区，其名称为“pass_0”。
而该定义语义描述的“60<= grade<90”分区的名称为“pass_1”。 

--查询graderecord的pass_0分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (pass_0);
 number |         name         |        class         | grade
--------+----------------------+----------------------+-------
 210103 | Brain                | 21.01                |    26
 210205 | David                | 21.02                |    47
(2 rows)

--查询graderecord的pass_1分区数据。
openGauss=# 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
 210311 | Jerry                | 21.04                |    60
(5 rows)

--查询graderecord的execllent分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (excellent);
 number |         name         |        class         | grade
--------+----------------------+----------------------+-------
 210101 | Alan                 | 21.01                |    92
 210206 | Eric                 | 21.02                |    97
 210307 | Frank                | 21.03                |    90
 210308 | Gavin                | 21.03                |   100
(4 rows)

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

--创建分区表metro_ride_record。记录乘车人、上下车站点、乘坐站点数量。并按照乘坐站点数量，每3站建立一个分区。
openGauss=# 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)
   );

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

--查询metro_ride_record的数据。
openGauss=# SELECT * FROM metro_ride_record;
 record_number |         name         |    enter_station     |    leave_station     | ride_stations_number
---------------+----------------------+----------------------+----------------------+----------------------
        120101 | Brain                | Tung Chung           | Tsing Yi             |                    2
        120102 | David                | Po Lam               | Yau Tong             |                    4
        120103 | Ben                  | Yau Ma Tei           | Wong Tai Sin         |                    6
        120104 | Carl                 | Tai Wo Hau           | Prince Edward        |                    8
        120105 | Henry                | Admiralty            | Lai King             |                   10
        120106 | Jack                 | Chai Wan             | Central              |                   12
        120107 | Jerry                | Central              | Tai Wo Hau           |                   14
        120108 | Alan                 | Diamond Hill         | Kwai Hing            |                   16
        120109 | Eric                 | Jordan               | Shek Kip Mei         |                   18
        120110 | Frank                | Lok Fu               | Sunny Bay            |                   20
(10 rows)

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

--查询metro_ride_record的cost_0分区数据。
openGauss=# 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)

--查询metro_ride_record的cost_1分区数据。
openGauss=# SELECT * FROM metro_ride_record PARTITION (cost_1);
 record_number |         name         |    enter_station     |    leave_station     | ride_stations_number
---------------+----------------------+----------------------+----------------------+----------------------
        120102 | David                | Po Lam               | Yau Tong             |                    4
(1 row)

--查询metro_ride_record的cost_6分区数据。
openGauss=# SELECT * FROM metro_ride_record PARTITION (cost_6);
 record_number |         name         |    enter_station     |    leave_station     | ride_stations_number
---------------+----------------------+----------------------+----------------------+----------------------
        120109 | Eric                 | Jordan               | Shek Kip Mei         |                   18
        120110 | Frank                | Lok Fu               | Sunny Bay            |                   20
(2 rows)

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

--创建分区表graderecord。
openGauss=# 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)
 );

-- 数据插入分区。
openGauss=# insert into graderecord values('210101','Alan','21.01',92);  
openGauss=# insert into graderecord values('210102','Ben','21.01',62);  
openGauss=# insert into graderecord values('210103','Brain','21.01',26);  
openGauss=# insert into graderecord values('210204','Carl','21.02',77);  
openGauss=# insert into graderecord values('210205','David','21.02',47);  
openGauss=# insert into graderecord values('210206','Eric','21.02',97);  
openGauss=# insert into graderecord values('210307','Frank','21.03',90);  
openGauss=# insert into graderecord values('210308','Gavin','21.03',100);  
openGauss=# insert into graderecord values('210309','Henry','21.03',67);  
openGauss=# insert into graderecord values('210410','Jack','21.04',75);  
openGauss=# insert into graderecord values('210311','Jerry','21.04',60);


--查询graderecord的数据。
openGauss=# 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
 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
(11 rows)

--查询graderecord的pass分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (pass);
ERROR:  partition "pass" of relation "graderecord" does not exist
查询失败。
原因是“PARTITION pass START(60),”是第一个分区定义，且该定义有START值，则范围（MINVALUE, 60）将自动作为第一个实际分区，其名称为“pass_0”。
而该定义语义描述的“60<= grade<90”分区的名称为“pass_1”。 

--查询graderecord的pass_0分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (pass_0);
 number |         name         |        class         | grade
--------+----------------------+----------------------+-------
 210103 | Brain                | 21.01                |    26
 210205 | David                | 21.02                |    47
(2 rows)

--查询graderecord的pass_1分区数据。
openGauss=# 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
 210311 | Jerry                | 21.04                |    60
(5 rows)

--查询graderecord的execllent分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (excellent);
 number |         name         |        class         | grade
--------+----------------------+----------------------+-------
 210101 | Alan                 | 21.01                |    92
 210206 | Eric                 | 21.02                |    97
 210307 | Frank                | 21.03                |    90
 210308 | Gavin                | 21.03                |   100
(4 rows)

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

--创建分区表graderecord。
openGauss=# 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)
 );

-- 数据插入分区。
openGauss=# insert into graderecord values('210101','Alan','21.01',92);  
openGauss=# insert into graderecord values('210102','Ben','21.01',62);  
openGauss=# insert into graderecord values('210103','Brain','21.01',26);  
openGauss=# insert into graderecord values('210204','Carl','21.02',77);  
openGauss=# insert into graderecord values('210205','David','21.02',47);  
openGauss=# insert into graderecord values('210206','Eric','21.02',97);  
openGauss=# insert into graderecord values('210307','Frank','21.03',90);  
openGauss=# insert into graderecord values('210308','Gavin','21.03',100);  
openGauss=# insert into graderecord values('210309','Henry','21.03',67);  
openGauss=# insert into graderecord values('210410','Jack','21.04',75);  
openGauss=# insert into graderecord values('210311','Jerry','21.04',60);


--查询graderecord的数据。
openGauss=# 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
 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
(11 rows)

--查询graderecord的no_pass分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (no_pass);
 number |         name         |        class         | grade
--------+----------------------+----------------------+-------
 210103 | Brain                | 21.01                |    26
 210205 | David                | 21.02                |    47
(2 rows)

--查询graderecord的pass分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (pass);
 number |         name         |        class         | grade
--------+----------------------+----------------------+-------
 210102 | Ben                  | 21.01                |    62
 210204 | Carl                 | 21.02                |    77
 210309 | Henry                | 21.03                |    67
 210410 | Jack                 | 21.04                |    75
 210311 | Jerry                | 21.04                |    60
(5 rows)

--查询graderecord的execllent分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (excellent);
 number |         name         |        class         | grade
--------+----------------------+----------------------+-------
 210101 | Alan                 | 21.01                |    92
 210206 | Eric                 | 21.02                |    97
 210307 | Frank                | 21.03                |    90
 210308 | Gavin                | 21.03                |   100
(4 rows)

39.5 创建列表分区表语法格式

39.5.1 创建列表分区表语法格式

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

39.5.2 创建列表分区表参数说明

partition_table_name

分区表的名称。
column_name

新表中要创建的字段名。
data_type

字段的数据类型。
partition_key

partition_key为分区键的名称。

列表分区策略的分区键仅支持1列。
partition_name

partition_name为范围分区的名称。
list_values_clause

对应分区存在的一个或者多个键值。多个键值之间以逗号分隔。
VALUES (DEFAULT)

加入的数据如有“list_values_clause”中未列出的键值，存放在VALUES (DEFAULT)对应的分区。
MAXVALUE

MAXVALUE表示分区的上边界，它通常用于设置最后一个范围分区的上边界。

39.5.3 创建列表分区表示例

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

--创建分区表graderecord。
openGauss=# 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')
 );

-- 数据插入分区。
openGauss=# insert into graderecord values('210101','Alan','21.01',92);  
openGauss=# insert into graderecord values('210102','Ben','21.01',62);  
openGauss=# insert into graderecord values('210103','Brain','21.01',26);  
openGauss=# insert into graderecord values('210204','Carl','21.02',77);  
openGauss=# insert into graderecord values('210205','David','21.02',47);  
openGauss=# insert into graderecord values('210206','Eric','21.02',97);  
openGauss=# insert into graderecord values('210307','Frank','21.03',90);  
openGauss=# insert into graderecord values('210308','Gavin','21.03',100);  
openGauss=# insert into graderecord values('210309','Henry','21.03',67);  
openGauss=# insert into graderecord values('210410','Jack','21.04',75);  
openGauss=# insert into graderecord values('210311','Jerry','21.04',60);


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

--查询graderecord的class_01分区数据。
openGauss=# 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)

--查询graderecord的class_04分区数据。
openGauss=# SELECT * FROM graderecord PARTITION (class_04);
 number |         name         |        class         | grade
--------+----------------------+----------------------+-------
 210410 | Jack                 | 21.04                |    75
 210311 | Jerry                | 21.04                |    60
(2 rows)

39.6 创建间隔分区表语法格式

39.6.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})
         [, ... ]
         );

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) 
 [, ... ]
 );

39.6.2 间隔分区表参数说明

INTERVAL ('interval_expr')

间隔分区定义信息。只支持TIMESTAMP[(p)] [WITHOUT TIME ZONE]、TIMESTAMP[(p)] [WITH TIME ZONE]、DATE数据类型。
interval_expr自动创建分区的间隔，例如：

自动创建分区的间隔，例如：1 day、1 month。
partition_name

partition_name为范围分区的名称。

系统自动建立的分区按照建立的先后顺序，依次命名为：sys_p1、sys_p2、sys_p3…

39.6.3 间隔分区表语法示例

示例8：间隔分区表sales_table。

--创建分区表sales_table。
openGauss=# 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')
 );
-- 数据插入分区later
openGauss=# INSERT INTO sales_table VALUES(1, 'jacket', '2021-01-8 00:00:00', 3,'Alaska');

-- 不在已有分区的数据插入，系统会新建分区sys_p1。
openGauss=# INSERT INTO sales_table VALUES(2, 'hat', '2021-04-06 00:00:00', 255,'Clolorado');

-- 不在已有分区的数据插入，系统会新建分区sys_p2。
openGauss=# INSERT INTO sales_table VALUES(3, 'shirt', '2021-11-17 00:00:00', 7000,'Florida');

-- 数据插入分区start
openGauss=# INSERT INTO sales_table VALUES(4, 'coat', '2020-10-21 00:00:00', 9000,'Hawaii');

--查询sales_table的数据。
openGauss=# SELECT * FROM sales_table;
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store
----------+----------------------+---------------------+--------------+----------------------
 4 | coat                 | 2020-10-21 00:00:00 |         9000 | Hawaii
 1 | jacket               | 2021-01-08 00:00:00 |            3 | Alaska
 2 | hat                  | 2021-04-06 00:00:00 |          255 | Clolorado
 3 | shirt                | 2021-11-17 00:00:00 |         7000 | Florida
(4 rows)

--查询sales_table的start分区数据。这里采用“sales_table PARTITION (start);”来引用分区。
openGauss=# SELECT * FROM sales_table PARTITION (start);
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store
----------+----------------------+---------------------+--------------+----------------------
 4 | coat                 | 2020-10-21 00:00:00 |         9000 | Hawaii
(1 row)

--查询sales_table的later分区数据。这里采用“sales_table PARTITION (later);”来引用分区。
openGauss=# SELECT * FROM sales_table PARTITION (later);
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store
----------+----------------------+---------------------+--------------+----------------------
 1 | jacket               | 2021-01-08 00:00:00 |            3 | Alaska
(1 row)

--查询sales_table的sys_p1分区数据。这里采用“sales_table PARTITION (sys_p1);”来引用分区。
openGauss=# 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 row)

--查询sales_table的sys_p2分区数据。这里采用“sales_table PARTITION (sys_p2);”来引用分区。
openGauss=# SELECT * FROM sales_table PARTITION (sys_p2);
 order_no |      goods_name      |     sales_date      | sales_volume |     sales_store
----------+----------------------+---------------------+--------------+----------------------
 3 | shirt                | 2021-11-17 00:00:00 |         7000 | Florida
(1 row)

39.7 哈希分区表语法格式

39.7.1 哈希分区表语法格式

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

39.7.2 哈希分区表参数说明

partition_table_name

分区表的名称。
column_name

新表中要创建的字段名。
data_type

字段的数据类型。
partition_key

partition_key为分区键的名称。哈希分区策略的分区键仅支持1列。
partition_name

partition_name为哈希分区的名称。希望创建几个哈希分区就给出几个分区名。

39.7.3 哈希分区表示例

示例9：哈希分区表hash_partition_table。

--创建哈希分区表hash_partition_table
openGauss=# create table hash_partition_table (
col1 int, 
col2 int)
partition by hash(col1)
(
partition p1,
partition p2
);

-- 数据插入
openGauss=# INSERT INTO hash_partition_table VALUES(1, 1);
INSERT 0 1
openGauss=# INSERT INTO hash_partition_table VALUES(2, 2);
INSERT 0 1
openGauss=# INSERT INTO hash_partition_table VALUES(3, 3);
INSERT 0 1
openGauss=# INSERT INTO hash_partition_table VALUES(4, 4);
INSERT 0 1

-- 查看数据
openGauss=# select * from hash_partition_table partition (p1);
 col1 | col2
------+------
 3 |    3
 4 |    4
(2 rows)

openGauss=# select * from hash_partition_table partition (p2);
 col1 | col2
------+------
 1 |    1
 2 |    2
(2 rows)

39.8 导入数据

39.8.1 导入数据语法格式

导入单行数据：

INSERT INTO partition_table_name [ ( column_name [, ...] ) ]  VALUES [ ( value )[, ...]  ];

导入结构相同的现有表格数据：

INSERT INTO partition_table_name SELECT * FROM  source_table_name

39.8.2 导入数据参数说明

partition_table_name

分区表的名称。
column_name

分区表中的字段名。可省略。
value

字段对应的值：
- 提供了column_name值时：value子句提供的值从左到右关联到对应列。
- 没提供column_name值时：value子句提供的值从左到右关联到partition_table_name对应列。

39.8.3 导入数据示例

示例10：

--创建分区表employees_table。
openGauss=# 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)
);

-- 数据插入分区founders
openGauss=# INSERT INTO  employees_table VALUES(1, 'SMITH', '1997-01-10 00:00:00','Manager');

-- 查看founders分区数据
openGauss=# select * from employees_table partition (founders);

-- 创建表格employees_data_table
openGauss=# CREATE TABLE employees_data_table
(
 employee_id              INTEGER          NOT NULL,
 employee_name            CHAR(20)         NOT NULL,
 onboarding_date          DATE             NOT NULL,
 position                 CHAR(20) 
);
-- 插入数据
openGauss=# insert into employees_data_table (employee_id, employee_name, onboarding_date, position) VALUES
(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');

-- 查看表格数据
openGauss=# select * from employees_data_table;

--数据导入employees_table
openGauss=# INSERT INTO employees_table SELECT * FROM  employees_data_table;

-- 查看senate分区数据
openGauss=# select * from employees_table partition (senate);
 employee_id |    employee_name     |   onboarding_date   |       position
-------------+----------------------+---------------------+----------------------
 2 | JONES                | 2001-05-06 00:00:00 | Supervisor
(1 row)

-- 查看seniors分区数据
openGauss=# select * from employees_table partition (seniors);
 employee_id |    employee_name     |   onboarding_date   |       position
-------------+----------------------+---------------------+----------------------
 3 | WILLIAMS             | 2011-09-17 00:00:00 | Engineer
(1 row)

-- 查看newcomer分区数据
openGauss=# select * from employees_table partition (newcomer);
 employee_id |    employee_name     |   onboarding_date   |       position
-------------+----------------------+---------------------+----------------------
 4 | TAYLOR               | 2021-10-21 00:00:00 | Clerk
(1 row)

39.9 修改分区

39.9.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;

39.9.2 修改分区表参数说明

partition_table_name

分区表的名称。
partition_name

partition_name为分区的名称。
split_partition_value

切割点。
PARTITION partition_new_name1, PARTITION partition_new_name2

按照切割点分裂出的两个分区。
partition_less_than_item

分区项的描述语句，语法为：

PARTITION partition_name VALUES LESS THAN ( { partition_value | MAXVALUE }  [, ...] )

用法与创建VALUES LESS THAN范围分区表语法格式中相同。

partition_start_end_item

分区项的描述语句，语法为：

PARTITION partition_name {
     {START(partition_value) END (partition_value) EVERY (interval_value)} |
     {START(partition_value) END ({partition_value | MAXVALUE})} |
     {START(partition_value)} |
     {END({partition_value | MAXVALUE})}

用法与创建START END范围分区表语法格式中相同。

partition_list_item

分区项的描述语句，语法为：

PARTITION partition_name VALUES (list_values_clause)

用法与创建列表分区表语法格式中相同。

split_point_clause

分裂分区时，指定的切割点。
partition_value

分区键值。

39.9.3 修改分区表示例

示例11：

--创建分区表employees_table。
openGauss=# 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)
);

-- 插入数据
openGauss=# 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');

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

--删除newcomer分区。
openGauss=# ALTER TABLE employees_table DROP PARTITION newcomer;  
ALTER TABLE

-- 查看newcomer分区数据
openGauss=# select * from employees_table partition (newcomer);
ERROR:  partition "newcomer" of relation "employees_table" does not exist

--增加fresh分区。
openGauss=# ALTER TABLE employees_table ADD PARTITION fresh VALUES LESS THAN ('2040-01-01 00:00:00');  
ALTER TABLE

--以2030-01-01 00:00:00为分割点，分裂fresh分区为current、future两个分区
openGauss=# ALTER TABLE employees_table SPLIT PARTITION fresh AT ('2030-01-01 00:00:00') INTO (PARTITION current, PARTITION future); 
ALTER TABLE

--将分区current改名为now
openGauss=# ALTER TABLE employees_table RENAME PARTITION current TO now; 
ALTER TABLE

--将founders，senate合并为一个分区original。
openGauss=# ALTER TABLE  employees_table MERGE PARTITIONS founders, senate INTO PARTITION original;

39.10 删除分区

39.10.1 删除分区表语法格式

DROP TABLE partition_table_name;

39.10.2 删除分区表参数说明

partition_table_name

分区表的名称。

39.10.3 删除分区表示例

示例12：

--删除分区表employees_table。
openGauss=# DROP TABLE employees_table;
DROP TABLE

👍 点赞，你的认可是我创作的动力！

⭐️ 收藏，你的青睐是我努力的方向！

✏️ 评论，你的意见是我进步的财富！

image.png

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 214,233评论 6赞 495
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 91,357评论 3赞 389
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 159,831评论 0赞 349
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 57,313评论 1赞 288
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 66,417评论 6赞 386
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 50,470评论 1赞 292
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 39,482评论 3赞 412
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,265评论 0赞 269
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 44,708评论 1赞 307
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 36,997评论 2赞 328
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,176评论 1赞 342
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 34,827评论 4赞 337
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,503评论 3赞 322
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,150评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,391评论 1赞 267
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,034评论 2赞 365
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,063评论 2赞 352

openGauss学习笔记-39 openGauss 高级数据管理-分区表