分区表

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

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

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

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

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

范围分区表的分类

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

创建VALUES LESS THAN范围分区表语法格式

  1. CREATE TABLE partition_table_name
  2. ( [column_name data_type ]
  3. [, ... ]
  4. )
  5. PARTITION BY RANGE (partition_key)
  6. (
  7. PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE})
  8. [, ... ]
  9. );

创建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表示分区的上边界,它通常用于设置最后一个范围分区的上边界。

创建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。

  1. --创建分区表sales_table
  2. openGauss=# CREATE TABLE sales_table
  3. (
  4. order_no INTEGER NOT NULL,
  5. goods_name CHAR(20) NOT NULL,
  6. sales_date DATE NOT NULL,
  7. sales_volume INTEGER,
  8. sales_store CHAR(20)
  9. )
  10. PARTITION BY RANGE(sales_date)
  11. (
  12. PARTITION season1 VALUES LESS THAN('2021-04-01 00:00:00'),
  13. PARTITION season2 VALUES LESS THAN('2021-07-01 00:00:00'),
  14. PARTITION season3 VALUES LESS THAN('2021-10-01 00:00:00'),
  15. PARTITION season4 VALUES LESS THAN(MAXVALUE)
  16. );
  17. -- 数据插入分区season1
  18. openGauss=# INSERT INTO sales_table VALUES(1, 'jacket', '2021-01-10 00:00:00', 3,'Alaska');
  19. -- 数据插入分区season2
  20. openGauss=# INSERT INTO sales_table VALUES(2, 'hat', '2021-05-06 00:00:00', 5,'Clolorado');
  21. -- 数据插入分区season3
  22. openGauss=# INSERT INTO sales_table VALUES(3, 'shirt', '2021-09-17 00:00:00', 7,'Florida');
  23. -- 数据插入分区season4
  24. openGauss=# INSERT INTO sales_table VALUES(4, 'coat', '2021-10-21 00:00:00', 9,'Hawaii');

查询分区表语法格式

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

查询分区表参数说明

  • partition_table_name

    分区表的名称。

  • partition_name

    partition_name为分区的名称。

  • partition_value

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

查询分区表语法示例

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

  1. --查询sales_table的数据。
  2. openGauss=# SELECT * FROM sales_table;
  3. order_no | goods_name | sales_date | sales_volume | sale
  4. s_store
  5. ----------+----------------------+---------------------+--------------+---------
  6. -------------
  7. 1 | jacket | 2021-01-10 00:00:00 | 3 | Alaska
  8. 2 | hat | 2021-05-06 00:00:00 | 5 | Clolorado
  9. 3 | shirt | 2021-09-17 00:00:00 | 7 | Florida
  10. 4 | coat | 2021-10-21 00:00:00 | 9 | Hawaii
  11. (4 rows)
  12. --查询sales_table4季度数据。这里采用“sales_table PARTITION (season4);”来引用第4季度数据所在分区。
  13. openGauss=# SELECT * FROM sales_table PARTITION (season4);
  14. order_no | goods_name | sales_date | sales_volume | sales_store
  15. ----------+----------------------+---------------------+--------------+----------------------
  16. 4 | coat | 2021-10-21 00:00:00 | 9 | Hawaii
  17. (1 row)
  18. --查询sales_table1季度数据。这里采用“sales_table PARTITION FOR ('2021-3-21 00:00:00')”来引用第1季度数据所在分区。其中的'2021-3-21 00:00:00'处于第1季度所在分区。
  19. openGauss=# SELECT * FROM sales_table PARTITION FOR ('2021-3-21 00:00:00');
  20. order_no | goods_name | sales_date | sales_volume | sales_store
  21. ----------+----------------------+---------------------+--------------+----------------------
  22. 1 | jacket | 2021-01-10 00:00:00 | 3 | Alaska
  23. (1 row)

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

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

  • 方式一:START(partition_value) END (partition_value | MAXVALUE)方式

    1. CREATE TABLE partition_table_name
    2. ( [column_name data_type ]
    3. [, ... ]
    4. )
    5. PARTITION BY RANGE (partition_key)
    6. (
    7. PARTITION partition_name START(partition_value) END (partition_value | MAXVALUE)
    8. [, ... ]
    9. );
  • 方式二:START(partition_value)方式

    1. CREATE TABLE partition_table_name
    2. ( [column_name data_type ]
    3. [, ... ]
    4. ] )
    5. PARTITION BY RANGE (partition_key)
    6. (
    7. PARTITION partition_name START(partition_value)
    8. [, ... ]
    9. );
  • 方式三:END(partition_value | MAXVALUE)方式

    1. CREATE TABLE partition_table_name
    2. ( [column_name data_type ]
    3. [, ... ]
    4. ] )
    5. PARTITION BY RANGE (partition_key)
    6. (
    7. PARTITION partition_name END(partition_value | MAXVALUE)
    8. [, ... ]
    9. );
  • 方式四:START(partition_value) END (partition_value) EVERY (interval_value)方式

    1. CREATE TABLE partition_table_name
    2. ( [column_name data_type ]
    3. [, ... ]
    4. ] )
    5. PARTITION BY RANGE (partition_key)
    6. (
    7. PARTITION partition_name START(partition_value) END (partition_value) EVERY (interval_value)
    8. [, ... ]
    9. );

创建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表示分区的上边界,它通常用于设置最后一个范围分区的上边界。

创建START END范围分区表示例

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

  1. --创建分区表graderecord
  2. openGauss=# CREATE TABLE graderecord
  3. (
  4. number INTEGER,
  5. name CHAR(20),
  6. class CHAR(20),
  7. grade INTEGER
  8. )
  9. PARTITION BY RANGE(grade)
  10. (
  11. PARTITION pass START(60) END(90),
  12. PARTITION excellent START(90) END(MAXVALUE)
  13. );
  14. -- 数据插入分区。
  15. openGauss=# insert into graderecord values('210101','Alan','21.01',92);
  16. openGauss=# insert into graderecord values('210102','Ben','21.01',62);
  17. openGauss=# insert into graderecord values('210103','Brain','21.01',26);
  18. openGauss=# insert into graderecord values('210204','Carl','21.02',77);
  19. openGauss=# insert into graderecord values('210205','David','21.02',47);
  20. openGauss=# insert into graderecord values('210206','Eric','21.02',97);
  21. openGauss=# insert into graderecord values('210307','Frank','21.03',90);
  22. openGauss=# insert into graderecord values('210308','Gavin','21.03',100);
  23. openGauss=# insert into graderecord values('210309','Henry','21.03',67);
  24. openGauss=# insert into graderecord values('210410','Jack','21.04',75);
  25. openGauss=# insert into graderecord values('210311','Jerry','21.04',60);
  26. --查询graderecord的数据。
  27. openGauss=# SELECT * FROM graderecord;
  28. number | name | class | grade
  29. --------+----------------------+----------------------+-------
  30. 210103 | Brain | 21.01 | 26
  31. 210205 | David | 21.02 | 47
  32. 210102 | Ben | 21.01 | 62
  33. 210204 | Carl | 21.02 | 77
  34. 210309 | Henry | 21.03 | 67
  35. 210410 | Jack | 21.04 | 75
  36. 210311 | Jerry | 21.04 | 60
  37. 210101 | Alan | 21.01 | 92
  38. 210206 | Eric | 21.02 | 97
  39. 210307 | Frank | 21.03 | 90
  40. 210308 | Gavin | 21.03 | 100
  41. (11 rows)
  42. --查询graderecordpass分区数据。
  43. openGauss=# SELECT * FROM graderecord PARTITION (pass);
  44. ERROR: partition "pass" of relation "graderecord" does not exist
  45. 查询失败。
  46. 原因是“PARTITION pass START(60) END(90),”是第一个分区定义,且该定义有START值,则范围(MINVALUE, 60)将自动作为第一个实际分区,其名称为“pass_0”。
  47. 而该定义语义描述的“60<= grade<90”分区的名称为“pass_1”。
  48. --查询graderecordpass_0分区数据。
  49. openGauss=# SELECT * FROM graderecord PARTITION (pass_0);
  50. number | name | class | grade
  51. --------+----------------------+----------------------+-------
  52. 210103 | Brain | 21.01 | 26
  53. 210205 | David | 21.02 | 47
  54. (2 rows)
  55. --查询graderecordpass_1分区数据。
  56. openGauss=# SELECT * FROM graderecord PARTITION (pass_1);
  57. number | name | class | grade
  58. --------+----------------------+----------------------+-------
  59. 210102 | Ben | 21.01 | 62
  60. 210204 | Carl | 21.02 | 77
  61. 210309 | Henry | 21.03 | 67
  62. 210410 | Jack | 21.04 | 75
  63. 210311 | Jerry | 21.04 | 60
  64. (5 rows)
  65. --查询graderecordexecllent分区数据。
  66. openGauss=# SELECT * FROM graderecord PARTITION (excellent);
  67. number | name | class | grade
  68. --------+----------------------+----------------------+-------
  69. 210101 | Alan | 21.01 | 92
  70. 210206 | Eric | 21.02 | 97
  71. 210307 | Frank | 21.03 | 90
  72. 210308 | Gavin | 21.03 | 100
  73. (4 rows)

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

  1. --创建分区表metro_ride_record。记录乘车人、上下车站点、乘坐站点数量。并按照乘坐站点数量,每3站建立一个分区。
  2. openGauss=# CREATE TABLE metro_ride_record
  3. (
  4. record_number INTEGER,
  5. name CHAR(20),
  6. enter_station CHAR(20),
  7. leave_station CHAR(20),
  8. ride_stations_number INTEGER
  9. )
  10. PARTITION BY RANGE(ride_stations_number)
  11. (
  12. PARTITION cost START(3) END(21) EVERY (3)
  13. );
  14. -- 数据插入分区。
  15. openGauss=# insert into metro_ride_record values('120101','Brain','Tung Chung','Tsing Yi',2);
  16. openGauss=# insert into metro_ride_record values('120102','David','Po Lam','Yau Tong',4);
  17. openGauss=# insert into metro_ride_record values('120103','Ben','Yau Ma Tei','Wong Tai Sin',6);
  18. openGauss=# insert into metro_ride_record values('120104','Carl','Tai Wo Hau','Prince Edward',8);
  19. openGauss=# insert into metro_ride_record values('120105','Henry','Admiralty','Lai King',10);
  20. openGauss=# insert into metro_ride_record values('120106','Jack','Chai Wan','Central',12);
  21. openGauss=# insert into metro_ride_record values('120107','Jerry','Central','Tai Wo Hau',14);
  22. openGauss=# insert into metro_ride_record values('120108','Alan','Diamond Hill','Kwai Hing',16);
  23. openGauss=# insert into metro_ride_record values('120109','Eric','Jordan','Shek Kip Mei',18);
  24. openGauss=# insert into metro_ride_record values('120110','Frank','Lok Fu','Sunny Bay',20);
  25. --查询metro_ride_record的数据。
  26. openGauss=# SELECT * FROM metro_ride_record;
  27. record_number | name | enter_station | leave_station | ride_stations_number
  28. ---------------+----------------------+----------------------+----------------------+----------------------
  29. 120101 | Brain | Tung Chung | Tsing Yi | 2
  30. 120102 | David | Po Lam | Yau Tong | 4
  31. 120103 | Ben | Yau Ma Tei | Wong Tai Sin | 6
  32. 120104 | Carl | Tai Wo Hau | Prince Edward | 8
  33. 120105 | Henry | Admiralty | Lai King | 10
  34. 120106 | Jack | Chai Wan | Central | 12
  35. 120107 | Jerry | Central | Tai Wo Hau | 14
  36. 120108 | Alan | Diamond Hill | Kwai Hing | 16
  37. 120109 | Eric | Jordan | Shek Kip Mei | 18
  38. 120110 | Frank | Lok Fu | Sunny Bay | 20
  39. (10 rows)
  40. PARTITION cost START(3) END(21) EVERY (3)”是第一个分区定义,且该定义有START值,则范围(MINVALUE, 3)将自动作为第一个实际分区,其名称为“cost_0”。
  41. 其余分区依次为“cost_1”、...、“cost_6”.
  42. --查询metro_ride_recordcost_0分区数据。
  43. openGauss=# SELECT * FROM metro_ride_record PARTITION (cost_0);
  44. record_number | name | enter_station | leave_station | ride_stations_number
  45. ---------------+----------------------+----------------------+----------------------+----------------------
  46. 120101 | Brain | Tung Chung | Tsing Yi | 2
  47. (1 row)
  48. --查询metro_ride_recordcost_1分区数据。
  49. openGauss=# SELECT * FROM metro_ride_record PARTITION (cost_1);
  50. record_number | name | enter_station | leave_station | ride_stations_number
  51. ---------------+----------------------+----------------------+----------------------+----------------------
  52. 120102 | David | Po Lam | Yau Tong | 4
  53. (1 row)
  54. --查询metro_ride_recordcost_6分区数据。
  55. openGauss=# SELECT * FROM metro_ride_record PARTITION (cost_6);
  56. record_number | name | enter_station | leave_station | ride_stations_number
  57. ---------------+----------------------+----------------------+----------------------+----------------------
  58. 120109 | Eric | Jordan | Shek Kip Mei | 18
  59. 120110 | Frank | Lok Fu | Sunny Bay | 20
  60. (2 rows)

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

  1. --创建分区表graderecord
  2. openGauss=# CREATE TABLE graderecord
  3. (
  4. number INTEGER,
  5. name CHAR(20),
  6. class CHAR(20),
  7. grade INTEGER
  8. )
  9. PARTITION BY RANGE(grade)
  10. (
  11. PARTITION pass START(60),
  12. PARTITION excellent START(90)
  13. );
  14. -- 数据插入分区。
  15. openGauss=# insert into graderecord values('210101','Alan','21.01',92);
  16. openGauss=# insert into graderecord values('210102','Ben','21.01',62);
  17. openGauss=# insert into graderecord values('210103','Brain','21.01',26);
  18. openGauss=# insert into graderecord values('210204','Carl','21.02',77);
  19. openGauss=# insert into graderecord values('210205','David','21.02',47);
  20. openGauss=# insert into graderecord values('210206','Eric','21.02',97);
  21. openGauss=# insert into graderecord values('210307','Frank','21.03',90);
  22. openGauss=# insert into graderecord values('210308','Gavin','21.03',100);
  23. openGauss=# insert into graderecord values('210309','Henry','21.03',67);
  24. openGauss=# insert into graderecord values('210410','Jack','21.04',75);
  25. openGauss=# insert into graderecord values('210311','Jerry','21.04',60);
  26. --查询graderecord的数据。
  27. openGauss=# SELECT * FROM graderecord;
  28. number | name | class | grade
  29. --------+----------------------+----------------------+-------
  30. 210103 | Brain | 21.01 | 26
  31. 210205 | David | 21.02 | 47
  32. 210102 | Ben | 21.01 | 62
  33. 210204 | Carl | 21.02 | 77
  34. 210309 | Henry | 21.03 | 67
  35. 210410 | Jack | 21.04 | 75
  36. 210311 | Jerry | 21.04 | 60
  37. 210101 | Alan | 21.01 | 92
  38. 210206 | Eric | 21.02 | 97
  39. 210307 | Frank | 21.03 | 90
  40. 210308 | Gavin | 21.03 | 100
  41. (11 rows)
  42. --查询graderecordpass分区数据。
  43. openGauss=# SELECT * FROM graderecord PARTITION (pass);
  44. ERROR: partition "pass" of relation "graderecord" does not exist
  45. 查询失败。
  46. 原因是“PARTITION pass START(60),”是第一个分区定义,且该定义有START值,则范围(MINVALUE, 60)将自动作为第一个实际分区,其名称为“pass_0”。
  47. 而该定义语义描述的“60<= grade<90”分区的名称为“pass_1”。
  48. --查询graderecordpass_0分区数据。
  49. openGauss=# SELECT * FROM graderecord PARTITION (pass_0);
  50. number | name | class | grade
  51. --------+----------------------+----------------------+-------
  52. 210103 | Brain | 21.01 | 26
  53. 210205 | David | 21.02 | 47
  54. (2 rows)
  55. --查询graderecordpass_1分区数据。
  56. openGauss=# SELECT * FROM graderecord PARTITION (pass_1);
  57. number | name | class | grade
  58. --------+----------------------+----------------------+-------
  59. 210102 | Ben | 21.01 | 62
  60. 210204 | Carl | 21.02 | 77
  61. 210309 | Henry | 21.03 | 67
  62. 210410 | Jack | 21.04 | 75
  63. 210311 | Jerry | 21.04 | 60
  64. (5 rows)
  65. --查询graderecordexecllent分区数据。
  66. openGauss=# SELECT * FROM graderecord PARTITION (excellent);
  67. number | name | class | grade
  68. --------+----------------------+----------------------+-------
  69. 210101 | Alan | 21.01 | 92
  70. 210206 | Eric | 21.02 | 97
  71. 210307 | Frank | 21.03 | 90
  72. 210308 | Gavin | 21.03 | 100
  73. (4 rows)

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

  1. --创建分区表graderecord
  2. openGauss=# CREATE TABLE graderecord
  3. (
  4. number INTEGER,
  5. name CHAR(20),
  6. class CHAR(20),
  7. grade INTEGER
  8. )
  9. PARTITION BY RANGE(grade)
  10. (
  11. PARTITION no_pass END(60),
  12. PARTITION pass END(90),
  13. PARTITION excellent END(MAXVALUE)
  14. );
  15. -- 数据插入分区。
  16. openGauss=# insert into graderecord values('210101','Alan','21.01',92);
  17. openGauss=# insert into graderecord values('210102','Ben','21.01',62);
  18. openGauss=# insert into graderecord values('210103','Brain','21.01',26);
  19. openGauss=# insert into graderecord values('210204','Carl','21.02',77);
  20. openGauss=# insert into graderecord values('210205','David','21.02',47);
  21. openGauss=# insert into graderecord values('210206','Eric','21.02',97);
  22. openGauss=# insert into graderecord values('210307','Frank','21.03',90);
  23. openGauss=# insert into graderecord values('210308','Gavin','21.03',100);
  24. openGauss=# insert into graderecord values('210309','Henry','21.03',67);
  25. openGauss=# insert into graderecord values('210410','Jack','21.04',75);
  26. openGauss=# insert into graderecord values('210311','Jerry','21.04',60);
  27. --查询graderecord的数据。
  28. openGauss=# SELECT * FROM graderecord;
  29. number | name | class | grade
  30. --------+----------------------+----------------------+-------
  31. 210103 | Brain | 21.01 | 26
  32. 210205 | David | 21.02 | 47
  33. 210102 | Ben | 21.01 | 62
  34. 210204 | Carl | 21.02 | 77
  35. 210309 | Henry | 21.03 | 67
  36. 210410 | Jack | 21.04 | 75
  37. 210311 | Jerry | 21.04 | 60
  38. 210101 | Alan | 21.01 | 92
  39. 210206 | Eric | 21.02 | 97
  40. 210307 | Frank | 21.03 | 90
  41. 210308 | Gavin | 21.03 | 100
  42. (11 rows)
  43. --查询graderecordno_pass分区数据。
  44. openGauss=# SELECT * FROM graderecord PARTITION (no_pass);
  45. number | name | class | grade
  46. --------+----------------------+----------------------+-------
  47. 210103 | Brain | 21.01 | 26
  48. 210205 | David | 21.02 | 47
  49. (2 rows)
  50. --查询graderecordpass分区数据。
  51. openGauss=# SELECT * FROM graderecord PARTITION (pass);
  52. number | name | class | grade
  53. --------+----------------------+----------------------+-------
  54. 210102 | Ben | 21.01 | 62
  55. 210204 | Carl | 21.02 | 77
  56. 210309 | Henry | 21.03 | 67
  57. 210410 | Jack | 21.04 | 75
  58. 210311 | Jerry | 21.04 | 60
  59. (5 rows)
  60. --查询graderecordexecllent分区数据。
  61. openGauss=# SELECT * FROM graderecord PARTITION (excellent);
  62. number | name | class | grade
  63. --------+----------------------+----------------------+-------
  64. 210101 | Alan | 21.01 | 92
  65. 210206 | Eric | 21.02 | 97
  66. 210307 | Frank | 21.03 | 90
  67. 210308 | Gavin | 21.03 | 100
  68. (4 rows)

创建列表分区表语法格式

  1. CREATE TABLE partition_table_name
  2. ( [column_name data_type ]
  3. [, ... ]
  4. )
  5. PARTITION BY LIST (partition_key)
  6. (
  7. PARTITION partition_name VALUES (list_values_clause)
  8. [, ... ]
  9. );

创建列表分区表参数说明

  • 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表示分区的上边界,它通常用于设置最后一个范围分区的上边界。

创建列表分区表示例

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

  1. --创建分区表graderecord
  2. openGauss=# CREATE TABLE graderecord
  3. (
  4. number INTEGER,
  5. name CHAR(20),
  6. class CHAR(20),
  7. grade INTEGER
  8. )
  9. PARTITION BY LIST(class)
  10. (
  11. PARTITION class_01 VALUES ('21.01'),
  12. PARTITION class_02 VALUES ('21.02'),
  13. PARTITION class_03 VALUES ('21.03'),
  14. PARTITION class_04 VALUES ('21.04')
  15. );
  16. -- 数据插入分区。
  17. openGauss=# insert into graderecord values('210101','Alan','21.01',92);
  18. openGauss=# insert into graderecord values('210102','Ben','21.01',62);
  19. openGauss=# insert into graderecord values('210103','Brain','21.01',26);
  20. openGauss=# insert into graderecord values('210204','Carl','21.02',77);
  21. openGauss=# insert into graderecord values('210205','David','21.02',47);
  22. openGauss=# insert into graderecord values('210206','Eric','21.02',97);
  23. openGauss=# insert into graderecord values('210307','Frank','21.03',90);
  24. openGauss=# insert into graderecord values('210308','Gavin','21.03',100);
  25. openGauss=# insert into graderecord values('210309','Henry','21.03',67);
  26. openGauss=# insert into graderecord values('210410','Jack','21.04',75);
  27. openGauss=# insert into graderecord values('210311','Jerry','21.04',60);
  28. --查询graderecord的数据。
  29. openGauss=# SELECT * FROM graderecord;
  30. number | name | class | grade
  31. --------+----------------------+----------------------+-------
  32. 210410 | Jack | 21.04 | 75
  33. 210311 | Jerry | 21.04 | 60
  34. 210307 | Frank | 21.03 | 90
  35. 210308 | Gavin | 21.03 | 100
  36. 210309 | Henry | 21.03 | 67
  37. 210204 | Carl | 21.02 | 77
  38. 210205 | David | 21.02 | 47
  39. 210206 | Eric | 21.02 | 97
  40. 210101 | Alan | 21.01 | 92
  41. 210102 | Ben | 21.01 | 62
  42. 210103 | Brain | 21.01 | 26
  43. (11 rows)
  44. --查询graderecordclass_01分区数据。
  45. openGauss=# SELECT * FROM graderecord PARTITION (class_01);
  46. number | name | class | grade
  47. --------+----------------------+----------------------+-------
  48. 210101 | Alan | 21.01 | 92
  49. 210102 | Ben | 21.01 | 62
  50. 210103 | Brain | 21.01 | 26
  51. (3 rows)
  52. --查询graderecordclass_04分区数据。
  53. openGauss=# SELECT * FROM graderecord PARTITION (class_04);
  54. number | name | class | grade
  55. --------+----------------------+----------------------+-------
  56. 210410 | Jack | 21.04 | 75
  57. 210311 | Jerry | 21.04 | 60
  58. (2 rows)

创建间隔分区表语法格式

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

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

  1. CREATE TABLE partition_table_name
  2. ( [column_name data_type ]
  3. [, ... ]
  4. )
  5. PARTITION BY RANGE (partition_key)
  6. (
  7. INTERVAL ('interval_expr')
  8. PARTITION partition_name VALUES LESS THAN (partition_value | MAXVALUE})
  9. [, ... ]
  10. );

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

方式一:START(partition_value) END (partition_value | MAXVALUE)方式

  1. CREATE TABLE partition_table_name
  2. ( [column_name data_type ]
  3. [, ... ]
  4. )
  5. PARTITION BY RANGE (partition_key)
  6. (
  7. INTERVAL ('interval_expr')
  8. PARTITION partition_name START(partition_value) END (partition_value | MAXVALUE)
  9. [, ... ]
  10. );

方式二:START(partition_value) END (partition_value) EVERY (interval_value)方式

  1. CREATE TABLE partition_table_name
  2. ( [column_name data_type ]
  3. [, ... ]
  4. ] )
  5. PARTITION BY RANGE (partition_key)
  6. (
  7. PARTITION partition_name START(partition_value) END (partition_value) EVERY (interval_value)
  8. [, ... ]
  9. );

方式三:START(partition_value)方式

  1. CREATE TABLE partition_table_name
  2. ( [column_name data_type ]
  3. [, ... ]
  4. ] )
  5. PARTITION BY RANGE (partition_key)
  6. (
  7. INTERVAL ('interval_expr')
  8. PARTITION partition_name START(partition_value)
  9. [, ... ]
  10. );

方式四:END(partition_value | MAXVALUE)方式

  1. CREATE TABLE partition_table_name
  2. ( [column_name data_type ]
  3. [, ... ]
  4. ] )
  5. PARTITION BY RANGE (partition_key)
  6. INTERVAL ('interval_expr')
  7. (
  8. PARTITION partition_name END(partition_value | MAXVALUE)
  9. [, ... ]
  10. );

间隔分区表参数说明

  • 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…

间隔分区表语法示例

示例8:间隔分区表sales_table。

  1. --创建分区表sales_table
  2. openGauss=# CREATE TABLE sales_table
  3. (
  4. order_no INTEGER NOT NULL,
  5. goods_name CHAR(20) NOT NULL,
  6. sales_date DATE NOT NULL,
  7. sales_volume INTEGER,
  8. sales_store CHAR(20)
  9. )
  10. PARTITION BY RANGE(sales_date)
  11. INTERVAL ('1 month')
  12. (
  13. PARTITION start VALUES LESS THAN('2021-01-01 00:00:00'),
  14. PARTITION later VALUES LESS THAN('2021-01-10 00:00:00')
  15. );
  16. -- 数据插入分区later
  17. openGauss=# INSERT INTO sales_table VALUES(1, 'jacket', '2021-01-8 00:00:00', 3,'Alaska');
  18. -- 不在已有分区的数据插入,系统会新建分区sys_p1
  19. openGauss=# INSERT INTO sales_table VALUES(2, 'hat', '2021-04-06 00:00:00', 255,'Clolorado');
  20. -- 不在已有分区的数据插入,系统会新建分区sys_p2
  21. openGauss=# INSERT INTO sales_table VALUES(3, 'shirt', '2021-11-17 00:00:00', 7000,'Florida');
  22. -- 数据插入分区start
  23. openGauss=# INSERT INTO sales_table VALUES(4, 'coat', '2020-10-21 00:00:00', 9000,'Hawaii');
  24. --查询sales_table的数据。
  25. openGauss=# SELECT * FROM sales_table;
  26. order_no | goods_name | sales_date | sales_volume | sales_store
  27. ----------+----------------------+---------------------+--------------+----------------------
  28. 4 | coat | 2020-10-21 00:00:00 | 9000 | Hawaii
  29. 1 | jacket | 2021-01-08 00:00:00 | 3 | Alaska
  30. 2 | hat | 2021-04-06 00:00:00 | 255 | Clolorado
  31. 3 | shirt | 2021-11-17 00:00:00 | 7000 | Florida
  32. (4 rows)
  33. --查询sales_tablestart分区数据。这里采用“sales_table PARTITION (start);”来引用分区。
  34. openGauss=# SELECT * FROM sales_table PARTITION (start);
  35. order_no | goods_name | sales_date | sales_volume | sales_store
  36. ----------+----------------------+---------------------+--------------+----------------------
  37. 4 | coat | 2020-10-21 00:00:00 | 9000 | Hawaii
  38. (1 row)
  39. --查询sales_tablelater分区数据。这里采用“sales_table PARTITION (later);”来引用分区。
  40. openGauss=# SELECT * FROM sales_table PARTITION (later);
  41. order_no | goods_name | sales_date | sales_volume | sales_store
  42. ----------+----------------------+---------------------+--------------+----------------------
  43. 1 | jacket | 2021-01-08 00:00:00 | 3 | Alaska
  44. (1 row)
  45. --查询sales_tablesys_p1分区数据。这里采用“sales_table PARTITION (sys_p1);”来引用分区。
  46. openGauss=# SELECT * FROM sales_table PARTITION (sys_p1);
  47. order_no | goods_name | sales_date | sales_volume | sales_store
  48. ----------+----------------------+---------------------+--------------+----------------------
  49. 2 | hat | 2021-04-06 00:00:00 | 255 | Clolorado
  50. (1 row)
  51. --查询sales_tablesys_p2分区数据。这里采用“sales_table PARTITION (sys_p2);”来引用分区。
  52. openGauss=# SELECT * FROM sales_table PARTITION (sys_p2);
  53. order_no | goods_name | sales_date | sales_volume | sales_store
  54. ----------+----------------------+---------------------+--------------+----------------------
  55. 3 | shirt | 2021-11-17 00:00:00 | 7000 | Florida
  56. (1 row)

哈希分区表语法格式

  1. CREATE TABLE partition_table_name
  2. ( [column_name data_type ]
  3. [, ... ]
  4. )
  5. PARTITION BY HASH (partition_key)
  6. (PARTITION partition_name )
  7. [, ... ]
  8. );

哈希分区表参数说明

  • partition_table_name

    分区表的名称。

  • column_name

    新表中要创建的字段名。

  • data_type

    字段的数据类型。

  • partition_key

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

  • partition_name

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

哈希分区表示例

示例9:哈希分区表hash_partition_table。

  1. --创建哈希分区表hash_partition_table
  2. openGauss=# create table hash_partition_table (
  3. col1 int,
  4. col2 int)
  5. partition by hash(col1)
  6. (
  7. partition p1,
  8. partition p2
  9. );
  10. -- 数据插入
  11. openGauss=# INSERT INTO hash_partition_table VALUES(1, 1);
  12. INSERT 0 1
  13. openGauss=# INSERT INTO hash_partition_table VALUES(2, 2);
  14. INSERT 0 1
  15. openGauss=# INSERT INTO hash_partition_table VALUES(3, 3);
  16. INSERT 0 1
  17. openGauss=# INSERT INTO hash_partition_table VALUES(4, 4);
  18. INSERT 0 1
  19. -- 查看数据
  20. openGauss=# select * from hash_partition_table partition (p1);
  21. col1 | col2
  22. ------+------
  23. 3 | 3
  24. 4 | 4
  25. (2 rows)
  26. openGauss=# select * from hash_partition_table partition (p2);
  27. col1 | col2
  28. ------+------
  29. 1 | 1
  30. 2 | 2
  31. (2 rows)

导入数据语法格式

导入单行数据:

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

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

  1. INSERT INTO partition_table_name SELECT * FROM source_table_name

导入数据参数说明

  • partition_table_name

    分区表的名称。

  • column_name

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

  • value

    字段对应的值:

    • 提供了column_name值时:value子句提供的值从左到右关联到对应列。
    • 没提供column_name值时:value子句提供的值从左到右关联到partition_table_name对应列。

导入数据示例

示例10:

  1. --创建分区表employees_table
  2. openGauss=# CREATE TABLE employees_table
  3. (
  4. employee_id INTEGER NOT NULL,
  5. employee_name CHAR(20) NOT NULL,
  6. onboarding_date DATE NOT NULL,
  7. position CHAR(20)
  8. )
  9. PARTITION BY RANGE(onboarding_date)
  10. (
  11. PARTITION founders VALUES LESS THAN('2000-01-01 00:00:00'),
  12. PARTITION senate VALUES LESS THAN('2010-01-01 00:00:00'),
  13. PARTITION seniors VALUES LESS THAN('2020-01-01 00:00:00'),
  14. PARTITION newcomer VALUES LESS THAN(MAXVALUE)
  15. );
  16. -- 数据插入分区founders
  17. openGauss=# INSERT INTO employees_table VALUES(1, 'SMITH', '1997-01-10 00:00:00','Manager');
  18. -- 查看founders分区数据
  19. openGauss=# select * from employees_table partition (founders);
  20. -- 创建表格employees_data_table
  21. openGauss=# CREATE TABLE employees_data_table
  22. (
  23. employee_id INTEGER NOT NULL,
  24. employee_name CHAR(20) NOT NULL,
  25. onboarding_date DATE NOT NULL,
  26. position CHAR(20)
  27. );
  28. -- 插入数据
  29. openGauss=# insert into employees_data_table (employee_id, employee_name, onboarding_date, position) VALUES
  30. (2, 'JONES', '2001-05-06 00:00:00', 'Supervisor'),
  31. (3, 'WILLIAMS', '2011-09-17 00:00:00', 'Engineer'),
  32. (4, 'TAYLOR', '2021-10-21 00:00:00', 'Clerk');
  33. -- 查看表格数据
  34. openGauss=# select * from employees_data_table;
  35. --数据导入employees_table
  36. openGauss=# INSERT INTO employees_table SELECT * FROM employees_data_table;
  37. -- 查看senate分区数据
  38. openGauss=# select * from employees_table partition (senate);
  39. employee_id | employee_name | onboarding_date | position
  40. -------------+----------------------+---------------------+----------------------
  41. 2 | JONES | 2001-05-06 00:00:00 | Supervisor
  42. (1 row)
  43. -- 查看seniors分区数据
  44. openGauss=# select * from employees_table partition (seniors);
  45. employee_id | employee_name | onboarding_date | position
  46. -------------+----------------------+---------------------+----------------------
  47. 3 | WILLIAMS | 2011-09-17 00:00:00 | Engineer
  48. (1 row)
  49. -- 查看newcomer分区数据
  50. openGauss=# select * from employees_table partition (newcomer);
  51. employee_id | employee_name | onboarding_date | position
  52. -------------+----------------------+---------------------+----------------------
  53. 4 | TAYLOR | 2021-10-21 00:00:00 | Clerk
  54. (1 row)

修改分区表语法格式

  • 删除分区:

    1. ALTER TABLE partition_table_name DROP PARTITION partition_name;
  • 增加分区:

    1. ALTER TABLE partition_table_name ADD {partition_less_than_item | partition_start_end_item| partition_list_item };
  • 重命名分区:

    1. ALTER TABLE partition_table_name RENAME PARTITION partition_name TO partition_new_name;
  • 分裂分区(指定切割点split_partition_value的语法):

    1. ALTER TABLE partition_table_name SPLIT PARTITION partition_name AT ( split_partition_value ) INTO ( PARTITION partition_new_name1, PARTITION partition_new_name2);
  • 分裂分区(指定分区范围的语法):

    1. ALTER TABLE partition_table_name SPLIT PARTITION partition_name INTO { ( partition_less_than_item [, ...] ) | ( partition_start_end_item [, ...] ) };
  • 合并分区:

    1. ALTER TABLE partition_table_name MERGE PARTITIONS { partition_name } [, ...] INTO PARTITION partition_name;

修改分区表参数说明

  • partition_table_name

    分区表的名称。

  • partition_name

    partition_name为分区的名称。

  • split_partition_value

    切割点。

  • PARTITION partition_new_name1, PARTITION partition_new_name2

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

  • partition_less_than_item

    分区项的描述语句,语法为:

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

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

  • partition_start_end_item

    分区项的描述语句,语法为:

    1. PARTITION partition_name {
    2. {START(partition_value) END (partition_value) EVERY (interval_value)} |
    3. {START(partition_value) END ({partition_value | MAXVALUE})} |
    4. {START(partition_value)} |
    5. {END({partition_value | MAXVALUE})}

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

  • partition_list_item

    分区项的描述语句,语法为:

    1. PARTITION partition_name VALUES (list_values_clause)

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

  • split_point_clause

    分裂分区时,指定的切割点。

  • partition_value

    分区键值。

修改分区表示例

示例11:

  1. --创建分区表employees_table
  2. openGauss=# CREATE TABLE employees_table
  3. (
  4. employee_id INTEGER NOT NULL,
  5. employee_name CHAR(20) NOT NULL,
  6. onboarding_date DATE NOT NULL,
  7. position CHAR(20)
  8. )
  9. PARTITION BY RANGE(onboarding_date)
  10. (
  11. PARTITION founders VALUES LESS THAN('2000-01-01 00:00:00'),
  12. PARTITION senate VALUES LESS THAN('2010-01-01 00:00:00'),
  13. PARTITION seniors VALUES LESS THAN('2020-01-01 00:00:00'),
  14. PARTITION newcomer VALUES LESS THAN(MAXVALUE)
  15. );
  16. -- 插入数据
  17. openGauss=# INSERT INTO employees_table VALUES
  18. (1, 'SMITH', '1997-01-10 00:00:00','Manager'),
  19. (2, 'JONES', '2001-05-06 00:00:00', 'Supervisor'),
  20. (3, 'WILLIAMS', '2011-09-17 00:00:00', 'Engineer'),
  21. (4, 'TAYLOR', '2021-10-21 00:00:00', 'Clerk');
  22. 查看newcomer分区
  23. openGauss=# SELECT * FROM employees_table PARTITION (newcomer);
  24. employee_id | employee_name | onboarding_date | position
  25. -------------+----------------------+---------------------+----------------------
  26. 4 | TAYLOR | 2021-10-21 00:00:00 | Clerk
  27. (1 row)
  28. --删除newcomer分区。
  29. openGauss=# ALTER TABLE employees_table DROP PARTITION newcomer;
  30. ALTER TABLE
  31. -- 查看newcomer分区数据
  32. openGauss=# select * from employees_table partition (newcomer);
  33. ERROR: partition "newcomer" of relation "employees_table" does not exist
  34. --增加fresh分区。
  35. openGauss=# ALTER TABLE employees_table ADD PARTITION fresh VALUES LESS THAN ('2040-01-01 00:00:00');
  36. ALTER TABLE
  37. --以2030-01-01 00:00:00为分割点,分裂fresh分区为currentfuture两个分区
  38. openGauss=# ALTER TABLE employees_table SPLIT PARTITION fresh AT ('2030-01-01 00:00:00') INTO (PARTITION current, PARTITION future);
  39. ALTER TABLE
  40. --将分区current改名为now
  41. openGauss=# ALTER TABLE employees_table RENAME PARTITION current TO now;
  42. ALTER TABLE
  43. --将founderssenate合并为一个分区original
  44. openGauss=# ALTER TABLE employees_table MERGE PARTITIONS founders, senate INTO PARTITION original;

删除分区表语法格式

  1. DROP TABLE partition_table_name;

删除分区表参数说明

  • partition_table_name

    分区表的名称。

删除分区表示例

示例12:

  1. --删除分区表employees_table
  2. openGauss=# DROP TABLE employees_table;
  3. DROP TABLE