Oracle Database INTERVAL Partitioning and Virtual Columns

I bumped into this issue recently which prevents you INSERTING a date of 31-12-9999 into an INTERVAL partitioned table due to the fact that the LESS THAN partition value would be larger than an Oracle Date.

After speaking with Jeff Moss he mentioned that we might be able to get round the problem by using a virtual column on the table and partition on that. I didn’t find a satisfactory solution using this method, but understanding some of the optimisers quirks and abilities when using partitions and virtual columns was rather interesting.

Starting with the following table structure:

CREATE TABLE interval_test
(
  data_from_date   DATE NOT NULL
, data_to_date     DATE
, business_key     NUMBER
)
PARTITION BY RANGE (data_to_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_test_0 VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

The purpose here is to create a tuple timestamped table for storing historical and current data. The data_from_date represents the date the row is known to be valid from and the data_to_date is when the row is known to changed or been removed entirely from the source system. The current row, the one that is still available in the source system, would not have an ‘end date’ and therefore would either be NULL or a made up ‘high’ date, such as DATE 9999-12-31.

And this is where the problems start.

Winding back a little, and forgetting the high date scenario for a moment, let’s take a look at how queries use the partitions. Generally, queries would use a specific date that’s between the data_from_date and data_to_date to get a view of the data for a point in time.

EXPLAIN PLAN FOR 
SELECT * 
FROM interval_test 
WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date
/

-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |    14 |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |    14 |1048575|
-----------------------------------------------------------------------------

As you can see, the partitions are being pruned where possible, starting at partition 14. Side note: because this table is INTERVAL partitioned and there is no maximum value set for our data_to_date Oracle may have to look at all the partitions after partition 14. Potentially, this could be all the way up to 1048575 which is the maximum partition number that could exist. It’s a shame the optimiser isn’t a little more intelligent here – I know there aren’t 1048575 partitions and so should the optimiser!

SELECT COUNT(*) FROM dba_tab_partitions WHERE table_name = 'INTERVAL_TEST';

  COUNT(*)
----------
        34

Anyway, back to the matter at hand. Inserting a high date into this table results in an error (described further here).

ORA-01841: (full) year must be between -4713 and +9999, and not be 0

A potential solution to the problem was to add a virtual column to the table that translates the high date into something that would go into a monthly partition. There are a bunch of options, to name a few:

• TRUNC the date to ‘MM’ and minus 1
• Minus 31 away from the date
• TRUNC the date to ‘IQ’ – this would give quarterly partitions and not monthly partitions, but let’s not rule it out.
• TO_CHAR the date to a YYYYMM, then CAST that as a NUMBER and use numbered partitioning.

I’m not going to explore all of these options and the set up script for you to test further scenarios is here. Note the test SQL will be:

SELECT * FROM interval_test WHERE DATE '2001-01-01' BETWEEN data_from_date AND data_to_date

Truncate the month and minus 1

This was my first test as this would give me the previous months value to partition on which would nicely fit in without causing the ORA-01841 error. The virtual column expression is as follows:

, partition_column AS (TRUNC(data_to_date, 'MM') - 1)

The result wasn’t a success with a query plan of:

------------------------------------------------------------------------
| Id  | Operation           | Name          | Time     | Pstart| Pstop |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|               | 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | INTERVAL_TEST | 00:00:01 |     1 |1048575|
------------------------------------------------------------------------

Minus 31

Next I simplified the expression by simply minusing 31 days from the date being inserted. This removes the TRUNC ‘complication’ to produce a less complex expression.

, partition_column AS (data_to_date -31)

Again, the result was a failure with a query plan of:

------------------------------------------------------------------------
| Id  | Operation           | Name          | Time     | Pstart| Pstop |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL|               | 00:00:01 |     1 |1048575|
|*  2 |   TABLE ACCESS FULL | INTERVAL_TEST | 00:00:01 |     1 |1048575|
------------------------------------------------------------------------

Does it ever work?

Yes! The following expressions work, but as you’ll notice have mixed results in solving our initial problem. It seems as soon as any math operator is specified that the optimiser cannot work out that partition pruning can occur. Using TRUNC however, which to me seems a more complex function, works perfectly. Interestingly when it does work, the filter section of the query plan shows an additional predicate that your query doesn’t specify but that is used to partition prune.

filter("DATA_FROM_DATE"<=TO_DATE(' 2001-01-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "DATA_TO_DATE">=TO_DATE(' 2001-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"INTERVAL_TEST"."PARTITION_COLUMN">=TRUNC(TO_DATE(' 2001-01-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss'),'IQ'))

Here are some of the virtual columns that do partition prune however, might not solve the original issue.

, partition_column AS (TRUNC(data_to_date, 'IQ'))

-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |   KEY |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |   KEY |1048575|
-----------------------------------------------------------------------------

, partition_column AS (TRUNC(data_to_date)) 

-----------------------------------------------------------------------------
| Id  | Operation                | Name          | Time     | Pstart| Pstop |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |               | 00:00:01 |       |       |
|   1 |  PARTITION RANGE ITERATOR|               | 00:00:01 |   KEY |1048575|
|*  2 |   TABLE ACCESS FULL      | INTERVAL_TEST | 00:00:01 |   KEY |1048575|
-----------------------------------------------------------------------------