Slow query on date field via views

I have several views with unions, outer joints and calculations that later combine to find a "final" report view.
This was to make things easier for the web-based report programmer to just pull the values from this view.

If I query the view by the id/index/primary_key, the view runs fine (about 3secs)

e.g: Select *

     from final_view 
     where Id_Index in (123, 456, 789)

However, when the view is queried by dates, it is slow. More like impossible actually 'cos the temp table space gets filled up (32GB!) and oracle returns an error about
being unable to extend.

E.g: Select *

     from final_view 
     where Submission_Date=to_date('01-01-2005', 'dd-mm-yyyy')

I tried indexing the Sub_Date but it didn't help.

But if I run the query directly on the table in question (with the sub_date), the query works fine & fast,
with or without the indexing.

Anyone got any pointers?

I'm using oracle 9i.
And the 3 big tables which the view taps on have about 100,000 records each.

Thanks

To e-mail, remove the obvious Received on Tue May 09 2006 - 21:07:09 CDT