Rolling Window Using Interval Partitioning

 

Jaromir D.B. Nemec

 

There are two caveats while using interval partitioning to implement rolling window:

 

ORA-14759: SET INTERVAL is not legal on this table.  and

ORA-14758: Last partition in the range section cannot be dropped

 

Both of those problems correspond to the left and right ends of the partitioned table. Let’s investigate those cases on an example.

 

SQL> CREATE TABLE int_part

  2    (

  3   transaction_date DATE not null,

  4   vc_pad VARCHAR2(100)

  5    )

  6   PARTITION BY RANGE (transaction_date)

  7    (

  8   PARTITION P_01  VALUES LESS THAN (TO_DATE('2011-12-02', 'YYYY-MM-DD') ),

  9   PARTITION P_02  VALUES LESS THAN (TO_DATE('2011-12-03', 'YYYY-MM-DD') ),

 10   PARTITION P_MAX VALUES LESS THAN (MAXVALUE)

 11  )

 12  ;

 

Table created.

 

We use a range partitioned table and change it to interval partitioning – this is probable a typical way to introduce interval partitioning for tables created before 11g release.

 

SQL> alter table int_part set INTERVAL (NUMTODSINTERVAL(1,'DAY'));

alter table int_part set INTERVAL (NUMTODSINTERVAL(1,'DAY'))

*

ERROR at line 1:

ORA-14759: SET INTERVAL is not legal on this table.

 

What’s the problem here – are there some features used in the range partitioned table that prevent the interval partitioning?

No, the problem is very simple, the table has a MAXVALUE partition – this of course prevent the start of the interval partitioning. Interval partitioned table must have an open end.

After dropping the MAXVALUE partition (or modifying to regular partition if it contains data) everything works fine.

 

SQL> alter table int_part drop partition p_max;

 

Table altered.

 

SQL> --

SQL> alter table int_part set INTERVAL (NUMTODSINTERVAL(1,'DAY'));

 

Table altered.

 

Ok, the table is interval partitioned; we can insert new data and the partitions will be allocated as required.

 

 

SQL> insert into int_part values (TO_DATE('2011-12-03', 'YYYY-MM-DD'), 'xx');

 

1 row created.

 

SQL> insert into int_part values (TO_DATE('2011-12-04', 'YYYY-MM-DD'), 'xx');

 

1 row created.

 

SQL>

SQL> select partition_name, interval, high_value from

  2  all_tab_partitions where table_owner = 'IBP' and table_name = 'INT_PART'

  3  order by partition_position

  4  ;

 

PARTITION_NAME                 INT HIGH_VALUE                                  

------------------------------ --- ----------------------------------------    

P_01                           NO  TO_DATE(' 2011-12-02 00:00:00', 'SYYYY-M    

P_02                           NO  TO_DATE(' 2011-12-03 00:00:00', 'SYYYY-M    

SYS_P385                       YES TO_DATE(' 2011-12-04 00:00:00', 'SYYYY-M    

SYS_P386                       YES TO_DATE(' 2011-12-05 00:00:00', 'SYYYY-M    

 

The new allocated partitions have the system generated names and can be identified on the YES value of the column interval. So the right end of the rolling window is fine.

To make the window rolling we need to remove the old partitions.

So lets start to drop partitions.  We use the partition extended name (PARTITION FOR) to identified the partition.

 

SQL> alter table int_part drop partition for (TO_DATE('2011-12-01', 'YYYY-MM-DD'));

 

Table altered.

 

Removing the oldest partition is no problem.

 

SQL> alter table int_part drop partition for (TO_DATE('2011-12-02', 'YYYY-MM-DD'));

alter table int_part drop partition for (TO_DATE('2011-12-02', 'YYYY-MM-DD'))

*

ERROR at line 1:

ORA-14758: Last partition in the range section cannot be dropped

 

The attempt to remove the second partition fails, why? This is the last partition of the range partitioned part of the table (interval = ‘NO’) – this partition can’t be removed. Two possible tricks how to handle the last range partitioned partition see [1]. Both of them are not preferable, as they require either move of data or an inaccessibility maintenance window.

Nevertheless there is a very simple third solution – inspired with our previous problem with MAXVALUE.  We simple let the partition exists – without data and named MINVALUE.

 

SQL> alter table int_part truncate partition for (TO_DATE('2011-12-02', 'YYYY-MM-DD'));

 

Table truncated.

 

SQL> alter table int_part rename partition p_02 to p_minvalue;

 

Table altered.

 

 

PARTITION_NAME                 INT HIGH_VALUE                                  

------------------------------ --- ----------------------------------------    

P_MINVALUE                     NO  TO_DATE(' 2011-12-03 00:00:00', 'SYYYY-M    

SYS_P392                       YES TO_DATE(' 2011-12-04 00:00:00', 'SYYYY-M    

SYS_P393                       YES TO_DATE(' 2011-12-05 00:00:00', 'SYYYY-M  

 

We truncated the partition and renamed it to P_MINVALUE. This partition will exists forever – which guaranties the existence of the required boundary between the range and interval part.

There is no limitation while removing the interval partitions after the boundary.

 

SQL> alter table int_part drop partition for (TO_DATE('2011-12-03', 'YYYY-MM-DD'));

 

Table altered.

 

So, finally we get an increasing gap between the MINVAL partition and left end of the rolling window.

 

PARTITION_NAME                 INT HIGH_VALUE                                  

------------------------------ --- ----------------------------------------    

P_MINVALUE                     NO  TO_DATE(' 2011-12-03 00:00:00', 'SYYYY-M    

SYS_P386                       YES TO_DATE(' 2011-12-05 00:00:00', 'SYYYY-M    

SYS_P387                       YES TO_DATE(' 2011-12-06 00:00:00', 'SYYYY-M    

SYS_P388                       YES TO_DATE(' 2011-12-07 00:00:00', 'SYYYY-M    

SYS_P389                       YES TO_DATE(' 2011-12-08 00:00:00', 'SYYYY-M    

SYS_P390                       YES TO_DATE(' 2011-12-09 00:00:00', 'SYYYY-M    

SYS_P391                       YES TO_DATE(' 2011-12-10 00:00:00', 'SYYYY-M    

 

7 rows selected.

 

Two final remarks.

Contrary to some posting a repeated drop partition using the partition extended name causes no problem. It will be critical if the repeated drop  will remove the next adjacent partition. But this is not the case.

 

SQL> alter table int_part drop partition for (TO_DATE('2011-12-03', 'YYYY-MM-DD'));

alter table int_part drop partition for (TO_DATE('2011-12-03', 'YYYY-MM-DD'))

*

ERROR at line 1:

ORA-02149: Specified partition does not exist

 

The attempt to drop an interval partition that doesn’t exists fails.

 

The second point is that the left and right end of the rolling window are somehow asymmetrical. If we don’t receive any data for a particular day this is no problem – the partition will not be created and the table will contain a hole.

If we fail to schedule the daily dropping job the partition will remain and will occupy the space forever. This is not the expected behaviour.

It will be fine to have a possibility to remove all partition FOR a range of days, something like:

 

alter table int_part drop partition for ( between dateA and dateB );

 

Unfortunately this is not possible now, so the periodical dropping job must workaround it implementing a loop dropping all outdated partition until we get the “Specified partition does not exist” exception.   

 

[1]  http://prutser.wordpress.com/2010/01/11/dropping-interval-partitions/

 

Last revision 10.12.2011

Jaromir D.B. Nemec is a freelancer specializing in data warehouse and integration solutions. He can be reached at http://www.db-nemec.com