Re: Merge join and sorts

Merge join does NOT 'always' use sort, it merely merges streams of sorted data.
It often happens to be the case that the two sets of data are generated unsorted and
therefore need to be sorted prior to merge.

However, it is perfectly feasible that
the fastest way of acquiring a set of
data is to access it through an index -
which could conceivably leave it correctly sorted for the merge. The question, therefore, was:

    Why, when both streams have been acquired     in a fashion that guarantees they are both     sorted suitably for merging, does Oracle still     apply a sort operation to the second stream ?

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Now running 3-day intensive seminars
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases


Alex Filonov wrote in message <3C3BDC94.70589908_at_pro-ns.net>...


>

>Answer to your question: Merge join uses sort. Always. If you want to know

why,


>read something about sort algorithms, Knuth for example.

>I don't understand hints though. If you want merge join, index search just

>makes it slower, much slower, about 3 times. If you want to use index scan

on


>join fields, either remove all hints or use only one index hint, on the

smaller


>table. The most efficient algorithm here would be full table scan on bigger

>table and index scan on a smaller one.

>