non equi join

From: derek <dereksmi_at_gmail.com>
Date: 31 May 2005 17:43:03 -0700
Message-ID: <1117586583.740005.117950@o13g2000cwo.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. Received on Tue May 31 2005 - 19:43:03 CDT