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 DETERMINISTIC is p_date date; begin select current_date into p_date from calendar_table where country_id=p_country_id; return p_date; end; / 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:
BUG 7260683 - SUBQUERY PREDICATE NOT PUSHED INTO UNION-ALL VIEW + 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.