Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: February 14, 2018 - 3:59 am UTC

Last updated: February 23, 2018 - 12:39 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Table with 10 years worth of data
Has two key date columns.

Queries are always by one of the dates. But never both.
So how to get partition elimination to work for both when really can only partition by 1 column.

**Thought was - since each partition (and subpartition) has hi/low boundary in DDL. Can the query that goes JUST for the subpartition - get partition elimation. Even if it spands multiple subpartitions - it would still be much better than entire global FTS.

DDL can be copied from shipments table here.
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/create-composite-partition-table.html#GUID-C1FA4836-206B-40E2-AEB9-05D4A85A6B61




Try and access subpartition directly. i.e Secondary date column.

SQL> explain plan for
  select * from shipments where delivery_date between TO_DATE('01-NOV-2006','dd-MON-yyyy') 
and TO_DATE('30-NOV-2006','dd-MON-yyyy');



Explained.



SQL> select * from table(dbms_xplan.display);



PLAN_TABLE_OUTPUT

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

Plan hash value: 337679427



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

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

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

| 0 | SELECT STATEMENT | | 1 | 57 | 2 (0)| 00:00:01 | | |

| 1 | PARTITION RANGE ALL | | 1 | 57 | 2 (0)| 00:00:01 | 1 | 6 |

| 2 | PARTITION RANGE ITERATOR| | 1 | 57 | 2 (0)| 00:00:01 | KEY | KEY |

|* 3 | TABLE ACCESS FULL | SHIPMENTS | 1 | 57 | 2 (0)| 00:00:01 | 1 | 18 |

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



Predicate Information (identified by operation id):

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



3 - filter("DELIVERY_DATE">=TO_DATE(' 2006-11-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

"DELIVERY_DATE"<=TO_DATE(' 2006-11-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))





Not sure – to me it looks like its going to go through all 18 subpartitons. But not sure on the KEY KEY as that indicates partition elimination. So ???



**NB** this is valid syntax though [SQL> select * from shipments subpartition (P06_NOV_A) where delivery_date = TO_DATE('01-NOV-2006','dd-MON-yyyy');] makes me wonder

QUESTIONS
=========

1) I get KEY KEY dynamic elimination in normal single level partition scheme. But whats happening above here ^^.

2) How to "prove" partition elimination is actually happening at runtime - 10053 ? Which partitions did I truly access. Possible ?.

3) Data Model. If I need to access data via two separate dates. How do we provide elimination of data (typically after 1 year of the 10 years) if we cant leverage direct subpartition. i.e Whats a way to solve not scanning 10 years. Using an index wont help too may probes.


and Connor said...

Yes you can get benefit. Here's a simpler example

SQL> insert into t
  2    select
  3      date '2014-01-01'+ trunc(dbms_random.value(1,1000)),
  4      date '2014-01-01'+ trunc(dbms_random.value(1,1000)),
  5      rownum
  6  from dual
  7  connect by level <= 10000;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t where x = date '2016-07-01';

Execution Plan
----------------------------------------------------------
Plan hash value: 1206648699

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    10 |   200 |   820   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE|      |    10 |   200 |   820   (1)| 00:00:01 |     3 |     3 |
|   2 |   PARTITION RANGE ALL  |      |    10 |   200 |   820   (1)| 00:00:01 |     1 |     3 |
|*  3 |    TABLE ACCESS FULL   | T    |    10 |   200 |   820   (1)| 00:00:01 |     7 |     9 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("X"=TO_DATE(' 2016-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))




line 1 - I scanned just a single partition (#3 on the outer partitioning scheme, ie 'x')
line 2 - for that one partitioned, I scanned all three subpartitions (1 thru 3)
line 3 - looks odd, but if you number *all* physical segments, there are 9 of them, and we scanned 7 through 9

Now we provide just 'y'

SQL> select * from t where y = date '2016-07-01';

Execution Plan
----------------------------------------------------------
Plan hash value: 1544514985

------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |    10 |   200 |   820   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL    |      |    10 |   200 |   820   (1)| 00:00:01 |     1 |     3 |
|   2 |   PARTITION RANGE SINGLE|      |    10 |   200 |   820   (1)| 00:00:01 |     3 |     3 |
|*  3 |    TABLE ACCESS FULL    | T    |    10 |   200 |   820   (1)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("Y"=TO_DATE(' 2016-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))


line 1 - I scanned all three logical partitions for 'x'
line 2 - for each of those, I only scan the 3rd subpartition
line 3 - we canr show the partition numbers (3,6,9) simply because of the limitations of explain plan output

I can prove what partitions I actually found data in via the following

SQL> select distinct dbms_rowid.rowid_object(rowid)
  2  from t where y = date '2016-07-01';

DBMS_ROWID.ROWID_OBJECT(ROWID)
------------------------------
                        211816
                        211813

2 rows selected.

SQL> select object_name, subobject_name from user_objects where data_object_id in (211816, 211813 );

OBJECT_NAME                              SUBOBJECT_NAME
---------------------------------------- ------------------------------
T                                        P1C
T                                        P2C


but obviously that's not conclusive proof that I did not scan the others. I you really want that, you can set a 10046 trace at level 8, and you'll see all the file/block waits and map them into DBA_EXTENTS, ie,

select * 
from dba_extents
where file_id = [file from trace]
and [block# from trace] between block_id and block_id + blocks - 1



Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John, February 19, 2018 - 2:28 pm UTC

Thanks Connor. Looks great and that's how I expected it to work before I put the testcase together.

But - if you notice on the testcase I didnt get the same results.I scanned all 6 main partitions and all 18 (3 each) sub-partitions.

I'm not sure if that's due to statistics and/or using a between date range rather than a specific date range. I can play around with that and try and determine.

Let me ask you this then
If you were/are designing the physical DB aspect of a system that access the data (large volumes like DSS) via two seperate dates - what method would you use ?

Partitioning/subpartitioning
Bitmap local partitioned indexes
Mix of both/other ?

Sometimes just knowing where to start helps.

Right now we are reverting back to just partitioning on one key date column .... and looking at MV view with fast refresh/real time to partition by other date. App can point to each/or based on date. Data is only small change rate.

This doesn't seem elegant however.






Connor McDonald
February 20, 2018 - 1:22 am UTC

I'd still be inclined to go with the range-range partitioning you and I both used in our respective test case. Remember that in such an instance, when you have subpartitions that the "parent" partitions are now just logical constructs.

So

where <partition_level_date> between ....

will only scan the data it truly needs to scan (ie, all subpartitions for the restricted set of logical parent partitions), and

where <subpartition_level_date> between ....

will also only scan the the data it needs to scan (ie, restricted set of subpartitions for all logical parent partitions)

Then its a case of how complex you want to go versus performance. You can minimize "wastage" of scans with a much more granular level of partitioning, but perhaps then you would also want to consider a regime where over time you merge older partitions/subpartitions to avoid dictionary pollution with 1000's of partitions etc.

Composite partition using (range-range)

Rajeshwaran Jeyabal, February 20, 2018 - 7:56 am UTC

demo@ORA12C> 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.

demo@ORA12C> alter table t add constraint t_chk check( start_dt < end_dt );

Table altered.

demo@ORA12C> set autotrace traceonly explain
demo@ORA12C> 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        |      |    84 | 12768 |   422   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL    |      |    84 | 12768 |   422   (1)| 00:00:01 |     1 |     2 |
|   2 |   PARTITION RANGE SINGLE|      |    84 | 12768 |   422   (1)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL    | T    |    84 | 12768 |   422   (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'))

demo@ORA12C> set autotrace off
demo@ORA12C>


Team,

Given the fact that the table is partition by END_DT and subpartition by START_DT and having a check constraint enforce that start_dt is less than end_dt.

And the above sql having filter against the start_dt column - why should it hit both the partitions.

Can't the optimizer do something like this using the Metadata and do partition pruning?

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

13-Jan-2015 less than 01-Jan-2016 - condition true - so scan this partition and its relevant sub-partition.

Please correct me, If i am getting it wrong.
Connor McDonald
February 22, 2018 - 2:13 am UTC

The dreaded nulls :-)

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>
SQL> alter table t add constraint t_chk check( start_dt < end_dt );

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 |   492   (1)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE ALL    |      |   101 | 15049 |   492   (1)| 00:00:01 |     1 |     2 |
|   2 |   PARTITION RANGE SINGLE|      |   101 | 15049 |   492   (1)| 00:00:01 |       |       |
|*  3 |    TABLE ACCESS FULL    | T    |   101 | 15049 |   492   (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'))

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: 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>


Wow

Racer I., February 22, 2018 - 9:57 am UTC

Hi,

I'm quite impressed that Oracle actually uses check constraints more complicated than NULL or reference for query predicates :

add constraint t_chk check( start_dt < end_dt );
+
 where start_dt = to_date('13-Jan-2015','dd-mon-yyyy') ;
=
 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'))


regards,
Connor McDonald
February 23, 2018 - 12:35 am UTC

I think its way cool too

on nulls

Rajeshwaran Jeyabal, February 22, 2018 - 10:10 am UTC

The dreaded nulls :-)

Thanks Connor, that helps.

but how did you identify that is due to missing NOT NULL constraints.

dont get any clue about that from "Predicate information" - How did you find that?
Connor McDonald
February 23, 2018 - 12:39 am UTC

Nothing really - because you're asking if we report on the things that we can NOT do, rather than what we could. Its like the optimizer plan showing

"We did NOT use index X because ...."

for every index on every table in a query.

I just thought - what reason could there be that I would still need to scan every partition - what *kind* of data could cause that ? And worked from there.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database