This topic describes how to select a partitioning type.

When you create a partitioned table, you can specify LIST or RANGE partitioning rules. The partitioning rules provide a set of constraints that define the data stored in each partition. When new rows are added to the partitioned table, the server uses the partitioning rules to determine which partition will contain each row.

PolarDB for Oracle can also use partitioning rules to enable partition pruning, improving performance when responding to user queries. When selecting a partitioning type and partition keys for a table, you need to consider how the data that is stored in the table will be queried, and include frequently queried columns in the partitioning rules.

Partitioning types

  • List partitioning

    When creating a list-partitioned table, you must specify a single partition key column. When you add a new row to the table, the server compares the key values specified in the partitioning rule to the corresponding column within the row. If the column value matches a value in the partitioning rule, the row is stored in the partition named in the rule.

  • Range partitioning

    When creating a range-partitioned table, you must specify one or more partition key columns. When you add a new row to the table, the server compares the value of the partition key column (or columns) to the corresponding column (or columns) in the table entry. If the column values satisfy the conditions specified in the partitioning rule, the row is stored in the partition named in the rule.

  • Subpartitioning

    Subpartitioning breaks a partitioned table into smaller subsets that can be stored on the same server. A table is typically subpartitioned by a different set of columns, and can be of a different subpartitioning type other than that of the parent partition. If one partition is subpartitioned, then each partition must include a minimum of at least one subpartition.

    If a table is subpartitioned, no data will be stored in any of the partitions. The data will be instead stored in the corresponding subpartitions.