Updated on 2025-09-18 GMT+08:00

Defining Table Partitions

A partitioned table is a logical table that is divided into several physical block libraries for storage based on a partitioning policy. The logical table is called a partitioned table, and the physical block is called a partition. A partitioned table is a logical table that does not store data. Data is stored on partitions. During conditional queries, the system scans only the partitions that meet the conditions, avoiding full table scanning and 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.

Choosing to Partition a Table

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

  • The data contains fields with distinct enumerated types.

    A table is partitioned based on fields with distinct enumerated types. Generally, columns such as date, area, and value are used for partitioning. The time column is most commonly used.

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

Partitioning an Existing Table

A table can be declared as a partitioned table only when it is created. For example:

1
2
CREATE TABLE my_tbl (x int) PARTITION BY (b int) STORE AS ORC;
INSERT INTO my_tbl VALUES (1,10);

Deleting a Partition

Run the ALTER TABLE statement to delete a partition from a partitioned table. For example, delete a partition from the my_tbl table.
1
ALTER TABLE my_tbl DROP PARTITIONS (b = 10);

Querying a Partition

  • Query the partition where b equals 10.
    1
    SELECT * FROM my_tbl  where b = 10;
    
  • View the information of the partitioned table.
    1
    2
    SHOW PARTITIONS my_tbl;
    DESCRIBE my_tbl;
    

Deleting a Partitioned Table

Run the DROP TABLE statement to delete a partitioned table.

1
DROP TABLE web_returns_p1;