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|
-----------------------------------------------------------------------------

Oracle Database INTERVAL Partitioning Date Error With 31-12-9999

You may have seen the below error when using dates at the extremes with an Oracle Database. This post focuses on the scenario where you’re inserting a high date, such as 31-12-9999 into an INTERVAL partitioned table. High dates such as this are often used in warehouse environments where from and to dates are specified to denote a period of time the data was valid for – a high date specifying the current data row.

Take the following table and partition definition:

CREATE TABLE interval_date_test
(
  my_date   DATE NOT NULL
)
PARTITION BY RANGE (my_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
(
  PARTITION p_interval_date_test0 VALUES LESS THAN (TO_DATE('2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
ENABLE ROW MOVEMENT
/

Each row inserted will go into a partition for that year-month combination. This can be useful for high volume data that’s generally queried for one-or-so months at a time.

If, however, you try to insert the following row, you’ll get an error:

INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31');

INSERT INTO interval_date_test (my_date) VALUES(DATE '9999-12-31')
            *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

This is due to the way the Oracle Database defines a partition. An INTERVAL RANGE partition in an Oracle Database is defined as accepting dates LESS THAN a specific value. The below example would store any dates up to the clock ticking over into May 2016.

VALUES LESS THAN (TO_DATE('2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

When partitioned monthly, the LESS THAN value for inserting DATE ‘9999-12-31’ would have to be DATE ‘10000-01-01’ and that is not a valid date value. Oracle Database dates must be between the year -4713 and +9999 inclusive as explained by the error.

The solution? Either use a non-INTERVAL partition strategy, reduce the date being INSERTed or experiment with a Virtual Column and partition on that.