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