469,927 Members | 1,617 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

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
May 10 '06 #1
2 11211
Sybrand Bakker <post...@sybrandb.demon.nl> 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
May 16 '06 #2
Sybrand Bakker <post...@sybrandb.demon.nl> 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
May 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by YoBro | last post: by
6 posts views Thread by Umar Farooq | last post: by
5 posts views Thread by krystoffff | last post: by
3 posts views Thread by Antoni | last post: by
2 posts views Thread by AcCeSsDeNiEd | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.