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:
- Create table with range interval partition on RUN_DATE, with INTERVAL of a month.
- 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:
Nov 2016 | 2 rows |
Dec 2016 | 1 row |
Jan 2017 | 2 rows |
Feb 2017 | 1 row |
Mar 2017 | 3 rows |
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:
Dec 2016 | 1 row |
Jan 2017 | 2 rows |
Feb 2017 | 1 row |
Mar 2017 | 3 rows |
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]
{ 2 comments… read them below or add one }
Beautifully explained. I read many guides online on partitioning without getting it. I found your explanation and it is clear now. Many thanks.
Great