Dropping interval partitions

One of the nice Oracle11g new features is interval partitioning which is an extension to range partitioning. The advantage of interval partitioning over range partitioning is that new partitions are created automatically when new rows are inserted which don’t belong in an existing partition.

The question, however, is how to get rid of old partitions? This isn’t as straightforward as one might expect. I struggled with this myself and after receiving questions from friends I decided that is was time to do some research and that is what this posting is all about.

Let’s get started by creating a one month interval partitioned table with three predefined range partitions and continue by looking at some metadata in the data dictionary.

The above output shows the three partitions we’ve just created and the interval=no column indicates that they are range partitions and not interval partitions as one might expect. Important to remember is that interval partitioning is an extension to range partitioning. The next step is to insert a few rows which don’t belong in the existing partitions to trigger the creation of new partitions.

The above output shows that two new partitions were created and that they are of type interval as indicated by the interval=yes column. Oracle assigned names to them because the partitions were created automatically.

The boundary between the highest range partition and the lowest interval partition is known as the transition point. Partitions above the transition point are created by Oracle automatically whenever needed, and we cannot add partitions manually using the “alter table add partition” command. However, in order to support partition exchange, a workaround is available using the “lock partition” command using the new “partition for ()” clause. To add the partition for July 2010 we use:

The first two drop partition SQL statements ran successfully but the third one returned an ORA- 14758 error indicating that we cannot drop the last partition in the range section of an interval partitioned table. The partition in question is the last remaining one under the transition point as shown below:

The only way to get rid of this partition is to move up the transition point. This can be achieved by merging the two partitions around the transition point into a new partition. However merging two partitions results in copying all rows from both partitions into a new partition possibly resulting in generating a large amount of redo. Since the intention is to drop the oldest partition we can safely truncate the partition before merging it, thus eliminating half the number of rows that have to be copied assuming both partitions have an equal number of rows to begin with.

The output above shows that the transition point has been moved up as a result of merging two partitions after the first partition was truncated. But despite the fact that one of the partitions to be merged is empty, Oracle still copies all rows into a new partition! Thus this way of dropping partitions from an interval partitioned table is quite expensive. If possible one should truncate both partitions before merging them together to eliminate the rows from being copied.

Another way to move up the transition point is to temporarily convert the interval partitioned table into a regular range partitioned table. This results in all adjacent interval partition being converted into range partitions as demonstrated below:

The query output shows that all partitions are now of type range because the partition type was temporarily altered from interval partitioning into range partitioning. This is an effective way to move the transition point up to the maximum, but it requires a downtime slot because in the short moment the table is range partitioned no new partition will be added if someone tries to insert a row that doesn’t belong in the existing partitions.

In conclusion, dropping partitions from an interval partitioned table isn’t as straightforward as one might expect and might require the transition point to be moved up. Moving the transition point up can be achieved by either merging partitions together or by temporarily converting a interval range partitioned table into a range partitioned table.-Harald

Like this:

LikeLoading...

Related

This entry was posted on January 11, 2010 at 09:02 and is filed under Oracle.
You can follow any responses to this entry through the RSS 2.0 feed.
You can leave a response, or trackback from your own site.

Jason Bucatasaid

If you can’t truncate both partitions before the merge, what about exchanging out the higher of the two (in the interval section) with an empty copy, merging them, then exchanging back? It still maintains global indexes (if you tell it to) when it otherwise shouldn’t really be necessary, but it at least saves the further overhead of the extra row copy.