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 Thu May 18 2006 - 03:18:53 CEST