Oracle Partitioning: Range Interval Partitioning

Oracle partitioning features in 11G brought with it a beautiful new twist to range partitioning — the ability to create partitions on-the-fly.

Hello range interval partitioning.

Before Oracle 11G, DBAs would have to define all the partitions on a partitioned table and ensure there were no "cracks" between the partitions. If an INSERT statement tried to add data that did not fall into any pre-created partition, this error would be thrown:

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

With interval partitioning, DBAs only need to create the first partition (call it the “anchor” partition) on a DATE/NUMBER column and specify the interval: the later partitions are created automatically based on the interval.

For example, when you define a NUMBER range partition with interval 100 and first partition = values < 0, on-the-fly partitions get created  as (0 — 99) (100 — 199) (200 — 299) etc whenever the inserted data falls into that bucket.

Let’s see a working example with a DATE range interval partition.

Case Study: Using Interval Partitioning for a History Table

The scenario:

An application runs data quality checks on customer data every month-end. The requirement is to retain the history of previous runs for the latest three months only and purge the older data. Column RUN_DATE in the table identifies the date on which the application was run.

Each month’s run inserts a couple of million rows into the table.

The solution:

1. Create table with range interval partition on RUN_DATE, with INTERVAL of a month.
2. After each month’s application run, run a script to drop the partition that is beyond the retention limit (3 months in this case).

Step Detail (1) Create table with range interval partition on RUN_DATE

create table audit_hist
 (
   run_date             date
 , custid               number(4)
 , name_populated       varchar2(1)
 , name_valid           varchar2(1)
 , dob_populated        varchar2(1)
 , dob_valid            varchar2(1)
)
partition by range (run_date)
interval (numtoyminterval(1, 'MONTH'))
(partition P_OLDDATA 
    values less than 
   (to_date('01-JAN-2016','DD-MON-YYYY'))
);

The statement creates a table with:

• anchor partition of < 01-Jan-2016: This means that any data older than 01-Jan-2016 will go into partition P_OLDDATA.
• interval of 1 month: This means that any data dated 01-Jan-2016 or later will go into its own month’s partition. If such a partition does not exist already, Oracle will create it automatically.

When you describe the interval partitioned AUDIT_HIST table, it looks like any other (non-partitioned) table:

SQL> desc audit_hist
 Name                    Null?    Type
 ----------------------- -------- -----------
 RUN_DATE                         DATE
 CUSTID                           NUMBER(4)
 NAME_POPULATED                   VARCHAR2(1)
 NAME_VALID                       VARCHAR2(1)
 DOB_POPULATED                    VARCHAR2(1)
 DOB_VALID                        VARCHAR2(1)
 

Before any data is added to the table, it has only the anchor partition:

select partition_name
     , high_value
     , num_rows
from user_tab_partitions
where table_name = 'AUDIT_HIST'
order by partition_position desc;

Let’s see what happens when data is added with the following distribution of RUN_DATE:

Gather stats and query USER_TAB_PARTITIONS. Sure enough, the partition row count distribution matches the contents of the table.

A partition’s HIGH_VALUE is its non-inclusive upper boundary. The lower boundary is defined by the HIGH_VALUE of the previous partition.

Step Detail (2) After each month’s application run, drop the partition beyond the retention limit

In this case, the retention limit is 3 months.

Before dropping partitions, check which intervals are old enough to be dropped:

-- Script to list the intervals to be dropped
declare
   l_date date;
begin
   for part in (select partition_name
                  , high_value
            from user_tab_partitions
           where table_name = 'AUDIT_HIST'
		   and interval = 'YES')
   loop
     execute immediate 
       'begin :part := ' || part.high_value || '; end;' 
        using OUT l_date;
	      if months_between(sysdate
		       , l_date) > 3 then		
     dbms_output.put_line( to_char( l_date, 'dd-mon-yyyy' ) );
		  end if;     
   end loop;
end;

When run:

The script points to the partition with HIGH_VALUE 01-dec-2016 — that is, the one which contains Nov’16 data.

Now for the script to drop the partition:

-- Script to drop intervals older than 3 months
begin
   for part in (select partition_name
                  , high_value
            from user_tab_partitions
           where table_name = 'AUDIT_HIST'
             and interval = 'YES')
   loop
     execute immediate 
       'begin 
	      if months_between(sysdate
		     , '||part.high_value||') > 3 then
		    execute immediate
              ''alter table audit_hist drop partition ' 
            || part.partition_name
			||''';
		  end if;
		 end;';
   end loop;
end;

When run:

Let us check what USER_TAB_PARTITIONS says now:

The result no more shows the partition with HIGH_VALUE 01-dec-2016 (i.e. containing Nov’16 RUN_DATE).

The content of the table after dropping the partition:

All content dated Nov’16 has been wiped out with the dropping of its partition. Data distribution by RUN_DATE after this change:

Stored procedure to drop old partitions: parameterized table name and threshold

As an extension to the anonymous PL/SQL for dropping old partitions, here’s a nifty stored procedure for you. This can be invoked to drop partitions older than a given threshold (in months), from any interval partitioned table provided as input.

-- Name: drop_old_partitions
-- Description: Procedure to drop old partitions 
--              from an interval partitioned table
-- Parameters: 
-- (1) table name 
-- (2) months beyond which the old interval is to be dropped
-- e.g. if input value is 3, it will drop partitions 
-- that satisfy this condition:
-- months_between(sysdate, partition's upper limit) > 3

   create or replace procedure drop_old_partitions 
                            (p_table_name IN VARCHAR2
                           , p_threshold IN NUMBER)
   is
   
     cursor tab_interval IS
      select partition_name
                  , high_value
            from user_tab_partitions
           where table_name = p_table_name
             and interval = 'YES';
	
   begin
   for part in tab_interval
   loop
     execute immediate 
       'begin 
	      if months_between(sysdate
		     , '||part.high_value||') > ' 
		        || p_threshold || 'then
		    execute immediate
              ''alter table '|| p_table_name ||' drop partition ' 
            || part.partition_name
			||''';
		  end if;
		 end;';
   end loop;
   end;

To run:

exec drop_old_partitions ('AUDIT_HIST', 3);

Note: Anchor partition cannot be dropped!

You might have spotted that the script included a filter for
interval = ‘YES’.

That filter excludes the anchor from the partition selection criteria.

What happens when you do not put that filter?

An error:

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

In this case study, this does not pose a problem as the RUN_DATE can only move forward. The anchor partition — a much older date – is going to remain empty and it’s all right even if it remains intact.

Summary

This article presents a scenario in which the power of range interval partitioning can be put to good use. It provided scripts for querying partition details and dropping old partitions beyond a retention limit.

For Further Reading

• When to use Range of Interval Partitioning
• How to Convert a Non-Partitioned Table to Partitioned [pre 12.2]