• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Database Quick Start
  5. Other Operations
  6. Creating and Managing Partitioned Tables

Creating and Managing Partitioned Tables

Context

DWS supports range 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.

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, so the query efficiency is improved.
  • High availability: If a partition in a partitioned table is faulty, data in the other partitions is still available.
  • Easy maintenance: To fix a partitioned table having a faulty partition, you simply need to fix the 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 partitioned table.
    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)
    )
    TABLESPACE example1
    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) TABLESPACE example2
    )
    ENABLE ROW MOVEMENT;

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

    CREATE TABLE
    NOTE:

    You are advised to creat 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:
    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)
    )
    TABLESPACE example1
    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) TABLESPACE example2
    )
    ENABLE ROW MOVEMENT;
    CREATE TABLE
    INSERT INTO tpcds.web_returns_p2 SELECT * FROM tpcds.customer_address;
    INSERT 0 0
  • Modify the row movement attributes of a partitioned table.
    ALTER TABLE tpcds.web_returns_p2 DISABLE ROW MOVEMENT;
    ALTER TABLE
  • Delete a partition.
    Run the following command to delete partition P8:
    ALTER TABLE tpcds.web_returns_p2 DROP PARTITION P8;
    ALTER TABLE
  • Add a partition.

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

    ALTER TABLE tpcds.web_returns_p2 ADD PARTITION P8 VALUES LESS THAN (MAXVALUE);
    ALTER TABLE
  • Rename a partition.
    • Run the following command to rename partition P8 to P_9:
      ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION P8 TO P_9;
      ALTER TABLE
    • Run the following command to rename partition P_9 to P8:
      ALTER TABLE tpcds.web_returns_p2 RENAME PARTITION FOR (40000) TO P8;
      ALTER TABLE
  • Query a partition.
    Run the following command to query partition P6:
     SELECT * FROM tpcds.web_returns_p2 PARTITION (P6);
     SELECT * FROM tpcds.web_returns_p2 PARTITION FOR (35888);
  • Delete a partitioned table.
    DROP TABLE tpcds.web_returns_p2;
    DROP TABLE