Dropping interval partitions - Revisited

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