Investigating Oracle 11g Interval Partitioning
September 13th, 2008 by Mark Rittman
If you’ve kept abreast of what’s new in the latest release of the database, you will probably be aware of three major enhancements to partitioning in Oracle 11g. Firstly, there’s the new reference (ref) partitioning where you can partition one table based on a key value in another, so that for example you can partition the order details table based on the same key as the orders table. The second enhancement is partitioning on virtual columns, where you can define a column based on an expression and then partition the table based on it. The one I’m most interested in though is Interval Partitioning, the third enhancement, as it allows you to define a range partitioning scheme for a table that automatically creates partitions as they are needed.
If you haven’t used partitioning before you’d actually probably think that this is how it works at the moment; you define a range partitioning scheme so that, for example, each month’s data is placed in it’s own partition which you can then back up, make read-only and so on, and as you load data into the table the partitions are created for you on demand. It’s actually the case though that prior to 11g you needed to define partitions before you load data into them, leading to the situation where before each data load into a data warehouse, say, you had to run ALTER TABLE scripts to add the relevant partitions into your tables. It’s not a huge amount of extra work but it does complicate the load process, and Oracle have addresed this in the 11g release by releasing INTERVAL partitioning, which creates new partitions as they are required.
To take an example, this script creates a table with four regular range partition and then sets up interval partitioning for dates past the 1st January 2007, which is referred to as the “transition point”. Interval partitioned tables always need at least one range-based partition set up, with interval partitioning taking over beyond this transition point.
SQL> CREATE TABLE interval_sales
2 ( prod_id NUMBER(6)
3 , cust_id NUMBER
4 , time_id DATE
5 , channel_id CHAR(1)
6 , promo_id NUMBER(6)
7 , quantity_sold NUMBER(3)
8 , amount_sold NUMBER(10,2)
9 )
10 PARTITION BY RANGE (time_id)
11 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
12 ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2005', 'DD-MM-YYYY')),
13 PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')),
14 PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY')),
15 PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) );
Table created.
If you then take a look at USER_TAB_PARTITIONS, you can see the four regular range-based partitions that I’ve just set up.
SQL> Select partition_name from user_tab_partitions where table_name = 'INTERVAL_SALES'; PARTITION_NAME ------------------------------ P0 P1 P2 P3
Now if I try and add some data into this table beyond the transition point and then take a look at USER_TAB_PARTITIONS, you can see the new partition that’s been set up. Note that Oracle has only created one partition to cover the the entire period from the transition period to the date of the transaction, and that the partition name is system-generated.
SQL> insert into interval_sales values (1,2,to_date('1-jan-2009', 'dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
SQL> commit;
Commit complete.
SQL> Select partition_name, high_value from user_tab_partitions where table_name = 'INTERVAL_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P161 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
So what happens if we insert more data, but this time for a date in-between? The interval partitioning scheme specifies one partition per month, what’s going to happen?
SQL> insert into interval_sales values (1,2,to_date('1-jan-2008', 'dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
Elapsed: 00:00:00.01
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
SQL> Select partition_name, high_value from user_tab_partitions where table_name = 'INTERVAL_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P161 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P162 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
6 rows selected.
That’s interesting. It’s created a single month partition to cover the new date, and the existing partition then covers from that date through to the date of the transaction. What about if we add a bunch of other transactions in several months over several years?
SQL> insert into interval_sales values (1,2,to_date('14-mar-2008', 'dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
SQL> insert into interval_sales values (1,2,to_date('17-sep-2009', 'dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
SQL> insert into interval_sales values (1,2,to_date('10-jun-2012', 'dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
SQL> insert into interval_sales values (1,2,to_date('05-aug-2008', 'dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
SQL> insert into interval_sales values (1,2,to_date('05-dec-2008', 'dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
SQL> insert into interval_sales values (1,2,to_date('05-nov-2008','dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
SQL> insert into interval_sales values (1,2,to_date('05-oct-2008','dd-mon-rrrr'),'d',3,4,9.9);
Commit complete.
SQL> Select partition_name, high_value from user_tab_partitions where table_name = 'INTERVAL_SALES';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
P0 TO_DATE(' 2005-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2006-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P3 TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P161 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P162 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P163 TO_DATE(' 2008-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P164 TO_DATE(' 2009-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P165 TO_DATE(' 2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P166 TO_DATE(' 2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P167 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P168 TO_DATE(' 2008-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SYS_P169 TO_DATE(' 2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
13 rows selected.
Now it’s a bit difficult to work out the ranges of the interval partitions because they’re listed in the order they were created, but if you re-order the list it looks like this:
TO_DATE(' 2005-01-01 00:00:00',
TO_DATE(' 2006-01-01 00:00:00',
TO_DATE(' 2006-07-01 00:00:00',
TO_DATE(' 2007-01-01 00:00:00',
TO_DATE(' 2008-02-01 00:00:00',
TO_DATE(' 2008-04-01 00:00:00',
TO_DATE(' 2008-09-01 00:00:00',
TO_DATE(' 2008-11-01 00:00:00',
TO_DATE(' 2008-12-01 00:00:00',
TO_DATE(' 2009-02-01 00:00:00',
TO_DATE(' 2009-01-01 00:00:00',
TO_DATE(' 2009-10-01 00:00:00',
TO_DATE(' 2012-07-01 00:00:00',
So what’s happening here is that the first four partitions are our range-based ones and cover up to the end of 2006; after that comes the interval partitions and they are defined with their high value being the first day of the next month, as they are month-based interval partitions. As our data is sparse these interval partitions end of covering several months, as further data is then added these ranges will end up going down as interval partitions for other months are created.
So that answers the question as to what the system does when you end up inserting data way beyond the transition point, as I was wondering whether lots of individual month-based partitions were created. What actually happens, as you can see from the example above, is that partitions are created as needed based on the the HIGH_VALUE that’s relevant to the data being added.
The other question I had was around partition maintenance, and in particular partition exchange loading, where you load data into a staging table to the same design as an individual table partition, prepare your local indexes to go with it, then swap it with the (usually empty) partition in the main table using an ALTER TABLE .. EXCHANGE PARTITION command, like this:
ALTER TABLE sales_partitioned EXCHANGE PARTITION sales_200108 WITH TABLE sales_staging INCLUDING INDEXES WITHOUT VALIDATION;
But how does this work when you use interval partitioning, when the partition name is system-generated? How do you know the same of the partition to exchange? The answer to this is in another partitioning enhancement in Oracle 11g, where you can refer to a partition by the range that it covers, like this:
SQL> ALTER TABLE INTERVAL_SALES DROP PARTITION FOR (TO_DATE('3-3-2004','DD-MM-YYYY'));
Table altered.
So if I prepare staging table to match my partitioned table, like this:
SQL> CREATE TABLE interval_sales_staging 2 ( prod_id NUMBER(6) 3 , cust_id NUMBER 4 , time_id DATE 5 , channel_id CHAR(1) 6 , promo_id NUMBER(6) 7 , quantity_sold NUMBER(3) 8 , amount_sold NUMBER(10,2) 9 )
and the add some data to it…
SQL> insert into interval_sales_staging values (1,2,to_date('05-oct-2008', 'dd-mon-rrrr'),'d',3,4,9.9);
1 row created.
and then try out an exchange partition, using this new FOR clause, it works:
SQL> ALTER TABLE interval_sales
2 EXCHANGE PARTITION FOR (TO_DATE('05-10-2008','DD-MM-YYYY'))
3 WITH TABLE interval_sales_staging
4 INCLUDING INDEXES
5 WITHOUT VALIDATION;
Table altered.
Not bad. But what if the partition I want to add in to doesn’t exist? What if I insert some data into the staging table that is for just one month, and yet the partition that the FOR clause points to covers several months – will I lose all the data in the existing partition even though I’m probably expecting to exchange just a single month’s partition? And what about if the new data is for a date beyond the current range of the interval partitioning, and there isn’t therefore a partition to exchange with?
To start off with then I’m going to list out all the the data in the interval partitioned table, so we can see what’s currently there:
SQL> select time_id, quantity_sold from interval_sales order by time_id; TIME_ID QUANTITY_SOLD --------- ------------- 05-OCT-08 4 01-JAN-08 4 14-MAR-08 4 05-AUG-08 4 05-OCT-08 4 05-NOV-08 4 05-DEC-08 4 01-JAN-09 4 17-SEP-09 4 10-JUN-12 4 10 rows selected.
So say now we have some new data for the month of Jun 2008, maybe we missed this data out in our initial load and we want to put it into the data warehouse now. In the interval partitioned table, data for Jun 08 is in a partition that goes from the 1st April 2008 to the 1st September 2008 and has a row in it dated 5th August. What happens if we exchange our staging table for this partition – will we lose the data for August?
SQL> insert into interval_sales_staging values (1,2,to_date('10-jun-2008', 'dd-mon-rrrr'),'d',3,5,9.9);
1 row created.
SQL>
SQL> commit;
Commit complete.
SQL> ALTER TABLE interval_sales
2 EXCHANGE PARTITION FOR (TO_DATE('10-06-2008','DD-MM-YYYY'))
3 WITH TABLE interval_sales_staging
4 INCLUDING INDEXES
5 WITHOUT VALIDATION;
ALTER TABLE interval_sales
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
OK. What about if we try and do an exchange partition for data beyond the highest interval partition, does that cause a “partition does not exist” error as well?
SQL> truncate table interval_sales_staging;
Table truncated.
SQL> insert into interval_sales_staging values (1,2,to_date('10-jun-2018', 'dd-mon-rrrr'),'d',3,5,9.9);
1 row created.
SQL> commit;
Commit complete.
SQL> ALTER TABLE interval_sales
2 EXCHANGE PARTITION FOR (TO_DATE('10-06-2018','DD-MM-YYYY'))
3 WITH TABLE interval_sales_staging
4 INCLUDING INDEXES
5 WITHOUT VALIDATION;
ALTER TABLE interval_sales
*
ERROR at line 1:
ORA-02149: Specified partition does not exist
My guess here is that you need to insert some data into the interval partitioned table, for the partition you want to then exchange, before this will work. Let’s give this a try.
SQL> truncate table interval_sales_staging;
Table truncated.
SQL> insert into interval_sales values (1,2,to_date('10-jun-2008', 'dd-mon-rrrr'),'d',3,5,9.9);
1 row created.
SQL> commit;
Commit complete.
SQL> ALTER TABLE interval_sales
2 EXCHANGE PARTITION FOR (TO_DATE('10-06-2008','DD-MM-YYYY'))
3 WITH TABLE interval_sales_staging
4 INCLUDING INDEXES
5 WITHOUT VALIDATION;
Table altered.
So there you have it. You can do partition exchange loading with an interval partitioned table, and you can reference the partition using the FOR clause, but you need to have created the partition first of all, by inserting a dummy row into the interval partitioned table, before this will work. The row can be anything as long as it satisfies any constraints, the idea is to then get Oracle to create the mostly empty interval partition so that you can then exchange it with the contents of your staging table.
Thanks to Peter Scott for the examples, the last tip and the information on the FOR clause, before we were aware of this we were thinking that the system-generated partition name was going to more or less eliminate interval partitioning for partition exchange loading, so it was nice to come across this feature after taking a look through the documentation.
July 12th, 2010 at 6:30 pm
Mark,
We are evaluating using Interval partitioning in our 11g database now. So far I like everything that the feature has to offer. However, there is this one thing that is causing problem to us and I wonder if you know of any solution to this.
Let’s say I set up a partitioned table based on a date column that is range-partitioned. We create daily partitions. Now we start using Interval partitioning. As the new data arrives Oracle creates new daily partitions. The problem is someone inserts a row that has date of 50 years in future, Oracle creates a new partition that is 50 years in future. Now all the data gets stored in that one big partition until we get to 50 years. There is no way to prevent this from happening other than manually deleting the row, splitting that big partition and resetting it to use Interval partitioning. This is a bad thing since our regular purge won’t purge (drop partition) anything for 50 years.
What is the proper way of handling this? I cannot put a check constraint on that column based on SYSDATE. This one drawback may turn out to be too big to ignore. Although I agree that someone made a mistake of inserting that row, the consequences are huge.
Thanks.
July 13th, 2010 at 10:32 am
@Developer
This is not the behaviour I see. To check this I created a simple three column table range partitioned on a date column. I set interval partitioning to be by month and created the required initial partition.
CREATE TABLE TEST_TABLE1
(
COLUMN1 VARCHAR2(4000),
COLUMN2 NUMBER,
PKEY DATE NOT NULL
)
PARTITION BY RANGE(PKEY)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );
I then inser a row for the 1-feb-2010 and look in USER_TAB_PARTITIONS to see it existing in the data dictionary.
I insert a row for 1-feb-2020 <- Note this is ten years into the future and again look in USER_TAB_PARTITIONS I now have two system generated partitions and my initial partition that I created with the table
Finally, I add a row for the 1-may-2010 <- back to 2010 and again inspected USER_TAB_PARTITIONS – this time I see three system generated partitions – one for each insert
Hope this helps
July 13th, 2010 at 8:59 pm
I misstated it. I meant Oracle won’t go back before the first interval partition.
e.g. In your case, if you inserted 1-jan-2009, it won’t create a new partition in the past. Everything will go in your p0 partition.
July 14th, 2010 at 7:28 am
@Developer – Ah, I think I see what you mean.
Interval partitioning will automatically create new partitions for any interval that requires a partition based on partition key (it does not matter about the order of creation – Mark and I have both shown that the ‘gaps’ get filled by partitions) But you can not create a partition automatically with a key value less than the greatest “user” created partition – that is the partition(s) defined in the table create statement or manually added in a ALTER TABLE statement.
You could always create the table with an initial partition for a date a 100 years back – then most (all?) data would go into interval generated partitions – - having an empty partition is always an option – it would just waste a few blocks of space
February 1st, 2012 at 7:12 am
I would never use a user provided value as my partition key. I would partition based on a field’s value which is populated by trigger or the system but I would let a user provided value define what partition something goes in when using range partitioning.
July 19th, 2012 at 11:33 pm
I like the idea of interval partitioning, but it becomes unpractical when doing partition exchanges. I think having to insert dummy row is unclean. I like the statement ” the idea is to then get Oracle to create the mostly empty interval partition so that you can then exchange it with the contents of your staging table.” Can’t Oracle easily create a function for this?
October 6th, 2012 at 7:04 pm
Thank you for the excellent example and explanation, I’ll definitely be using it
January 14th, 2013 at 7:45 pm
I have a requirement where I need to change the transition point from 1st jan 2010 to 1st jan 2009 and all the partitions created after 1st jan 2009 should be interval. The business requirement is to load history data from 1st jan 2009.
Since I am not able to drop the partition which has the transition point, I have to create non-interval range partitions from 1st jan 2009 to 1st jan 2010.
Is there a workaround where we can change the transition point to 2009 and from there it behaves as interval partition.
Any help is appreciated.