This topic describes how to design partitioned table schemas in OceanBase Database.
Considerations when creating a partitioned table
If a large amount of data is to be accessed frequently, you can create a partitioned table.
Partitions must be specified when you create a table. You cannot convert a non-partitioned table into a partitioned one online. You also cannot modify the number of partitions, the partition type, or the partitioning key of a partitioned table online.
Use table groups to improve the efficiency of joining multiple tables.
If multiple tables are associated by using their partitioning keys and the partitioning keys of these tables are partitioned by using the same strategy, such as HASH partitioning, and the number of partitions is equal, you can put these tables into one table group to improve the efficiency of joining the tables.
Considerations when you add partitioning constraints to a table
When you create a partitioned table, make sure that each field corresponding to the primary key or unique key of the table contains the partitioning key or a field included in the partitioning key.
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.
Recommendations on partitioning strategies based on the actual use and application scenarios of a table.
Actual use: historical tables, transaction stream tables
Application scenarios: tables with obvious access hotspots
Recommendations on how to select a partitioning key and strategy when you create a partitioned table. For more information, see Create and manage partitioned tables.
HASH partitioning: Choose a field with high cardinality and a high frequency of occurrence in the query conditions as the partitioning key for HASH partitioning.
RANGE and LIST partitioning: Select a field that is suitable for business rules as the partitioning key, but avoid using a field that has a small number of partitions. Example: For a large log table partitioned by using RANGE partitioning, you can use a time-related field as the partitioning key.
Limitations on partitions.
In a HASH-partitioned table, range queries on the partitioning key are not supported.