This topic describes the recommended structure design for partitioned tables in OceanBase Database.
Observe the following notes when you create a partitioned table:
If the table contains a large amount of data and the accessed data is centralized, you can create a partitioned table.
Observe the following notes for constraints on partitioned tables:
When you create a partitioned table, ensure that at least one field in each primary key and unique key must be a partitioning key field of the table.
We recommend that you use a primary key wherever possible to ensure global uniqueness in a partitioned table.
A unique index on a partitioned table must include a partitioning key of the table.
We recommend that you design the partitioning strategy based on your practical use and scenarios.
Practical use: history table and flow table
Scenarios: tables with obvious access hotspots
To use partitioned tables, you need to use appropriate partitioning keys and partitioning strategies. Only recommendations on partitioning type selection are provided here. For more information, see Create and manage partitioned table.
HASH partitioning: Select a field with a high degree of distinction and the highest frequency of occurrence in the query condition as the partitioning key for hash partitioning.
RANGE and LIST partitioning: Select an appropriate field as the partitioning key based on business rules. Ensure that the number of partitions is not too small. For example, for large log tables, you can use a time type column as a partitioning key for RANGE partitioning.
RANGE partitioning: MAXVALUE cannot be specified for the last column.
Restrictions on partitions:
Range queries based on partitioning keys are not suitable in hash-partitioning scenarios.