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