Re: Query tuning experts: Any hope for tuning query with 5+ joins and a sort?

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:aqoapd$m13$1$830fa79d_at_news.demon.co.uk...
>
> Sorry about the late reply.
>
> I've been lecturing in the US, and haven't
> had much spare time for the newsgroup.
>
> Remember that the optimizer is continually
> evolving, and it takes time for new features
> to spread through all the code areas. Depending
> on version, you will find that there are several
> places where special optimisation features don't
> work with new index options (viz function based,
> reverse key, descending column) indexes.
>
> This is just one such example - it does work
> with 9.2 though.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> Next Seminar dates:
> (see http://www.jlcomp.demon.co.uk/seminar.html )
>
> ____USA__________November 7/9 (Detroit)
> ____USA__________November 19/21 (Dallas)
> ____England______November 12/14
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
>
>
>
> william milbratz wrote in message ...
>
> >1) The /*+ ORDERED FIRST_ROWS */ hints work fine when I'm querying
> on
> >'normal' (i.e. btree indexes, nothing fancy...)..but when I used a
> >function based index, Oracle doesn't take the hint..i.e. and does its
> >own sorting. In addition, I've tried using the 'INDEX' and the
> >'INDEX_ASC' hint. No avail.
> >
>
>
>

Well, I've been seriously wrong before. I still owe JL GBP 100 from a previous ill-considered remark, which debt has subsequently kindly been reduced to a beer.

And maybe I will once again put my foot (Foote?) in it. Nevertheless, here goes with a general observation:
Oracle are good at bringing out a load of new features with each release. The problem appears to be that the separate teams responsible for each feature carry out regression testing of their particular feature against some 'common' codebase.
Then, at the eleventh hour, everything is built for the production release. All (most of?) the individual new features do exactly what they say on the tin, but combine them at your peril...

OK! Flame away!

Regards,
Paul Received on Tue Nov 12 2002 - 17:20:49 CST