Sometimes in the IT world, the term “surprise” is not a good one.
“I woke up this morning and got a surprise…my database was down.”
“I ran a SELECT COUNT(*) on my most important table, and got a surprise result of zero rows.”
and so forth. Generally as IT professionals, encountering the unexpected is not a good start to the day 
.
But sometimes, surprises can be a nice thing. Here is my example for today – when I found that the database can do a remarkably good job when it comes to reducing the workload with partitioned tables.
Most people will already be familiar with the concept of partition elimination. If you have a table partitioned into (say) yearly segments, and you ask for all of the data for the past 2 years, then the optimizer is intelligent enough to only scan the relevant partitions rather than the entire table. Here is an example of that in action. I have a table that is partitioned by year on a END_DATE column, and then sub-partitioned into quarters based on a START_DATE column.
SQL> create table t
2 partition by range( end_dt )
3 subpartition by range( start_dt )
4 (
5 partition p_2014 values less than ( to_date('01-Jan-2015','dd-mon-yyyy') )
6 (
7 subpartition sp_2014_q1 values less than ( to_date('01-apr-2014','dd-mon-yyyy') ) ,
8 subpartition sp_2014_q2 values less than ( to_date('01-jul-2014','dd-mon-yyyy') ) ,
9 subpartition sp_2014_q3 values less than ( to_date('01-oct-2014','dd-mon-yyyy') ) ,
10 subpartition sp_2014_q4 values less than ( to_date('01-jan-2015','dd-mon-yyyy') )
11 ) ,
12 partition p_2015 values less than ( to_date('01-Jan-2016','dd-mon-yyyy') )
13 (
14 subpartition sp_2015_q1 values less than ( to_date('01-apr-2015','dd-mon-yyyy') ) ,
15 subpartition sp_2015_q2 values less than ( to_date('01-jul-2015','dd-mon-yyyy') ) ,
16 subpartition sp_2015_q3 values less than ( to_date('01-oct-2015','dd-mon-yyyy') ) ,
17 subpartition sp_2015_q4 values less than ( to_date('01-jan-2016','dd-mon-yyyy') )
18 )
19 )
20 as
21 select a.* ,
22 to_date('01-Jan-2014','dd-mon-yyyy') + mod(rownum,730)-1 as start_dt,
23 to_date('01-Jan-2014','dd-mon-yyyy') + mod(rownum,730) as end_dt
24 from all_objects a;
Table created.
SQL>
SQL> alter table t modify start_dt not null;
Table altered.
SQL> alter table t modify end_dt not null;
Table altered.
SQL>
SQL> set autotrace traceonly explain
SQL> 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)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 15049 | 491 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 101 | 15049 | 491 (1)| 00:00:01 | 1 | 2 |
| 2 | PARTITION RANGE SINGLE| | 101 | 15049 | 491 (1)| 00:00:01 | | |
|* 3 | TABLE ACCESS FULL | T | 101 | 15049 | 491 (1)| 00:00:01 | | |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("START_DT"=TO_DATE(' 2015-01-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
You can see that we had to scan all of the partitions, but within each of the partitions we only had to scan a single sub-partition (as indicated by “PARTITION RANGE SINGLE”). So for each year (based on END_DT) we scanned a single one of the 4 subpartitions. We are doing only 25% of the work of scanning the entire table. But one pleasant surprise I saw today was how the optimizer can take advantage of additional information to improve things even more. Let us now add a fairly obvious rule about the data:
SQL>
SQL> alter table t add constraint t_chk check( start_dt < end_dt );
Table altered.
And have another look at that query execution plan.
SQL>
SQL> set autotrace traceonly explain
SQL> 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)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 101 | 15049 | 247 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 101 | 15049 | 247 (1)| 00:00:01 | 2 | 2 |
| 2 | PARTITION RANGE SINGLE| | 101 | 15049 | 247 (1)| 00:00:01 | 1 | 1 |
|* 3 | TABLE ACCESS FULL | T | 101 | 15049 | 247 (1)| 00:00:01 | 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'))
SQL>
SQL>
How cool is that! The constraint let us remove even more partitions from consideration. In fact, we ended up only scanning a single partition. Moreover, we never specified END_DT as a predicate, but you can see in the FILTER section, we could synthesize such a predicate using the rule defined by the check constraint we added.
I suppose the moral of the story is two-fold here.
1) The optimizer can be pretty smart with partition elimination,
2) As always, never hide rules and facts about the data from the database. Make sure you define those constraints in the database tier.
