Slow query on date field via views

[AcCeSsDeNiEd]

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

[AcCeSsDeNiEd]

Sybrand Bakker <[e-mail]> wrote:

Did you get the explain plans and compare them?
Please get the explain plans and post them here. --
Sybrand Bakker, Senior Oracle DBA

Ok, after much fiddling around and running sqlanalyze I was able to optimize the query pretty well.
It told me to use a "union all" instead of "union". I hope I don't end up with dupe rows.
That did help pretty much.

However, it still took me 25 secs to run the below query:

Select w.*
from client.weekly_report_details w
where w.submission_date between to_date('17-Apr-2005', 'DD-MON-YYYY') and to_date('30-Apr-2005',
'DD-MON-YYYY')
and w.adv_id=42

If I use the index then it runs about 2 secs:

Select w.*
from client.weekly_report_details w
where w.submission_date between to_date('17-Apr-2005', 'DD-MON-YYYY') and to_date('30-Apr-2005',
'DD-MON-YYYY')
and w.policy_id in (63771, 63923, 63924)

I found more nasties in the jsp report the vendor wrote.
He was using to_char() on the date fields.

My plan table seems to be pretty big :)
I have no idea how to read it.

I hope a small binary does not irk people here

Thanks

To e-mail, remove the obvious

[AcCeSsDeNiEd]

Sybrand Bakker <[e-mail]> wrote:

Did you get the explain plans and compare them?
Please get the explain plans and post them here. --
Sybrand Bakker, Senior Oracle DBA

Ok, after much fiddling around and running sqlanalyze I was able to optimize the query pretty well.
It told me to use a "union all" instead of "union". I hope I don't end up with dupe rows.
That did help pretty much.

However, it still took me 25 secs to run the below query:

Select w.*
from client.weekly_report_details w
where w.submission_date between to_date('17-Apr-2005', 'DD-MON-YYYY') and to_date('30-Apr-2005',
'DD-MON-YYYY')
and w.adv_id=42

If I use the index then it runs about 2 secs:

Select w.*
from client.weekly_report_details w
where w.submission_date between to_date('17-Apr-2005', 'DD-MON-YYYY') and to_date('30-Apr-2005',
'DD-MON-YYYY')
and w.policy_id in (63771, 63923, 63924)

I found more nasties in the jsp report the vendor wrote.
He was using to_char() on the date fields.

My plan table seems to be pretty big :)
I have no idea how to read it.

I hope a small binary does not irk people here

Thanks

To e-mail, remove the obvious