Archives of the TeradataForum

Message Posted: Fri, 26 Oct 2001 @ 19:52:10 GMT

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

I tried your examples on V2R4.0.2.24 and got the same results you did. Then I added this version to the mix:

explain locking row for access
select a.databaseid, b.tableid, b.fieldid
from
DBC.TVM a
join
dbc.tvfields b
on (b.tableid = a.tvmid
     and
    b.tableid = '000000090000'xb);

Explanation
--------------------------------------------------------------------------
  1) First, we do a two-AMP JOIN step from DBC.a by way of unique index
     # 4 "DBC.a.TVMId = '000000090000'XB" with no residual conditions,
     which is joined to DBC.b by way of the primary index
     "DBC.b.TableId = DBC.a.TVMId" with an additional condition of (
     "DBC.b.TableId = '000000090000'XB").  DBC.a and DBC.b are joined
     using a nested join, with a join condition of ("(1=1)").  The
     result goes into Spool 1, which is built locally on the AMPs.  The
     size of Spool 1 is estimated with low confidence to be 8 to 70,560
     rows.  The estimated time for this step is 0.23 to 7.41 seconds.

  -> The contents of Spool 1 are sent back to the user as the result of
     statement 1.  The total estimated time is 0.23 seconds.

Which is identical to your WHERE clause version. I seem to remember someone at NCR (maybe Todd Walters) saying that there were some difficulties combining conditions between ON and WHERE clauses to produce global optimization. For example, ON & WHERE conditions behave differently in OUTER JOIN versus INNER JOIN.

Perhaps one of our more theoretical list members would care comment on the relational differences between your original ON/WHERE version and my ON only one.

/dave hough

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