Hello Connor/Chris,
I am trying to achieve partition pruning based on columns that are related to partitioning key column using a CHECK constraint but am bit unsure about the inconsistent results that I am witnessing and am trying to understand the reasons. While I am getting partition pruning by using a column in WHERE predicate that is not partitioning key but is related to partitioning key column using CHECK constraint when defined in one way, it does not appear to work when I switch the partitioning key column and CHECK constraint.
Can you please help me to understand what am I missing? This works the same on 19.12 as well as 11.2.0.4
SQL>select banner_full from v$version ;
BANNER_FULL
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Oracle Database 19c EE Extreme Perf Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
1 row selected.
SQL>REM Cleanup
SQL>begin
2 for rec in (select table_name from user_tables where table_name = 'EVENT_PART')
3 loop
4 execute immediate 'drop table event_part purge' ;
5 end loop ;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>REM Create partitioned table on END_DATE
SQL>create table event_part (event_id int primary key, start_date date not null, end_date date not null, event_desc VARCHAR2(1000)) partition by range (end_date) interval (numtodsinterval(1, 'day')) (partition ep0 values less than (to_date('01-APR-2022','DD-MON-YYYY')));
Table created.
SQL>
SQL>REM Populate partitioned table for 100 days (generating 100 partitions)
SQL>insert into event_part
2 select level, (sysdate + level + (level/(24*60))), (sysdate + level + (level/(24*60)) + (dbms_random.value(2,49)/(24*60*60))), dbms_random.string('a', 300)
3 from dual
4 connect by level <= 100 ;
100 rows created.
SQL>
SQL>REM Define CHECK constraint to asscoiate START_DATE with END_DATE
SQL>alter table event_part add CONSTRAINT chk_start_end_dt CHECK (end_date between start_date and (start_date + (1/24))) ;
Table altered.
SQL>
SQL>REM Gather Stats
SQL>exec dbms_stats.gather_table_stats(ownname => sys_context('USERENV','CURRENT_USER'), tabname => 'EVENT_PART', no_invalidate => FALSE) ;
PL/SQL procedure successfully completed.
SQL>
SQL>REM Test SQL 1 - WHERE predicate on END_DATE and partition pruning achieved
SQL>select /*+ GATHER_PLAN_STATISTICS */ * from event_part where end_date between to_date('01-APR-2022','DD-MON-YYYY') and to_date('05-APR-2022','DD-MON-YYYY') ;
no rows selected
SQL>
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +PARTITION')) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cavx0905hxycm, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from event_part where end_date
between to_date('01-APR-2022','DD-MON-YYYY') and
to_date('05-APR-2022','DD-MON-YYYY')
Plan hash value: 1218157282
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 0 |00:00:00.01 |
| 1 | PARTITION RANGE ITERATOR | | 1 | 1 | 2 | 6 | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS STORAGE FULL| EVENT_PART | 0 | 1 | 2 | 6 | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("END_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"END_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("END_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"END_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
INTERNAL_FUNCTION("START_DATE")+.0416666666666666666666666666666666666667>=TO_DATE(' 2022-04-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "START_DATE"<=TO_DATE(' 2022-04-05 00:00:00',
'syyyy-mm-dd hh24:mi:ss')))
27 rows selected.
SQL>
SQL>REM Test SQL 2 - WHERE predicate on START_DATE and no partition pruning achieved (Why ??)
SQL>select /*+ GATHER_PLAN_STATISTICS */ * from event_part where start_date between to_date('01-APR-2022','DD-MON-YYYY') and to_date('05-APR-2022','DD-MON-YYYY') ;
no rows selected
SQL>
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +PARTITION')) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8n2qrrwm99mz6, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from event_part where start_date
between to_date('01-APR-2022','DD-MON-YYYY') and
to_date('05-APR-2022','DD-MON-YYYY')
Plan hash value: 1218157282
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 0 |00:00:00.98 | 3600 | 1600 | | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 1 | 2 |1048575| 0 |00:00:00.98 | 3600 | 1600 | | | |
|* 2 | TABLE ACCESS STORAGE FULL| EVENT_PART | 100 | 1 | 2 |1048575| 0 |00:00:00.98 | 3600 | 1600 | 1025K| 1025K| 1029K (0)|
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("START_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "START_DATE">=TO_DATE(' 2022-04-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "END_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("START_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "START_DATE">=TO_DATE(' 2022-04-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND "END_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
INTERNAL_FUNCTION("START_DATE")+.0416666666666666666666666666666666666667>=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
25 rows selected.
SQL>
SQL>REM Cleanup
SQL>begin
2 for rec in (select table_name from user_tables where table_name = 'EVENT_PART')
3 loop
4 execute immediate 'drop table event_part purge' ;
5 end loop ;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>REM Create partitioned table on START_DATE
SQL>create table event_part (event_id int primary key, start_date date not null, end_date date not null, event_desc VARCHAR2(1000)) partition by range (start_date) interval (numtodsinterval(1, 'day')) (partition ep0 values less than (to_date('01-APR-2022','DD-MON-YYYY')));
Table created.
SQL>
SQL>REM Populate partitioned table for 100 days (generating 100 partitions)
SQL>insert into event_part
2 select level, (sysdate + level + (level/(24*60))), (sysdate + level + (level/(24*60)) + (dbms_random.value(2,49)/(24*60*60))), dbms_random.string('a', 300)
3 from dual
4 connect by level <= 100 ;
100 rows created.
SQL>
SQL>REM Define CHECK constraint to asscoiate START_DATE with END_DATE (same condition as before but defined in a different way)
SQL>alter table event_part add CONSTRAINT chk_start_end_dt CHECK (start_date between (end_date - (1/24)) and end_date) ;
Table altered.
SQL>
SQL>REM Gather Stats
SQL>exec dbms_stats.gather_table_stats(ownname => sys_context('USERENV','CURRENT_USER'), tabname => 'EVENT_PART', no_invalidate => FALSE) ;
PL/SQL procedure successfully completed.
SQL>
SQL>REM Test SQL 1 - WHERE predicate on START_DATE and partition pruning achieved
SQL>select /*+ GATHER_PLAN_STATISTICS */ * from event_part where start_date between to_date('01-APR-2022','DD-MON-YYYY') and to_date('05-APR-2022','DD-MON-YYYY') ;
no rows selected
SQL>
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +PARTITION')) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 8n2qrrwm99mz6, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from event_part where start_date
between to_date('01-APR-2022','DD-MON-YYYY') and
to_date('05-APR-2022','DD-MON-YYYY')
Plan hash value: 1218157282
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 0 |00:00:00.01 |
| 1 | PARTITION RANGE ITERATOR | | 1 | 1 | 2 | 6 | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS STORAGE FULL| EVENT_PART | 0 | 1 | 2 | 6 | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("START_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"START_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"END_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("START_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"START_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"END_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
INTERNAL_FUNCTION("END_DATE")-.0416666666666666666666666666666666666667<=TO_DATE(' 2022-04-05
00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
28 rows selected.
SQL>
SQL>REM Test SQL 2 - WHERE predicate on END_DATE and partition pruning achieved (How ??)
SQL>select /*+ GATHER_PLAN_STATISTICS */ * from event_part where end_date between to_date('01-APR-2022','DD-MON-YYYY') and to_date('05-APR-2022','DD-MON-YYYY') ;
no rows selected
SQL>
SQL>select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST +PARTITION')) ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID cavx0905hxycm, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS */ * from event_part where end_date
between to_date('01-APR-2022','DD-MON-YYYY') and
to_date('05-APR-2022','DD-MON-YYYY')
Plan hash value: 1218157282
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 0 |00:00:00.01 |
| 1 | PARTITION RANGE ITERATOR | | 1 | 1 | 1 | 6 | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS STORAGE FULL| EVENT_PART | 1 | 1 | 1 | 6 | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("END_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"END_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"START_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
filter(("END_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"END_DATE">=TO_DATE(' 2022-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"START_DATE"<=TO_DATE(' 2022-04-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
INTERNAL_FUNCTION("END_DATE")-.0416666666666666666666666666666666666667<=TO_DATE(' 2022-04-05
00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
28 rows selected.
SQL>
SQL>spool off
The database is partition pruning in all cases.
To see what's going on, it helps to search for dates in the middle of the date range.
So I've searched for dates in May. Here are the plans for the queries NOT filtering on the partitioning column:
REM Test SQL 2 - WHERE predicate on START_DATE and no partition pruning achieved (Why ??)
select /*+ GATHER_PLAN_STATISTICS */ * from event_part
where start_date between to_date('01-MAY-2022','DD-MON-YYYY') and to_date('05-MAY-2022','DD-MON-YYYY') ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID dh0h3uwskjssd, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS / * from event_part where
start_date between to_date('01-MAY-2022','DD-MON-YYYY') and
to_date('05-MAY-2022','DD-MON-YYYY')
Plan hash value: 1218157282
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 4 |00:00:00.01 | 2694 | 1200 |
| 1 | PARTITION RANGE ITERATOR| | 1 | 6 | 32 |1048575| 4 |00:00:00.01 | 2694 | 1200 |
|* 2 | TABLE ACCESS FULL | EVENT_PART | 75 | 6 | 32 |1048575| 4 |00:00:00.58 | 2694 | 1200 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("START_DATE"<=TO_DATE(' 2022-05-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"START_DATE">=TO_DATE(' 2022-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE">=TO_DATE(' 2022-05-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND INTERNAL_FUNCTION("START_DATE")+.0416666666666666666666666666666666666667
>=TO_DATE(' 2022-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
REM Test SQL 2 - WHERE predicate on END_DATE and partition pruning achieved (How ??)
select /*+ GATHER_PLAN_STATISTICS */ * from event_part
where end_date between to_date('01-MAY-2022','DD-MON-YYYY') and to_date('05-MAY-2022','DD-MON-YYYY') ;
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 26wyqvmntbavg, child number 0
-------------------------------------
select /*+ GATHER_PLAN_STATISTICS / * from event_part where end_date
between to_date('01-MAY-2022','DD-MON-YYYY') and
to_date('05-MAY-2022','DD-MON-YYYY')
Plan hash value: 1218157282
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 4 |00:00:00.25 | 1080 | 480 |
| 1 | PARTITION RANGE ITERATOR| | 1 | 6 | 1 | 36 | 4 |00:00:00.25 | 1080 | 480 |
|* 2 | TABLE ACCESS FULL | EVENT_PART | 31 | 6 | 1 | 36 | 4 |00:00:00.25 | 1080 | 480 |
--------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("END_DATE"<=TO_DATE(' 2022-05-05 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "END_DATE">=TO_DATE('
2022-05-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "START_DATE"<=TO_DATE(' 2022-05-05 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND INTERNAL_FUNCTION("END_DATE")-.0416666666666666666666666666666666666667<=TO_DATE(' 2022-05-05
00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
Notice that in both cases the plan is either stopping or starting pruning on partitions in the 30s.
When partitioning on END_DATE with this constraint and query:
alter table event_part
add constraint chk_start_end_dt check (
end_date between start_date and (start_date + (1 / 24))
);
select /*+ GATHER_PLAN_STATISTICS */ * from event_part
where start_date between to_date('01-MAY-2022','DD-MON-YYYY')
and to_date('05-MAY-2022','DD-MON-YYYY');
The optimizer knows that:
END_DATE >= START_DATE
START_DATE >= to_date('01-MAY-2022','DD-MON-YYYY')
So can infer:
END_DATE >= to_date('01-MAY-2022','DD-MON-YYYY')
You can see this in the predicates section of the plan.
While in theory it could use similar logic to infer the upper value (and thus partition) for END_DATE, this involves rearranging the formulas. This is something the optimizer doesn't do.
Similar logic applies in reverse when partitioning on START_DATE.
So given these examples, it only prunes the upper or lower bound for the partitions (depending on which column you've partitioned on).