Re: Slow query on date field via views

On Wed, 10 May 2006 10:07:09 +0800, AcCeSsDeNiEd <dillon_at_SpamMinuSaccessdenied.darktech.org> wrote:

>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

Did you get the explain plans and compare them? Please get the explain plans and post them here.

--
Sybrand Bakker, Senior Oracle DBA