Design partitioned table schemas

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 of the table includes at least one field that is the partitioning key of the table.

    • We recommend that you use the primary key to ensure global uniqueness in a partitioned table.

    • A unique index of a partitioned table must include the partitioning key of the table.

• Recommendations on partitioning strategies

  • Consider the actual use and scenarios of the table when you design a partitioning strategy.

  • Actual use: historical tables, transaction stream tables

• Scenarios: tables with obvious hot spots

  • Recommendations on partitioning keys

  • When you create a partitioned table, choose an appropriate partitioning key and partitioning strategy. This topic provides only basic recommendations. For more information, see Create and manage partitioned tables.

• HASH partitioning: We recommend that you use a field with high cardinality and that frequently appears in the query condition as the partitioning key for HASH partitioning.

  • RANGE and LIST partitioning: We recommend that you choose an appropriate field as the partitioning key based on business rules. However, the number of partitions must not be too small. For example, if you want to partition a large log table by time, you can use RANGE partitioning.