Investigating Oracle 11g Interval Partitioning

September 13th, 2008 by

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.

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.

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.

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?

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?

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:

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:

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:

So if I prepare staging table to match my partitioned table, like this:

and the add some data to it…

and then try out an exchange partition, using this new FOR clause, it works:

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:

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?

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?

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.

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.

Comments

  1. Developer Says:

    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.

  2. Peter Scott Says:

    @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

  3. Developer Says:

    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.

  4. Peter Scott Says:

    @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

  5. Robert Nicholson Says:

    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.

  6. ScottJameson Says:

    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?

  7. Sandy Says:

    Thank you for the excellent example and explanation, I’ll definitely be using it

  8. Vinoth Says:

    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.

Website Design & Build: tymedia.co.uk