Help Center/ GaussDB(DWS)/ Getting Started/ Database Quick Start/ Creating and Managing Partitioned Tables
Updated on 2023-11-14 GMT+08:00
View PDF

Creating and Managing Partitioned Tables

Background

GaussDB(DWS) supports range partitioned tables and list partitioned tables.

Range partitioned table: Data within a specific range is mapped onto each partition. The range is determined by the partition key specified when the partitioned table is created. This partitioning mode is most commonly used. The partition key is usually a date. For example, sales data is partitioned by month.

List partitioned table: Data is mapped to partitions based on partition keys. These keys do not overlap in different partitions. Create a partition for each group of key values to store corresponding data. List partitioning is supported only by clusters of 8.1.3 and later versions.

A partitioned table has the following advantages over an ordinary table:

  • High query performance: The system queries only the concerned partitions rather than the whole table, improving the query efficiency.
  • High availability: If a partition is faulty, data in the other partitions is still available.
  • Easy maintenance: You only need to fix the faulty partition.
  • Balanced I/O: Partitions can be mapped to different disks to balance I/O and improve the overall system performance.

To convert an ordinary table to a partitioned table, you need to create a partitioned table and import data to it from the ordinary table. When you design tables, plan whether to use partitioned tables based on service requirements.

Procedure

  • Perform the following operations on a range partitioned table.
    • Create a range partitioned table: 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
      		 
      CREATE TABLE tpcds.customer_address
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10)                       ,
    ca_street_name      character varying(60)               ,
    ca_street_type      character(15)                       ,
    ca_suite_number     character(10)                       ,
    ca_city             character varying(60)               ,
    ca_county           character varying(30)               ,
    ca_state            character(2)                        ,
    ca_zip              character(10)                       ,
    ca_country           character varying(20)               ,
    ca_gmt_offset       numeric(5,2)                        ,
    ca_location_type    character(20)
)
DISTRIBUTE BY HASH (ca_address_sk)
PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;

      If the following information is displayed, the table is created.

      1
      		 
      CREATE TABLE

      Create a maximum of 1000 column-store partitioned tables.

    • Insert data.

      Insert data from the tpcds.customer_address table to the tpcds.web_returns_p2 table.

      For example, you can run the following command to insert the data of the tpcds.customer_address table into its backup table tpcds.web_returns_p2: 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
      		 
      CREATE TABLE tpcds.web_returns_p2
(
    ca_address_sk       integer                  NOT NULL   ,
    ca_address_id       character(16)            NOT NULL   ,
    ca_street_number    character(10)                       ,
    ca_street_name      character varying(60)               ,
    ca_street_type      character(15)                       ,
    ca_suite_number     character(10)                       ,
    ca_city             character varying(60)               ,
    ca_county           character varying(30)               ,
    ca_state            character(2)                        ,
    ca_zip              character(10)                       ,
    ca_country           character varying(20)               ,
    ca_gmt_offset       numeric(5,2)                        ,
    ca_location_type    character(20)
)
DISTRIBUTE BY HASH (ca_address_sk)
PARTITION BY RANGE (ca_address_sk)
(
        PARTITION P1 VALUES LESS THAN(5000),
        PARTITION P2 VALUES LESS THAN(10000),
        PARTITION P3 VALUES LESS THAN(15000),
        PARTITION P4 VALUES LESS THAN(20000),
        PARTITION P5 VALUES LESS THAN(25000),
        PARTITION P6 VALUES LESS THAN(30000),
        PARTITION P7 VALUES LESS THAN(40000),
        PARTITION P8 VALUES LESS THAN(MAXVALUE)
)
ENABLE ROW MOVEMENT;
CREATE TABLE
INSERT INTO tpcds.web_returns_p2 SELECT * FROM tpcds.customer_address;
INSERT 0 0

      ROW MOVEMENT is disabled by default. In this case, cross-partition update is not allowed. To enable cross-partition update, specify ENABLE ROW MOVEMENT. However, if SELECT FOR UPDATE is executed concurrently to query the partitioned table, the query results may be inconsistent. Therefore, exercise caution when performing this operation.

    • Modify the row movement attributes of a partitioned table. 
      1
      		 
      ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT;
    • Delete a partition.
      Run the following command to delete partition P8: 
      1
      		 
      ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8;
    • Add a partition.

      Run the following command to add partition P8 and set its range to [40000, MAXVALUE]:

      1
      		 
      ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
    • Rename a partition.
      • Run the following command to rename partition P8 to P_9: 
        1
        		 
        ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9;
      • Run the following command to rename partition P_9 to P8: 
        1
        		 
        ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8;
    • Query a partition.
      Run the following command to query partition P7: 
      1
2
      		 
      SELECT * FROM tpcds.web_returns_p2 PARTITION (P7);
SELECT * FROM tpcds.web_returns_p2 PARTITION FOR (35888);
    • View partitioned tables using the system catalog dba_tab_partitions. 
      1
      		 
      SELECT * FROM dba_tab_partitions WHERE table_name='tpcds.customer_address';
    • Delete a partitioned table. 
      1
      		 
      DROP TABLE tpcds.web_returns_p2;
  • Perform the following operations on a list partitioned table:
    • Create a list partitioned table. 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
      		 
      CREATE TABLE data_list
(
    id int,
    time int, 
    sarlay decimal(12,2)
)
PARTITION BY LIST (time)
(
        PARTITION P1 VALUES (202209),
        PARTITION P2 VALUES (202210,202208),
        PARTITION P3 VALUES (202211),
        PARTITION P4 VALUES (202212),
        PARTITION P5 VALUES (202301) 
);

      If the following information is displayed, the tablespaces are created.

      1
      		 
      CREATE TABLE
    • Insert data. 
      1
      		 
      INSERT INTO data_list VALUES (1,202209,10000),(2,202210,20000),(3,202211,30000),(4,202212,40000),(5,202301,50000),(6,202301,60000);
    • Add a partition. 
      1
      		 
      ALTER TABLE data_list ADD PARTITION P6 VALUES (202302,202303);
    • Split partitions. 
      1
      		 
      ALTER TABLE data_list SPLIT PARTITION P2 VALUES(202210) INTO (PARTITION p2a,PARTITION p2b);
    • Merge partitions. 
      1
      		 
      ALTER TABLE data_list MERGE PARTITIONS p2a,p2b INTO PARTITION P2;
    • Rename a partition.
      • Rename partition P4 to P_5. 
        1
        		 
        ALTER TABLE data_list RENAME PARTITION P4 TO P_5;
      • Rename partition P_5 to P4. 
        1
        		 
        ALTER TABLE data_list RENAME PARTITION FOR (202212) TO P4;
    • Delete a partition.
      Delete partition P1. 
      1
      		 
      ALTER TABLE data_list DROP PARTITION P1;
    • Query a partition.
      Query partition P5. 
      1
2
      		 
      SELECT * FROM data_list PARTITION (P5);
SELECT * FROM data_list PARTITION FOR (202301);
    • View partitioned tables using the system catalog dba_tab_partitions. 
      1
      		 
      SELECT * FROM dba_tab_partitions where table_name='data_list';
    • Delete a partitioned table. 
      1
      		 
      DROP TABLE data_list;
Parent topic: Database Quick Start