help in a view

Database
Enthusiast

help in a view

Hello,

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,

sel *

from calendar_dim c

inner join

(

sel * from table1

where reference_dt > 11-11-2012

union all

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.