Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 0 down vote accepted

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;
share|improve this answer

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)

share|improve this answer
    
Materialized view with query rewrite might indeed work. Of course it would require multiple copies of data, so it's not an ideal solution, but worth considering. The date values in original data have granularity of one day. If the scheme above is used, equality predicate would miss many of the rows that span multiple days. –  sjk Dec 14 '12 at 10:18
    
@sjk - yep, you have to trade off increased storage vs. improved performance. You could always create the partitioned MVs on subset of your data if, for example, most of the queries only cover dates in the past two years. I'm not sure what you mean when you say that that equality predicates would miss many rows - could you give a more detail please? –  Chris Saxon Dec 14 '12 at 13:00
    
The view above contains (partition_year=start_year and partition_year=end_year), if start_year would instead be start_date, the first half of view would not match to most rows any more. I tried to change the view to include (extract year), but then I could not get pruning any more (it did a full table scan). I could of course also change partition_year to partition_date, but then most of the rows would end to overflow partition (since typical date range is more than one day). –  sjk Dec 14 '12 at 13:15
    
@sjk - I think you need to post your code with dates that isn't pruning, rather than the integer/year example that is. Although I've called the columns year in my example, they are just dates and partition pruning is working (for me). So I'm struggling to see why it's not working for you. –  Chris Saxon Dec 16 '12 at 15:19

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.

share|improve this answer
    
Thanks - I see what the issue is now! I've updated my answer: I believe you're out of luck with this approach unfortunately. –  Chris Saxon Dec 17 '12 at 13:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.