Virtual date partitions

I posted this question on twitter earlier on today (It was a thought that crossed my mind during a (terrible) presentation on partitioning that I had to sit through a few weeks ago – no matter how bad a presentation is I always seem to be able to get a couple of interesting questions out of it):

Quiz: if you have a table with a date column date_col and create a virtual column defined as trunc(date_col,’W’) and partition on it – will a query on date_col result in partition elimination?

The answer is yes – at least for the version of Oracle that I happened to have to hand (12c) the next time I had a few minutes spare. Here’s a quick and dirty demo – with data content relevant to the publication date of this blog note so you may need to adjust the code to your current date if you want to run the test.

rem
rem     Script:         pt_virtual_date.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Oct 2013
rem     Purpose:

drop table transactions purge;

create table transactions (
        transaction_date        date            not null,
        job_id                  varchar2(10)    not null,
        account_id              number(8)       not null,
        transaction_type        varchar2(2)     not null,
        transaction_id          varchar2(10)    not null,
        amount                  number(10,2)    not null,
        padding                 varchar2(100),
        transaction_week        generated always as (trunc(transaction_date,'IW'))
)
partition by range (transaction_week) interval (numtodsinterval(7,'day')) (
        partition p0 values less than (to_date('30-Sep-2013','dd-mon-yyyy'))
)
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate)-7, 1, 1, 'IN', 1, 1, rpad('x',100))
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate), 2, 2, 'IN', 2, 2, rpad('x',100))
;

insert into transactions( transaction_date, job_id  , account_id , transaction_type, transaction_id , amount  , padding )
values(trunc(sysdate) + 7, 3, 3, 'IN', 3, 3, rpad('x',100))
;

begin
	dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'transactions',
                method_opt       => 'for all columns size 1'
	);
end;
/

So transaction_date is a column in the table but the partitioning column is transaction_week, which is defined as trunc(transaction_date,’IW’) which gives me Mondays as the partition boundaries. (I always go back to the manuals for truncating on weeks – there are at least two options (W and IW) and there’s a significant difference in the way they are defined.)

So here are a couple of simple queries – will the optimizer do partition elimination ?

select * from transactions where transaction_date = sysdate;
select * from transactions where transaction_date = to_date('17-Oct-2013','dd-mon-yyyy');

And the answer is yes. Note the “partition range single” that appears at operation 1 in both case even though the Pstart and Pstop show KEY/KEY – which means Oracle has to deduce which partitions are relevant at run-time.

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |   130 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TRANSACTION_DATE"=SYSDATE@! AND "TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(SYSDATE@!,'fmiw'))

-------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |              |     1 |   130 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|              |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
|*  2 |   TABLE ACCESS FULL    | TRANSACTIONS |     1 |   130 |     2   (0)| 00:00:01 |   KEY |   KEY |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TRANSACTION_DATE"=TO_DATE(' 2013-10-17 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "TRANSACTIONS"."TRANSACTION_WEEK"=TRUNC(TO_DATE(' 2013-10-17 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'),'fmiw'))

Pretty clever !

I find it slightly strange that the predicate section also reports the derived predicate on the virtual column – it’s clearly not necessary for this example because we can see that the optimizer has already decided that the query will visit just one partition and we (and, I would have thought, includes the optimizer) know that every row in that partition will have the same value for the virtual column. Possibly its presence is just a side effect of the generic strategy used for more complex predicates and the special-case code hasn’t been included, but maybe there’s something I’ve overlooked that makes it necessary. (It might be interesting to experiment with adding a constraint describing the relationship between transaction_date and transaction_week to see what happens … but that’s left as an exercise.)