Intelligent partition pruning

Partition pruning is only possible in presence of partition key columns in predicates, but this scenario recently appeared in an application that breaks the rule.

demo@ORA11G> create table t

  2  partition by range(end_date)

  3  ( partition p2010 values less than (to_date('01-Jan-2011','dd-mon-yyyy')) ,

  4    partition p2011 values less than (to_date('01-Jan-2012','dd-mon-yyyy')) ,

  5    partition p2012 values less than (to_date('01-Jan-2013','dd-mon-yyyy')) ,

  6    partition p2013 values less than (to_date('01-Jan-2014','dd-mon-yyyy')) ,

  7    partition p2014 values less than (to_date('01-Jan-2015','dd-mon-yyyy')) ,

  8    partition p2015 values less than (to_date('01-Jan-2016','dd-mon-yyyy')) ,

  9    partition p2016 values less than (to_date('01-Jan-2017','dd-mon-yyyy')) ,

 10    partition p2017 values less than (to_date('01-Jan-2018','dd-mon-yyyy')) )

 11  as

 12  select a.* , created-1 as begin_date,

 13               created+1 as end_date

 14  from all_objects a;

 

Table created.

 

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

 

PL/SQL procedure successfully completed.

 

demo@ORA11G> set autotrace traceonly explain

demo@ORA11G> select *

  2  from t

  3  where begin_date between

  4     to_date('01-apr-2016','dd-mon-yyyy') and

  5     to_date('15-apr-2016','dd-mon-yyyy') ;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3557914527

 

--------------------------------------------------------------------------------------------

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT    |      |   989 |   110K|   360   (1)| 00:00:05 |       |       |

|   1 |  PARTITION RANGE ALL|      |   989 |   110K|   360   (1)| 00:00:05 |     1 |     8 |

|*  2 |   TABLE ACCESS FULL | T    |   989 |   110K|   360   (1)| 00:00:05 |     1 |     8 |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("BEGIN_DATE">=TO_DATE(' 2016-04-01 00:00:00', 'syyyy-mm-dd

              hh24:mi:ss') AND "BEGIN_DATE"<=TO_DATE(' 2016-04-15 00:00:00', 'syyyy-mm-dd

              hh24:mi:ss'))

 

 

The table is partitioned by end_date and the above query doesn’t have any filters/predicates on the end_date column so we scanned all the partitions to answer this query.

 

But if we could provide some information about the data representation, then the optimizer can make use of them during optimization to yield better plans.

 

demo@ORA11G> alter table t add constraint t_check

  2  check(begin_date <= end_date);

 

Table altered.

 

demo@ORA11G> alter table t modify begin_date not null;

 

Table altered.

 

demo@ORA11G> alter table t modify end_date not null;

 

Table altered.

 

 

So I have provided the information via the check constraint saying that begin_date and end_date are mandatory columns and begin_date should always be less than or equal to end_date column.

 

With these metadata in place, executing the above query would produce the plan like this.

 

demo@ORA11G> select *

  2  from t

  3  where begin_date between

  4     to_date('01-apr-2016','dd-mon-yyyy') and

  5     to_date('15-apr-2016','dd-mon-yyyy') ;

 

Execution Plan

----------------------------------------------------------

Plan hash value: 1571388083

 

-------------------------------------------------------------------------------------------------

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

-------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT         |      |   989 |   110K|     7   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ITERATOR|      |   989 |   110K|     7   (0)| 00:00:01 |     7 |     8 |

|*  2 |   TABLE ACCESS FULL      | T    |   989 |   110K|     7   (0)| 00:00:01 |     7 |     8 |

-------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("BEGIN_DATE">=TO_DATE(' 2016-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

              AND "BEGIN_DATE"<=TO_DATE(' 2016-04-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "END_DATE">=TO_DATE(' 2016-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

 

 

With the presence of check and not null constraint, the optimizer effectively added the predicate on end_date column and resulted in scanning only a subset of partitions.