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

Sun, 3 Nov 2002 23:32:55 +1000, Richard Foote said (and I quote):
>
> Therefore using an index to simply avoid a sort is not always going to cut
> it for the CBO. Despite warm fuzzy feelings to the contrary, using the full
> table scan and performing a somewhat expensive sort could very well be a
> cheaper option than using the index *if* a largish portion of the table is
> being accessed (and largish may not be as large as some may suggest).
>

That, I doubt. I need proof to believe it. No matter how good Oracle's sort mechanism may be (and it isn't very good), it is impossible for a sort after a full scan to take less time than a retrieval in final order by index.

Yeah, I know about all the benchmarks. Those are for simple retrieval of more than 25% of THE ROWS. Not a sort, as well.

And in most cases one may well find that a retrieval by index is actually a lot faster than the benchmarks "prove", if the rows are actually stored in the blocks in the sequence of the index keys. It happens a lot more often than we think.

There is such a thing as sort/merge overhead and that is not being taken into account in the case you mention. The sorted-by-index retrieval is hardly more expensive than a final merge.

-- 
Cheers
Nuno Souto
nsouto_at_optusnet.com.au.nospam