I would like to thank you in advance for the help.
I will provide a example of my real situation.If it will not be possible to solve it,i may provide the ddl,plan and so on.
I have a view named view_1.
It is implemented as below,
from calendar_dim c
sel * from table1
where reference_dt > 11-11-2012
sel * from view2
) as result_
on result_.date_clm = c.date_clm
The view2 , contains aggregates-group by and functions(extract...and so on) & a where condition where reference_dt < 11-11-2012
So,if i will need the result for a specific date , it works perfect.
Meaning,it passes the reference date inside the view, it does partition elemination and it returns the result.
for example sel * from view1 where reference_dt = sel * from view1 where reference_dt = '10-10-2012'**
The problem starts when i want to do some aggregations on spesific columns , for the last date of the previous month & some other calculations on other columns for the specified date.Meaning , If i will set the date as '10-10-2012' , i need in one column to aggregate on '30-09-2012'.
I manage to handle this situation , by instead of joining to calendar , i join with a new result of the calendar after inner joining by itself in order to get the last previous month date.
The problem is that on plan,it pass correct the date specified , but when it tries for the aggregation column of previous month last date , it does full table scan?
Any ideas for this behaviour?
Thank you very much.