Interval partitioning with automated cleanup
For large databases partitioning is not only great, it is necessary. Unfortunately it adds new problems. One such problem is how to create new partitions so that they are available when they are needed. It often requires the use of an overflow partition, moving data to the real partitions when they come online. It also tends to require priviliges not granted to developers.
With Oracle 11g this becomes much easier. With the new interval partitioning scheme the database automatically creates new partitions when they are needed. A template partition is created from the start; the system takes care of the rest.
For example, to create a new partition for each day:
create table test_tab ( created_date date default sysdate ,partition_key date generated always as (trunc(created_date)) virtual ,test_col_1 number ) partition by range (partition_key) interval (NUMTODSINTERVAL(1,'DAY')) ( partition test_tab_initial_part values less than (to_date('20100121', 'YYYYMMDD') ) );
Note that a virtual column is used for the partitioning key; another 11g feature. Try it out:
insert into test_tab (test_col_1) values (1); commit; select partition_name, high_value from user_tab_partitions where table_name = 'TEST_TAB'; PARTITION_NAME HIGH_VALUE --------------------- ---------------------------- TEST_TAB_INITIAL_PART TO_DATE(' 2010-01-21 ... SYS_P42 TO_DATE(' 2010-10-29 ...
A new partition has been created on the fly.
While this is great it solves only half the problem. The old partitions must be dropped eventually. How can we automate that?
First we need to find the partitions that can be dropped. This is a bit tricky as the high_value column in user_tab_partitions is a LONG that contains a SQL expression rather than a value we can use in comparisons. Our first task is to write a function that converts a high_value to a date:
create or replace function get_high_value_as_date( p_table_name in varchar2, p_partition_name in varchar2 ) return date as v_high_value varchar2(1024); v_date date; begin select high_value into v_high_value from user_tab_partitions where table_name = upper(p_table_name) and partition_name = upper(p_partition_name); execute immediate 'select ' || v_high_value || ' from dual' into v_date; return v_date; end;
Armed with the conversion function we can loop over the partitions for our table and drop the obsolete ones:
declare c_days_to_keep constant integer := 31; x_last_partition exception; pragma exception_init(x_last_partition, -14758); begin for rec in (select table_name, partition_name from user_tab_partitions where table_name = 'TEST_TAB' and get_high_value_as_date(table_name, partition_name) < sysdate - c_days_to_keep) loop begin execute immediate 'alter table ' || rec.table_name || ' drop partition ' || rec.partition_name; exception when x_last_partition then null; end; end loop; end;
Note that we cannot drop the template partition.