Archives of the TeradataForum

Message Posted: Tue, 18 Dec 2007 @ 11:14:01 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Victor, Jonathan,

there might be a little confusion by reading the manual...

The manual is referring to the inner table which in in

from A left table B

table B.

A Where-condition on a column B table which is not covering the possibility that the join can not be done (which results in NULLS for the table B columns) will implicitly result in an inner join. As an outer join is more complex to perform the optimiser is smart and not even pretend to do.

The example below shows the difference...

     explain
     select *
     from a as a
              left outer join
              b as b
           on a.a = b.b
     where a.a = 1;

        3) We do a single-AMP JOIN step from TEST_DB_ULI.a by way of the
           primary index "TEST_DB_ULI.a.a = 1" with no residual conditions,
           which is joined to TEST_DB_ULI.b by way of a RowHash match scan
           with no residual conditions.  TEST_DB_ULI.a and TEST_DB_ULI.b
           are left outer joined using a merge join, with a join condition of (
           "TEST_DB_ULI.a.a = TEST_DB_ULI.b.b").  The result goes into Spool
           1 (one-amp), which is built locally on the AMPs.  The size of
           Spool 1 is estimated with low confidence to be 4 rows.  The
           estimated time for this step is 0.02 seconds.


     explain
     select *
     from a as a
              left outer join
              b as b
           on a.a = b.b
     where b.b = 1;

     3) We do a single-AMP JOIN step from TEST_DB_ULI.b by way of the
           primary index "TEST_DB_ULI.b.b = 1" with no residual conditions,
           which is joined to TEST_DB_ULI.a by way of a RowHash match scan
           with no residual conditions.  TEST_DB_ULI.b and TEST_DB_ULI.a
           are joined using a merge join, with a join condition of (
           "TEST_DB_ULI.a.a = TEST_DB_ULI.b.b").  The result goes into Spool
           1 (one-amp), which is built locally on the AMPs.  The size of
           Spool 1 is estimated with low confidence to be 4 rows.  The
           estimated time for this step is 0.02 seconds.

So coming back to the original question - given the SQL posted we would expect at least 45 rows as the condition was applied against the outer table...

Ulrich

SQL:

     create table a
     (
     a integer
     ) primary index (a);
     create table b
     (
     b integer
     ) primary index (b);

     insert into a values (1);
     insert into a values (2);
     insert into b values (1);

     explain
     select *
     from a as a
              left outer join
              b as b
           on a.a = b.b
     where a.a = 1;

     explain
     select *
     from a as a
              left outer join
              b as b
           on a.a = b.b
     where b.b = 1;


     select *
     from a as a
              left outer join
              b as b
           on a.a = b.b
     where a.a = 1;

     select *
     from a as a
              left outer join
              b as b
           on a.a = b.b
     where b.b = 1;

     drop table a;
     drop table b;

	<Prev	Next>		<<First	<Prev	Next>	Last>>