Karen Morton wrote:
>
> Stephanie,
Cough, cough.
> I've ran into the exact same thing that statistics don't always tell the
> real story and that the bottom-line has to be elapsed time.
>
> I am curious however as to how you wrote the best performing (fastest)
> query. I have a client who uses the exact same structure and methodology
> you mention (innate tree structure, views with outer joins, etc) and they
> have some pretty long and nasty queries (I see your 176 step execution plan
> and raise you to 298!). I've had pretty decent success re-writing these
> monsters but would be interested in hearing your different approaches to
> re-writing such queries to see if I can pick up a few ideas I hadn't thought
> of. Any generic ideas or methods you use might prove helpful to many.
>
> Thanks,
> Karen
>
> Karen Morton
> Morton Consulting
> [EMAIL PROTECTED]
>
Karen,
The only effective method I know is to take an axe and cut everything
which is not strictly necessary. More often than not, when you replace a
view by its query, you realize that you only need some of the columns of
the view, and that some (possibly not fantastically efficient) joins in
the view can be happily disposed of. You also find useless joins outside
of views.
In the query mentioned above, the hard-core was a
start with a_id in
(select a.a_id
from a, b, c, d
where b.a_id = a.a_id
and <condition on b>
and c.b_id = b.b_id
and <condition on c>
and d.c_id = c.c_id
and <condition on d>
union
select a.a_id
from a, b, e, f
where b.a_id = a.a_id
and <condition on b>
and e.b_id = b.b_id
and <condition on e>
and f.e_id = e.e_id
and <condition on f>
union
...)
Why joining on a, since the primary key of a (which is what we want) can
be found as foreign key in b? Why repeat the condition on b in the
second part of the union ? And so on.
Once everything was reduced to something I could grasp and constituent
pieces, it was far easier to twitch the code here and there and get
decent results.
'Far easier' is of course a way of speaking. I would be glad if there
were recipes. I usually start with something twice as bad as the
original query, which a bit of additional tuning takes to about ten
times slower than what you want to improve. At which point, about 2
hours after having started, I am beginning to get a better feeling of
the data, take another cup of coffee (and possibly another one), scrap
everything I have done so far and get some results. Another example
today (3rd of my 4 customers this week) where, starting with 170,000 or
about LIOs I have managed after much effort to peak at 500,000 before
reaching the finish line at 2,000. Phew, reputation saved, but it was
hot.
HTH,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
