Re: Monster sql statement need optimization

Thanks very much guys for taking a stab at this. Here's some more backgound info.

This is from a production application that is considered to be "right" so I can't change the sql and did not write it. Good point about the outer join though. I'm not dba so I can't change init.ora parameters but could request a change but would want to be fairly sure it would "work" before doing so. I could turn off parallel query if I wanted to.

I got this to run today in less than an hour by switching the order of tables b and c in the where clause and adding an ordered hint. This caused tables a and c to get joined first in the plan. After that a unique index on table c got used and life was much better. How did I know to do this? I had another similar query that was running faster and I noticed in the plan that those two tables were joined first.

Go figure. I really don't know how anyone would know to try this otherwise. With monster tables and monster queries, building indexes in all the obvious places and having fresh stats is not always enough.  I say there's still a bit of art, magic or whatever you want to call it in what we do.

Now if I can get permission to add the hint to the source code and recompile I'll be set.

Gene Hubert
Durham, NC

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3CEE58B7.CD96E771_at_exesolutions.com>...
> Herman de Boer wrote:
>
> > Gene,
> >
> > What might help here *A LOT* is a bigger hash_area_size. For this
> > amount of records, a minimal value of 10Mb seems reasonable. If
> > possible, set it to 80 Mbyte.
> >
> > I think the plan itself is quite good...
> >
> > A few things are worth mentioning:
> > * Table f is outer joined to table a on territory_number, but not
> > on a.evnt_dates f.tart_date / f.end_date. This might give
> > wrong output.
> > * The ":Q..." words indicate that parallell query (or distributed
> > query) is involved. Possibly that some tables have a degree set.
> > This might not be benificiary on your system. Use:
> > "alter table ... noparallel".
> >
> > Kind Regards,
> >
> > Herman de Boer
> > sr. consultant
> > IT Consultancy Group bv.
> >
>
> In addition I wonder about the following:
>
> There seems to be a lot of nvl(rtrim(field_name,' ') going on.
>
> Are the fields CHAR rather than VARCHAR2?
>
> And what is really being accomplished by the NVL?
>
> Daniel Morgan
Received on Fri May 24 2002 - 20:50:11 CDT