Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Slow query on date field via views - weekly_r_view_plan_table.xls (0/1)
Sybrand Bakker <post..._at_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 Received on Wed May 17 2006 - 20:18:53 CDT