The system hasn't sorted multiple times.

Your assumption about how the mechanism
ought to work is correct, and does describe
what happens. You can check this by looking
at you session stats, or doing an autotrace.

You will find that you have done 2 sorts, and
sorted 20,000 rows.

For your example, Oracle sorts both inputs,
and writes the second output to 'TEMP'. Then
for each row in the first output it will find a start
and end point in the second output and join.

The 49995000 beside the second sort line:
> 49995000 SORT JOIN (cr=38 pr=0 pw=0 time=50031081 us)
is the number of rows supplied as a result
of that sorted row source - but Oracle
has only sorted it once, than kept
're-supplying' the required results.

Ideally there ought to be a line which is
the parent of the sort operation that is
an 'ordered selection output' line (or
something similar) so that you could
see that the sort generated 10,000 rows,
and then it's output was scanned in order
many times.



--
Regards

Jonathan Lewis

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

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005






<dereksmi@gmail.com> wrote in message
news:1117586398.259896.157200@g14g2000cwa.googlegr oups.com...
>
>
> Hi,
>
> I have two tables t1 and t2 containing 10K tuples each. I tried to do a
> non equi-join on these tables using the following query.
>
> select /*+ ORDERED USE_MERGE (t2 t1)*/count(*) from test2 t2, test1 t1
> where t2.a > t1.a;
>
> A simple implementaion of Sort merge based non equi join would have
> sorted both relations and iterated through the outer relation returning
> all the matching tuples. However when I checked the execution plan made
> by Oracle. this is wot it shows
>
>
> Rows Row Source Operation
> ------- ---------------------------------------------------
> 1 SORT AGGREGATE (cr=76 pr=0 pw=0 time=26809424 us)
> 49995000 MERGE JOIN (cr=76 pr=0 pw=0 time=150014986 us)
> 10000 SORT JOIN (cr=38 pr=0 pw=0 time=26064 us)
> 10000 TABLE ACCESS FULL TEST2 (cr=38 pr=0 pw=0 time=10086 us)
> 49995000 SORT JOIN (cr=38 pr=0 pw=0 time=50031081 us)
> 10000 TABLE ACCESS FULL TEST1 (cr=38 pr=0 pw=0 time=10055 us)
>
>
> Why does it sort the inner relation multiple times? Is there a way to
> avoid it? Any help would be greatly appreciated!!
>
> best regards,
> Derek.
>