Date Published: November 20, 2016

How to make partition elimination/pruning to work

If you have situation when partition elimination/pruning does not work, here are couple effective tricks to fix it.


If query derives partition key from sub-query, for example (query where sale_date is a partition key):

select * from sales where sale_date=(select current_date from calendar_table where country_id=1)  

Replace sub-query with DETERMINISTIC function:

create or replace function f_get_date (p_country_id in number) 
return date 
p_date date; 
select current_date into p_date from calendar_table where country_id=p_country_id;  
return p_date; 

select * from sales where sale_date=f_get_date(1);  

In most cases it should not be differences between sub-query and function. But there are multiple bugs and other “grey area”, where oracle cant push sub-query predicates to view, and other optimizer features limited when sub-query is used.

Here are couple examples but its far from complete list:

+ The bug was closed by development as 'Not a bug' with a justification as :   
Subquery in equality can neither be pushed or unnested as sementics will change - 
with equality it is asserted that subquery produces no more than 1 row, otherwise the error must be reported.  

Bug 7655142 : SUB OPTIMAL EXECUTION PLAN WITH SUB QUERY Which was closed as Not a bug , with justification:   
by design we do NOT push filter predicates into the view if view predicate involves a subquery. >>   

Bottom line is – if you try to pull scalar value – use function for it. This is what function is invented to do, and optimizer has a lot more options if function is used instead of sub-query.

“DETERMINISTIC” is important as oracle can call function millions of times during SQL execution.

In some cases init.ora OPTIMIZER_SECURE_VIEW_MERGING=false is needed.


Make sure you do not have “INTERNAL_FUNCTION” in execution plan. If you do – most probably you have implicit data type conversion, like date -> timestamp. that kills partition elimination and needed to be fixed.


Write a Reply or Comment

Your email address will not be published. Required fields are marked *