The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 248 other followers

Dropping interval partitions

Posted by Harald van Breederode on January 11, 2010

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.

SQL> create table ipart
  2  (time_id date
  3  ,cust_id number(4)
  4  ,amount_sold number(5))
  5  partition by range(time_id)
  6  interval(NUMTOYMINTERVAL(1,'month'))
  7  (partition m01cy2010 values less than (to_date('01-feb-2010','DD-MON-YYYY'))
  8  ,partition m02cy2010 values less than (to_date('01-mar-2010','DD-MON-YYYY'))
  9  ,partition m03cy2010 values less than (to_date('01-apr-2010','DD-MON-YYYY')));

Table created.

SQL> select partition_name, interval, high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
M01CY2010      NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
M02CY2010      NO  TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
M03CY2010      NO  TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

Note: The high_value column is truncated for formatting reasons.

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.

SQL> insert into ipart values('01-apr-2010',1,2);

1 row created.

SQL> insert into ipart values('11-jun-2010',1,2);

1 row created.

SQL> select partition_name, interval, high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
M01CY2010      NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
M02CY2010      NO  TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
M03CY2010      NO  TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P193       YES TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P194       YES TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N 

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:

SQL> lock table ipart partition
  2  for (to_date('01-jul-2010','DD-MON-YYYy')) in share mode;

Table(s) Locked.

By querying the data dictionary we can verify that the desired partition was indeed created by the above SQL statement:

SQL> select partition_name, interval, high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
M01CY2010      NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
M02CY2010      NO  TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
M03CY2010      NO  TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P193       YES TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P194       YES TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P195       YES TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

Now that we have a couple of range and interval partitions we can start dropping them


SQL> alter table ipart drop partition m01cy2010;

Table altered.

SQL> alter table ipart drop partition
  2  for (to_date('01-feb-2010','DD-MON-YYYy'));

Table altered.

SQL> alter table ipart drop partition
  2  for (to_date('01-mar-2010','DD-MON-YYYy'));
alter table ipart drop partition
*
ERROR at line 1:
ORA-14758: Last partition in the range section cannot be dropped

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:

SQL> select partition_name, interval, high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
M03CY2010      NO  TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P193       YES TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P194       YES TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P195       YES TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

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.

SQL> alter table ipart truncate partition
  2  for (to_date('01-mar-2010','DD-MON-YYYy'));

Table truncated.

SQL> alter table ipart merge partitions
  2  for (to_date('01-mar-2010','DD-MON-YYYy')),
  3  for (to_date('01-apr-2010','DD-MON-YYYy'));

Table altered.

SQL> select partition_name, interval, high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
SYS_P196       NO  TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P194       YES TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P195       YES TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

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:

SQL> alter table ipart set interval ();

Table altered.

SQL> alter table ipart set interval (NUMTOYMINTERVAL(1,'month'));

Table altered.

SQL> select partition_name, interval, high_value
  2  from dba_tab_partitions where table_name = 'IPART'
  3  order by partition_position;

PARTITION_NAME INT HIGH_VALUE
-------------- --- ------------------------------------------------------------
SYS_P196       NO  TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P194       NO  TO_DATE(' 2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P195       NO  TO_DATE(' 2010-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

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

About these ads

8 Responses to “Dropping interval partitions”

  1. […] 11-How to drop partitions from interval partitioned table? Harald van Breederode-dropping inverval partitions […]

  2. binaya said

    I cannot see the interval column in user_tab_partitions view or dba_tab_partitions. Is there any other view I can use to know the transition point in oracle 11g database

  3. You may want to consider additionally the MINVALUE option to handle the boundary problem while dropping partitions – see http://www.db-nemec.com/rolling_window_interval_part.html

    Jaromir D.B. Nemec

  4. Florin M said

    The interval column in user(dba)_tab_partitions is new in 11.2.
    Florin

  5. Jason Bucata said

    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.

    • Harald van Breederode said

      Hi Jason,

      Did you read my Dropping Interval Partitions Revisited posting?

      If you performe a dummy DDL on the interval partitioned table, all interval partitions are converted into range partitions which can be dropped.
      -Harald

      • Jason Bucata said

        Ah… no, I didn’t see that one. I just stumbled upon this one, didn’t know there was a followup.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 248 other followers

%d bloggers like this: