Re: full outer join:can somebody tell me why I receive these results?!

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Thu, 21 Feb 2008 13:00:34 -0800 (PST)
Message-ID: <bd4e2cfc-5188-498d-8c8c-1f48e88cbcdc@v3g2000hsc.googlegroups.com>


On Feb 21, 11:09 am, unebo..._at_hotmail.com wrote:
> Why do I get these results when I execute the script following this
> text?
>
> In strategy A results I dont understand  why T2 rows are repeated with
> n2 being null on the repeated rows...
>
> In strategy B results I dont understand where T2 'e' row is? Why can't
> I get it in the results?
> Why T2 'd' row is doubled with a 3 and 4 n2 value?
>
> I execute this on a 9i installation
>
> thanks in advance!!!
>
> strategy A
> N1                   COL1 N2                 COL2
> ---------------------- ----   ---------------------- ----
> 1                      a    1                      a
> 2                      b    2                      b
> 3                      c    3                      d
>                                                      d
>                                                      a
>                                                      b
>                                                      e
> strategy B
> N1                   COL1 N2               COL2
> ---------------------- ---- ---------------------- ----
> 1                      a    1                      a
> 2                      b    2                      b
> 3                      c    3                      d
>                              4                      d
>
> begin
>    execute immediate 'drop table t1';
> end;
> /
>
> begin
>    execute immediate 'drop table t2';
> end;
> /
>
> create table t1(col1 char(1));
> create table t2(col2 char(1));
>
> insert into t1 values ('a');
> insert into t1 values ('c');
> insert into t1 values ('b');
>
> insert into t2 values ('e');
> insert into t2 values ('b');
> insert into t2 values ('a');
> insert into t2 values ('d');
>
> select * from t1;
> select * from t2;
>
> /* strategy  A */
> select *
> from (select row_number() over(order by col1) as n1
>            , col1
>       from t1
>      )
>      full outer join
>      (select row_number() over(order by col2) as n2
>            , col2
>       from t2
>      )
>      on n1=n2
> ;
>
> /* strategy B */
> select *
> from (select rownum as n1
>            , col1
>       from (select *
>             from t1
>             order by col1
>            )
>      )
>      full outer join
>      (select rownum as n2
>            , col2
>       from (select *
>             from t2
>             order by col2
>            )
>      )
>      on n2=n1
> ;

Thanks for posting the SQL statements to re-create the test. I believe that there are bugs that occur in the 9i version of the ANSI joins on occasion, and this may be one such case where the execution plan is transformed, resulting in ROWNUM being evaluated at the wrong time. You might look at the explain plans for the two examples. Here are the explain plans from Oracle 10.2.0.2: /* strategy A */


| Id  | Operation                | Name | Starts | E-Rows | A-Rows |
--------------------------------------------------------------------
|   1 |  VIEW                    |      |      1 |      7 |      4 |
|   2 |   UNION-ALL              |      |      1 |        |      4 |
|*  3 |    HASH JOIN OUTER       |      |      1 |      3 |      3 |
|   4 |     VIEW                 |      |      1 |      3 |      3 |
|   5 |      COUNT               |      |      1 |        |      3 |
|   6 |       VIEW               |      |      1 |      3 |      3 |
|   7 |        SORT ORDER BY     |      |      1 |      3 |      3 |
|   8 |         TABLE ACCESS FULL| T1   |      1 |      3 |      3 |
|   9 |     VIEW                 |      |      1 |      4 |      4 |
|  10 |      COUNT               |      |      1 |        |      4 |
|  11 |       VIEW               |      |      1 |      4 |      4 |
|  12 |        SORT ORDER BY     |      |      1 |      4 |      4 |
|  13 |         TABLE ACCESS FULL| T2   |      1 |      4 |      4 |
|* 14 |    HASH JOIN ANTI        |      |      1 |      4 |      1 |
|  15 |     VIEW                 |      |      1 |      4 |      4 |
|  16 |      COUNT               |      |      1 |        |      4 |
|  17 |       VIEW               |      |      1 |      4 |      4 |
|  18 |        SORT ORDER BY     |      |      1 |      4 |      4 |
|  19 |         TABLE ACCESS FULL| T2   |      1 |      4 |      4 |
|  20 |     VIEW                 |      |      1 |      3 |      3 |
|  21 |      COUNT               |      |      1 |        |      3 |
|  22 |       VIEW               |      |      1 |      3 |      3 |
|  23 |        SORT ORDER BY     |      |      1 |      3 |      3 |
|  24 |         TABLE ACCESS FULL| T1   |      1 |      3 |      3 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):


   3 - access("N2"="N1")
  14 - access("N2"="N1")

/* strategy B */



| Id | Operation | Name | Starts | E-Rows | A-Rows |
|   1 |  VIEW                  |      |      1 |      7 |      4 |
|   2 |   UNION-ALL            |      |      1 |        |      4 |
|*  3 |    HASH JOIN OUTER     |      |      1 |      3 |      3 |
|   4 |     VIEW               |      |      1 |      3 |      3 |
|   5 |      WINDOW SORT       |      |      1 |      3 |      3 |
|   6 |       TABLE ACCESS FULL| T1   |      1 |      3 |      3 |
|   7 |     VIEW               |      |      1 |      4 |      4 |
|   8 |      WINDOW SORT       |      |      1 |      4 |      4 |
|   9 |       TABLE ACCESS FULL| T2   |      1 |      4 |      4 |
|* 10 |    HASH JOIN ANTI      |      |      1 |      4 |      1 |
|  11 |     VIEW               |      |      1 |      4 |      4 |
|  12 |      WINDOW SORT       |      |      1 |      4 |      4 |
|  13 |       TABLE ACCESS FULL| T2   |      1 |      4 |      4 |
|  14 |     VIEW               |      |      1 |      3 |      3 |
|  15 |      WINDOW SORT       |      |      1 |      3 |      3 |
|  16 |       TABLE ACCESS FULL| T1   |      1 |      3 |      3 |
------------------------------------------------------------------

Predicate Information (identified by operation id):


   3 - access("N1"="N2")
  10 - access("N1"="N2")

You will note that the two execution plans above look very similar.

Your first query with a hint to prohibit query transformation: select /*+ NO_QUERY_TRANSFORMATION */
*
from (select rownum as n1

           , col1
      from (select *
            from t1
            order by col1
           )
     )
     full outer join
     (select rownum as n2
           , col2
      from (select *
            from t2
            order by col2
           )
     )
     on n2=n1;

---------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |
---------------------------------------------------------------------
|   1 |  VIEW                     |      |      1 |     13 |      4 |
|   2 |   UNION-ALL               |      |      1 |        |      4 |
|   3 |    NESTED LOOPS OUTER     |      |      1 |     12 |      3 |
|   4 |     VIEW                  |      |      1 |      3 |      3 |
|   5 |      COUNT                |      |      1 |        |      3 |
|   6 |       VIEW                |      |      1 |      3 |      3 |
|   7 |        SORT ORDER BY      |      |      1 |      3 |      3 |
|   8 |         TABLE ACCESS FULL | T1   |      1 |      3 |      3 |
|   9 |     VIEW                  |      |      3 |      4 |      3 |
|* 10 |      VIEW                 |      |      3 |      4 |      3 |
|  11 |       COUNT               |      |      3 |        |     12 |
|  12 |        VIEW               |      |      3 |      4 |     12 |
|  13 |         SORT ORDER BY     |      |      3 |      4 |     12 |
|  14 |          TABLE ACCESS FULL| T2   |      3 |      4 |     12 |
|* 15 |    FILTER                 |      |      1 |        |      1 |
|  16 |     VIEW                  |      |      1 |      4 |      4 |
|  17 |      COUNT                |      |      1 |        |      4 |
|  18 |       VIEW                |      |      1 |      4 |      4 |
|  19 |        SORT ORDER BY      |      |      1 |      4 |      4 |
|  20 |         TABLE ACCESS FULL | T2   |      1 |      4 |      4 |
|* 21 |     VIEW                  |      |      4 |      3 |      3 |
|  22 |      COUNT                |      |      4 |        |      9 |
|  23 |       VIEW                |      |      4 |      3 |      9 |
|  24 |        SORT ORDER BY      |      |      4 |      3 |      9 |
|  25 |         TABLE ACCESS FULL | T1   |      4 |      3 |     12 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):


  10 - filter("N2"="N1")
  15 - filter( IS NULL)
  21 - filter("N1"=:B1)

What happens if you supply the hint on your version of Oracle?

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Thu Feb 21 2008 - 15:00:34 CST

Original text of this message