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 247 other followers

Dropping interval partitions – Revisited

Posted by Harald van Breederode on July 26, 2010

In a previous posting about dropping interval partitions I explained how the transition point in an interval partitioned table can be moved up to be able to drop partitions. I explained that by temporarily converting the table from interval to range partitioning, all interval partitions are converted into range partitions.

Last week I discovered, suggested by Heijne Makkreel, that this conversion is unnecessary and that just setting the interval granularity to what it is already set to, all interval partitions are converted to range partitions.

The query below shows the partitions of an interval partitioned table (the same table as used in my original posting) with one range partition and four adjacent interval partitions. The partitions are 1 month in size.

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_P359       NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P360       YES TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P361       YES TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P362       YES TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P363       YES TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

SQL> select interval from dba_part_tables where table_name = 'IPART';

INTERVAL
--------------------------------------------------------------------------------
NUMTOYMINTERVAL(1,'MONTH')

By issuing an ALTER TABLE command that sets the interval to what it is already set to, all interval partitions are converted into range partitions as demonstrated below:

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_P359       NO  TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P360       NO  TO_DATE(' 2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P361       NO  TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P362       NO  TO_DATE(' 2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N
SYS_P363       NO  TO_DATE(' 2010-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'N

The query output above shows that all partitions are now of type range, and we can simply drop them if we wish to do so.
-Harald

About these ads

3 Responses to “Dropping interval partitions – Revisited”

  1. Greg said

    Can we revert that by rerunning alter table ?
    Regards.
    Greg

    • Harald van Breederode said

      Hi Greg,

      No, once the interval partitions are converted into range partitions you cannot alter them back again into interval partitions. Why would you want to do that anyway?
      -Harald

      • Greg said

        Not sure, just wondering if I can control entropy :).
        Thanks for Your answer.
        Great blog.
        Regards.
        Greg

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 247 other followers

%d bloggers like this: