Automatic list partitioning v16

Suggest edits

Automatic list partitioning is an extension to LIST partitioning that allows a database to create a partition for any new distinct value of the list partitioning key. A new partition is created when data is inserted into the LIST partitioned table and the inserted value doesn't match any of the existing table partition. Use the AUTOMATIC clause to implement automatic list partitioning.

For example, consider a table named sales with a sales_state column that contains the existing partition values CALIFORNIA and FLORIDA. Each of the sales_state values increases with a rise in the statewise sales. A sale in a new state, for example, INDIANA and OHIO, requires creating new partitions. If you implement automatic list partitioning, the new partitions INDIANA and OHIO are automatically created, and data is entered into the sales table.

For information about automatic list partitioning syntax, see CREATE TABLE...PARTITION BY.

Restrictions for automatic list partitioning

The following restrictions apply to the AUTOMATIC clause:

  • A table that enables automatic list partitioning can't have a DEFAULT partition.
  • Automatic list partitioning doesn't support multi-column list partitioning.
  • In composite partitioning, the automatic list partitioning can be useful as a primary partitioning mechanism but isn't supported at the subpartition level.
← Prev

Interval range partitioning

Next →

Using partition pruning