NOT NULL on partition key columns

Had discussed about the importance of NOT NULL constraints in-terms of query rewrite capabilities in the past.

The following example appeared as part of real production scenario, where a simple predicate against the sub-partition key columns doesn’t perform partition elimination.

rajesh@ORA11G> create table t

  2  partition by range( end_dt )

  3  subpartition by range( start_dt )

  4  (

  5    partition p_2014 values less than

  6             ( to_date('01-Jan-2015','dd-mon-yyyy') )

  7     (

  8                subpartition sp_2014_q1 values less than

  9                             ( to_date('01-apr-2014','dd-mon-yyyy') ) ,

 10                subpartition sp_2014_q2 values less than

 11                             ( to_date('01-jul-2014','dd-mon-yyyy') ) ,

 12                subpartition sp_2014_q3 values less than

 13                             ( to_date('01-oct-2014','dd-mon-yyyy') ) ,

 14                subpartition sp_2014_q4 values less than

 15                             ( to_date('01-jan-2015','dd-mon-yyyy') )

 16     ) ,

 17    partition p_2015 values less than

 18             ( to_date('01-Jan-2016','dd-mon-yyyy') )

 19     (

 20               subpartition sp_2015_q1 values less than

 21                             ( to_date('01-apr-2015','dd-mon-yyyy') ) ,

 22               subpartition sp_2015_q2 values less than

 23                             ( to_date('01-jul-2015','dd-mon-yyyy') ) ,

 24               subpartition sp_2015_q3 values less than

 25                             ( to_date('01-oct-2015','dd-mon-yyyy') ) ,

 26               subpartition sp_2015_q4 values less than

 27                             ( to_date('01-jan-2016','dd-mon-yyyy') )

 28     )

 29  )

 30  as

 31  select a.* ,

 32     to_date('01-Jan-2014','dd-mon-yyyy') + mod(rownum,730)-1 as start_dt,

 33     to_date('01-Jan-2014','dd-mon-yyyy') + mod(rownum,730) as end_dt

 34  from all_objects a;

 

Table created.

 

rajesh@ORA11G> alter table t add constraint t_chk check( start_dt < end_dt );

 

Table altered.

 

Given the fact that, table is partitioned by END_DT and sub partitioned by START_DT and a check constraint to enforce that START_DT is always less than END_DT.

 

rajesh@ORA11G> set autotrace traceonly explain

rajesh@ORA11G> select *

  2  from t

  3  where start_dt = to_date('13-Jan-2015','dd-mon-yyyy') ;

 

Execution Plan

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

Plan hash value: 2183203583

 

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

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

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

|   0 | SELECT STATEMENT        |      |   136 | 23936 |    58   (6)|       |       |

|   1 |  PARTITION RANGE ALL    |      |   136 | 23936 |    58   (6)|     1 |     2 |

|   2 |   PARTITION RANGE SINGLE|      |   136 | 23936 |    58   (6)|       |       |

|*  3 |    TABLE ACCESS FULL    | T    |   136 | 23936 |    58   (6)|       |       |

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

 

Predicate Information (identified by operation id):

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

 

   3 - filter("START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

 

The above sql has a filter against the START_DT column, but why should it hit both the partitions, can’t the optimizer do something like this using metadata and do partition elimination?

 

So given the filter start_dt = 13-Jan-2015, and the presence of check constraint can produce the additional transitive filter like this (end_dt > 13-Jan-2015)

 

o   13-Jan-2015 less than 01-Jan-2015 – condition false, so skip scanning first partition.

o   13-Jan-2015 less than 01-Jan-2016 – condition true, so scan this partition and its relevant sub-partition to produce results.

 

But the optimizer doesn’t perform that. Instead it hit both the partitions.

 

Peeking into the 10053 trace file, produced some clue on this (highlighted below).

 

**************************

PM:     PM bypassed: Outer query contains no views.

PM:     PM bypassed: Outer query contains no views.

query block SEL$1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$1 (#0)

"T"."START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

try to generate transitive predicate from check constraints for query block SEL$1 (#0)

constraint: "T"."START_DT"<"T"."END_DT"

 

finally: "T"."START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

 

apadrv-start sqlid=18016176355183581983

  :

    call(in-use=2136, alloc=16344), compile(in-use=67952, alloc=72680), execution(in-use=109984, alloc=113960)

 

*******************************************

Peeked values of the binds in SQL statement

*******************************************

 

Final query after transformations:******* UNPARSED QUERY IS *******

 

SELECT "T"."OWNER" "OWNER",

  "T"."OBJECT_NAME" "OBJECT_NAME",

  "T"."SUBOBJECT_NAME" "SUBOBJECT_NAME",

  "T"."OBJECT_ID" "OBJECT_ID",

  "T"."DATA_OBJECT_ID" "DATA_OBJECT_ID",

  "T"."OBJECT_TYPE" "OBJECT_TYPE",

  "T"."CREATED" "CREATED",

  "T"."LAST_DDL_TIME" "LAST_DDL_TIME",

  "T"."TIMESTAMP" "TIMESTAMP",

  "T"."STATUS" "STATUS",

  "T"."TEMPORARY" "TEMPORARY",

  "T"."GENERATED" "GENERATED",

  "T"."SECONDARY" "SECONDARY",

  "T"."NAMESPACE" "NAMESPACE",

  "T"."EDITION_NAME" "EDITION_NAME",

  "T"."START_DT" "START_DT",

  "T"."END_DT" "END_DT"

FROM "RAJESH"."T" "T"

WHERE "T"."START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

 

 

Now let’s add a NOT NULL constraints on the partition key columns and rerun our sql to see the plan it picks.

 

rajesh@ORA11G> alter table t modify END_DT NOT NULL;

 

Table altered.

 

rajesh@ORA11G> set autotrace traceonly explain

rajesh@ORA11G> select *

  2  from t

  3  where start_dt = to_date('13-Jan-2015','dd-mon-yyyy') ;

 

Execution Plan

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

Plan hash value: 3874588989

 

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

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

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

|   0 | SELECT STATEMENT        |      |   136 | 23936 |    55   (0)|       |       |

|   1 |  PARTITION RANGE SINGLE |      |   136 | 23936 |    55   (0)|     2 |     2 |

|   2 |   PARTITION RANGE SINGLE|      |   136 | 23936 |    55   (0)|     1 |     1 |

|*  3 |    TABLE ACCESS FULL    | T    |   136 | 23936 |    55   (0)|     5 |     5 |

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

 

Predicate Information (identified by operation id):

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

 

   3 - filter("START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "END_DT">TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

 

So given the NOT NULL constraint on the END_DT column and the presence of check constraint, helped the optimizer to transparently rewrite the given condition

 

start_dt = to_date('13-Jan-2015','dd-mon-yyyy')

 

as

 

start_dt = to_date('13-Jan-2015','dd-mon-yyyy') and end_dt > to_date('13-Jan-2015','dd-mon-yyyy')

 

Since the end_dt > to_date('13-Jan-2015','dd-mon-yyyy'), it applied this criteria to eliminate the first partition from scanning and hit straight into the Second partition, and within that it only scan the relevant sub-partition required for this SQL.

 

And the 10053 trace file confirms the same.

 

FPD: Considering simple filter push in query block SEL$1 (#0)

"T"."START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

try to generate transitive predicate from check constraints for query block SEL$1 (#0)

constraint: "T"."START_DT"<"T"."END_DT"

 

finally: "T"."START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

 

FPD:   transitive predicates are generated in query block SEL$1 (#0)

"T"."START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."END_DT">TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

apadrv-start sqlid=18016176355183581983

 

Final query AFTER transformations:******* UNPARSED QUERY

IS

  *******

  SELECT "T"."OWNER" "OWNER",

    "T"."OBJECT_NAME" "OBJECT_NAME",

    "T"."SUBOBJECT_NAME" "SUBOBJECT_NAME",

    "T"."OBJECT_ID" "OBJECT_ID",

    "T"."DATA_OBJECT_ID" "DATA_OBJECT_ID",

    "T"."OBJECT_TYPE" "OBJECT_TYPE",

    "T"."CREATED" "CREATED",

    "T"."LAST_DDL_TIME" "LAST_DDL_TIME",

    "T"."TIMESTAMP" "TIMESTAMP",

    "T"."STATUS" "STATUS",

    "T"."TEMPORARY" "TEMPORARY",

    "T"."GENERATED" "GENERATED",

    "T"."SECONDARY" "SECONDARY",

    "T"."NAMESPACE" "NAMESPACE",

    "T"."EDITION_NAME" "EDITION_NAME",

    "T"."START_DT" "START_DT",

    "T"."END_DT" "END_DT"

  FROM "RAJESH"."T" "T"

  WHERE "T"."START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

  AND "T"."END_DT"    >TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

 

All these sort of partition elimination (pruning) is possible only with NOT NULL constraint in place.

 

The dreaded NOT NULL constraint strikes again!