Method for Attaching Partition ONLINE to Range Partitioned Table

摘要

Method for attaching one or more tables online to a range partitioned table, includes: starting a child transaction; obtaining an exclusive lock on a source table; obtaining an intent exclusive lock on a target table; soft invalidating queries on the target table where compiled form of queries are cached for reuse; attaching the source table(s) to the target table as a new partition(s) of the target table; modifying indices and table structure of the target table to reflect the attaching of the new partition; committing the child transaction; and releasing the exclusive lock and the intent exclusive lock, where concurrently running queries over the target table continue to run without considering the partition until the intent exclusive lock and the exclusive lock are released. Queries on the target table only consider existing partitions when the attaching is in progress. The new partition is available once the attach operation is complete.