Re: non equi-join

derek wrote:
> 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?

It doesn't. It indeed starts the inner row source multiple times, but it does starts it multiple times for equi-join as well. The number of rows processed reflects the number of rows that match the predicate. Received on Wed Jun 01 2005 - 04:21:55 CEST