Tuning the Table Partitioning

Purpose

Table partitioning is a process of dividing a database table into multiple smaller and easy-to-manage parts based on partition keys. Logically, there is only one table, but at the underlying layer the table consists of multiple physical partitions.

Advantages of table partitioning:

1. Some queries can be greatly optimized. When a WHERE clause contains a partition key condition, the database can access the required partition table data, which reduces the amount of data that needs to be accessed and improves the query performance. Partitions can be modified after a partition table is created. Therefore, when the partition scheme is configured for the first time, data can be reorganized to improve the efficiency of common queries.
2. Queries involving aggregate functions such as SUM() and COUNT() can easily be processed in parallel. In this way, the query can be performed on each partition at the same time, and the final result only needs to be obtained by summing up all partitions.
3. Data queries are dispersed across drives to achieve greater query throughput.

Procedure

Tables can be partitioned by range, list, hash, or key. You are advised to select an appropriate method for partitioning tables based on the actual service model. For example, in the BenchmarkSQL TPC-C test model, perform hash partitioning for the ORDER_LINE table. The number of partitions depends on the number of warehouses. You are advised to create a partition for every 100 warehouses.