This topic describes how to design partitioned table schemas 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 as one of its components.
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 partitioning strategies based on the actual purposes and application scenarios of the tables.
Actual purposes: history tables and transaction stream tables.
Application scenarios: tables with obvious hot spots.
Considerations when you select a partitioning key for a partitioned table.
To use hash partitioning, select a field with high cardinality and a high likelihood of being included in the query conditions as the partitioning key.
To use range partitioning or list partitioning, select a field that has business significance and a sufficient number of partitions. Example: For a large log table, you can use a time-related field for range partitioning.
Limitations on partitions.
In a hash-partitioned table, range queries on the partitioning field are not supported.