This topic describes how to design a partitioned table in OceanBase Database.
Considerations when you create a partitioned table.
If a large amount of data is to be accessed frequently, you can create a partitioned table.
Constraints on partitioned tables.
When you create a partitioned table, make sure that each primary key or unique key on the table includes a partitioning key field.
We recommend that you use the primary key to ensure global uniqueness in a partitioned table.
A unique index on a partitioned table must include the partitioning key.
We recommend that you design a partitioning strategy based on the actual use and scenarios of the table.
Actual use: historical tables, transaction flow tables.
Scenarios: tables with obvious hotspots.
Considerations when you select a partitioning key for a partitioned table.
To apply the HASH partitioning method, choose a partitioning key based on a field that has high discriminative power and frequently appears in the query conditions.
To apply the RANGE or LIST partitioning method, choose a business field as the partitioning key, and make sure that the number of partitions is not too small. For example, you can apply the RANGE partitioning method to a large log table by using a time field as the partitioning key.
Limitations on partitions.
In a HASH-partitioned table, range queries based on the partitioning key are not supported.