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

1.

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.

2.

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.