Partition pruning with multiple date columns

Question

I have a large table in Oracle 11g database that holds historical data from several years, so I would like to partition it by year. The problem is that the table has multiple date columns and they are all used in queries, so I can't just pick one date column and use it as partition key.

Most of the time dates are close to each other, so I have created partitions for each year, plus one "overflow" partition that holds the rows that cross the year boundary. Here is a simplified example:

create table t (
  start_year int,
  end_year int,
  partition_year int as (case when start_year=end_year then start_year else 0 end),
  data blob 
)
partition by range(partition_year) (
  partition poverflow values less than (1000),
  partition p2000 values less than (2001),
  partition p2001 values less than (2002),
  partition p2002 values less than (2003),
  partition p2003 values less than (2004),
  partition p2004 values less than (2005)
);

The problem with this approach is that partition_year must be explicitly referenced in queries or partition pruning (highly desirable because the table is large) doesn't take effect. This table is used for ad-hoc aggregate queries by multiple users; I can't expect that they all remember this logic.

This can be solved with a view

create or replace view v as
select *
from t
where partition_year=start_year 
  and partition_year=end_year 
  and partition_year>1000
union all
select *
from t partition (poverflow);

Now queries like this one

select * from v where start_year >= 2003 and end_year <= 2004;

Use correct partitions (5-6 + 1 in plan below):

---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |      |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   1 |  VIEW                      | V    |     1 |  4030 |     2   (0)| 00:00:01 |       |       |
|   2 |   UNION-ALL                |      |       |       |            |          |       |       |
|   3 |    PARTITION RANGE ITERATOR|      |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|*  4 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     5 |     6 |
|   5 |    PARTITION RANGE SINGLE  |      |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
|*  6 |     TABLE ACCESS FULL      | T    |     1 |  2041 |     2   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004 AND "END_YEAR">=2003 AND 
              "START_YEAR"<=2004 AND "PARTITION_YEAR"<=2004 AND "PARTITION_YEAR"="START_YEAR" AND 
              "PARTITION_YEAR"="END_YEAR")
   6 - filter("START_YEAR">=2003 AND "END_YEAR"<=2004)

The problem is that if I replace int types with dates, this doesn't work any more. I have tried to extract the year component from dates and add corresponding constraints to the view, but partitions are not pruned. Changing the type of partition_year to date also didn't help.

Is there any way how I could have multiple date columns in a table and still be able to use partition pruning?

Answer 1

Oracle is unable to do partition pruning when a function is applied to the partitioned column. From the docs:

There are several cases when the optimizer cannot perform pruning. One common reasons is when an operator is used on top of a partitioning column. This could be an explicit operator (for example, a function) or even an implicit operator introduced by Oracle as part of the necessary data type conversion for executing the statement.

Your view has to apply some form of function to start and end dates to figure out if they're the same year or not, so I believe you're out of luck with this approach.

Our solution to a similar problem was to create materialized views over the base table, specifying different partition keys on the materialized views.

We've tailored ours to match common base queries so that we get query rewrite benefits as well. You may need to get users to use the MVs directly to ensure you get the partition pruning working as you need, rather than relying on query rewrite.

(Updated to remove incorrect example and add info regarding applying functions to partition columns)

Answer 2

I have tested the solution provided by Chris with this data:

insert into t (start_year,end_year) values (date'2011-01-01',date'2011-01-01');
insert into t (start_year,end_year) values (date'2011-01-01',date'2011-01-02');

If I run a query against the view:

select * from v;

I only get the first row back. This is because the view has an equality predicate, but the partition definition has extract(year) function.

If I modify the view to include extract functions:

create or replace view v as
select *
from t
where extract(year from partition_year)=extract(year from start_year)
  and extract(year from partition_year)=extract(year from end_year)
  and partition_year>date'2000-01-01'
union all
select *
from t partition (poverflow);

I get correct results but partition pruning doesn't happen any more.

Answer 3

I have found a partial solution

By defining view as

create or replace view v as
select *
from t
where partition_date between start_date and end_date 
  and partition_date > date'1000-01-01'
union all
select *
from t partition (poverflow);

The following query works correctly, accessing only partitions 1,4 and 5

select * from v where start_date >= date'2002-01-01' and end_date <= date'2003-01-01';

However, query

select * from v where start_date = date'2002-01-01';

Scans partitions 1,4-6, instead of 1 and 4 (using end_date instead would access partitions 1-4). In our case, this is not a critical limitation since typical queries access only the most recent years, queries against specific date and date ranges in the past are rare.

A slightly different version of this approach would be to define partition_date column as

case when trunc(start_date,'YEAR')=trunc(end_date,'YEAR') then greatest(start_date,end_date) 
else to_date('01.01.0001') end

And the view as

create or replace view v as
select *
from t
where partition_date >= start_date and partition_date >= end_date
  and partition_date > date'1000-01-01'
union all
select *
from t partition (poverflow);

This has similar performance, but start_date and end_date both cause more recent years to be accessed. If the requirements are relaxed like this (pruning only the previous years is allowed) then the overflow partition is not actually needed any more and the solution simplifies to:

create table t (
  start_date date,
  end_date date,
  partition_date date as (greatest(start_date,end_date)),
  data blob
)
partition by range(partition_date) (
  partition p2000 values less than (date'2001-01-01'),
  partition p2001 values less than (date'2002-01-01'),
  partition p2002 values less than (date'2003-01-01'),
  partition p2003 values less than (date'2004-01-01'),
  partition p2004 values less than (date'2005-01-01')
);

create or replace view v as
select *
from t
where partition_date >= start_date and partition_date >= end_date;