Re: non equi-joins

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_at_gmail.com> wrote in message 
news:1117586398.259896.157200_at_g14g2000cwa.googlegroups.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.

>