Re: non equi-joins

From: <fitzjarrell_at_cox.net>
Date: 31 May 2005 19:58:14 -0700
Message-ID: <1117594694.317107.8550@z14g2000cwz.googlegroups.com>

dereksmi_at_gmail.com 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? Is there a way to
> avoid it? Any help would be greatly appreciated!!
>
> best regards,
> Derek.

Why do you post the same message multiple times? Such behaviour does not bode well for a useful response. Post your question ONCE, and in properl English, and wait; should someone be able to provide enlightenment, they will.

I've created and loaded two tables, t1 and t2, with sequential data. The query plan I generate for your query is:

| 0 | SELECT STATEMENT | | 1 | 26 | 146 |
| 1 | SORT AGGREGATE | | 1 | 26 | |
| 2 | MERGE JOIN | | 5640K| 139M| 146 |
| 3 | SORT JOIN | | 10001 | 126K| 69 |
| 4 | TABLE ACCESS FULL| T1 | 10001 | 126K| 2 |

|*  5 |    SORT JOIN         |             | 11280 |   143K|    77 |


|   6 |     TABLE ACCESS FULL| T2          | 11280 |   143K|     2 |

--------------------------------------------------------------------

Additinoal information I obtained:

Predicate Information (identified by operation id):

5 - access("T2"."A">"T1"."A")

filter("T2"."A">"T1"."A")

Modifying your query to remove the hint and generating another plan produces:

| 0 | SELECT STATEMENT | | | | |
| 1 | SORT AGGREGATE | | | | |
| 2 | NESTED LOOPS | | | | |
| 3 | TABLE ACCESS FULL | T2 | | | |
|* 4 | TABLE ACCESS FULL | T1 | | | |

Your hint, it appears, is causing you grief, as the un-hinted query performs as you'd expect. Notice, too, no costing information is present in this plan. dbms_xplan.display reveals this is a rule-based optimisation.

Generating statistics on these tables and running your query again:

| 0 | SELECT STATEMENT | | 1 | 6 | 98 |
| 1 | SORT AGGREGATE | | 1 | 6 | |
| 2 | MERGE JOIN | | 5000K| 28M| 98 |
| 3 | SORT JOIN | | 10000 | 30000 | 49 |
| 4 | TABLE ACCESS FULL| T1 | 10000 | 30000 | 9 |

|*  5 |    SORT JOIN         |             | 10000 | 30000 |    49 |


|   6 |     TABLE ACCESS FULL| T2          | 10000 | 30000 |     9 |

--------------------------------------------------------------------

No real difference except the cost. Generating another plan for the un-hinted query (this time using the CBO instead of the RBO):

|*  5 |    SORT JOIN         |             | 10000 | 30000 |    49 |


|   6 |     TABLE ACCESS FULL| T2          | 10000 | 30000 |     9 |

--------------------------------------------------------------------

Now we know it's the CBO creating this plan, as both the hinted and un-hinted queries produce the same execution path.

All of this was run on Oracle 9.2.0.6, on Solaris 8.

I am not aware of any CBO anomalies in 9.2.0.6, and I haven't found time nor space to install 10g. And, I'm hoping Jonathan Lewis can shed some light on these plans. I do not know the Oracle internals that well.

David Fitzjarrell Received on Tue May 31 2005 - 21:58:14 CDT