One of my favorite new features in Oracle 11g is interval partitioning. The reason that it’s one of my favorites is because it solves a long-standing issue. Previously an Oracle error occurred if you didn’t have a partition range specified that matched the value to be inserted. This new feature extends the functionality of range partitioning so you can define equal-sized (future) partitions using an interval definition.
When using interval partitioning, Oracle automatically creates new partitions as they are needed. Oracle creates the new partition at the time of the first record insert for the new partition. This greatly helps with the manageability of partitioned tables by saving the DBA from having to create new partitions manually, while also preventing Oracle user errors. The valid combinations for the new interval partitioning are Interval, Interval-List, Interval-Hash, and Interval-Range. Interval partitioning will work especially well for range partitioning, where partitioning was done based on date ranges.
Consider this example that shows this new functionality when creating a table.
A related feature is deferred segment creation for partitions (introduced in 11.2.0.2). With this feature, on-disk segments are not created for a subpartition and its dependent objects until the first row is inserted.
Leave a Reply