While at the Trivadis Performance Day, I was asked how indexes were dealt with when Automatic Partitioning partitions a table, so I promised to write a blog post to explain it.

Automatic partitioning partitions a table using the following command:

ALTER TABLE table MODIFY PARTITION BY partition_type ... ONLINE PARALLEL;

As a consequence of this, auto patitioning manages indexes according to the default behavior of ALTER TABLE MODIFY (see “UPDATE INDEXES” in the modify_to_partitioned section).

The full details are in the documentation, but here’s a summary at the time of writing:

  • Nonprefixed indexes are converted to nonpartitioned global indexes.
  • Prefixed indexes are converted to local partitioned indexes.
  • Bitmap indexes are converted to local partitioned indexes, regardless of whether they are prefixed or not.