Defining Table Partitions

Partitioning refers to splitting what is logically one large table into smaller physical pieces based on specific schemes. The table based on the logic is called a partition cable, and a physical piece is called a partition. Data is stored on these smaller physical pieces, namely, partitions, instead of the larger logical partitioned table. During conditional query, the system scans only the partitions that meet the conditions rather than scanning the entire table improving query performance.

Advantages of partitioned tables:

Improved query performance. You can search in specific partitions, improving the search efficiency.
Enhanced availability. If a partition is faulty, data in other partitions is still available.
Improved maintainability. For expired historical data that needs to be periodically deleted, you can quickly delete it by dropping or truncate partitions.

Supported Table Partition Types

Range partitioning: partitions are created based on a numeric range, for example, by date or price range.
List partitioning: partitions are created based on a list of values, such as sales scope or product attribute. Only clusters of 8.1.3 and later versions support this function.

Choosing to Partition a Table

You can choose to partition a table when the table has the following characteristics:

There are obvious ranges among the fields of the table.
A table is partitioned based on obvious rangeable fields. Generally, columns such as date, area, and value are used for partitioning. The time column is most commonly used.
Queries to the table have obvious range characteristics.
If the queried data fall into specific ranges, its better tables are partitioned so that through partition pruning, only the queried partition needs to be scanned, improving data scanning efficiency and reducing the I/O overhead of data scanning.
The table contains a large amount of data.
Scanning small tables does not take much time, therefore the performance benefits of partitioning are not significant. Therefore, you are advised to partition only large tables. In column-store table, each column is an independent file source, and the minimum storage unit CU can store 60,000 rows of data. Therefore, for column-store partitioned tables, it is recommended that the data volume in each partition be greater than or equal to the number of DNs multiplied by 60,000.

Creating a Range Partitioned Table

Example: Create a table web_returns_p1 partitioned by the range wr_returned_date_sk.

Create partitions in batches, with fixed partition ranges. The following example can be used:

Creating a List Partitioned Table

A list partitioned table can use any column that allows value comparison as the partition key column. When creating a list partitioned table, you must declare the value partition for each partition.

Example: Create a list partitioned table sales_info.

Partitioning an Existing Table

A table can be partitioned only when it is created. If you want to partition a table, you must create a partitioned table, load the data in the original table to the partitioned table, delete the original table, and rename the partitioned table as the name of the original table. You must also re-grant permissions on the table to users. For example:

Adding a Partition

Run the ALTER TABLE statement to add a partition to a partitioned table. For example, to add partition P2020 to the web_returns_p1 table, run the following command:

Splitting a Partition

The syntax for splitting a partition varies between a range partitioned table and a list partitioned table.

Run the ALTER TABLE statement to split a partition in a range partitioned table. For example, the partition pxxxx of the table web_returns_p1 is split into two partitions p2020 and p20xx at the splitting point 20201231.
Run the ALTER TABLE statement to split a partition in a list partitioned table. For example, split the partition province2_202201 of table sales_inf into two partitions province3_202201 and province4_202201.

Merging Partitions

Run the ALTER TABLE statement to merge two partitions in a partitioned table. For example, merge partitions p2016 and p2017 of table web_returns_p1 into one partition p20162017.

Deleting a Partition

Run the ALTER TABLE statement to delete a partition from a partitioned table. For example, run the following command to delete partition P2020 from the web_returns_p1 table: