This topic describes the recommended structure design for partitioned tables in OceanBase Database.
Take note of the following points 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.
When you create a table, you can specify whether to create a partitioned table. Once created, a non-partitioned table cannot be converted into a partitioned table. In addition, the number of partitions, partition type, and partitioning key values cannot be modified after the table is created.
Use table groups.
If multiple tables are related to each other by their partitioning keys, the partitioning strategies of the partitioning keys are consistent, for example, are hash partitioning, and their numbers of partitions are the same, you can use the table group technology to put multiple tables into one table group to improve the association efficiency.
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 tables.
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.
KEY partitioning: When you define the number of partitions, you must select a prime number of partitions to achieve an even distribution of data.
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.