Archives of the TeradataForum

Message Posted: Wed, 21 Jun 2006 @ 10:43:24 GMT

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

Hello all,

Here is the case I met:

I have a 2mm row table left join to a 39 row table on the PI of the small. It works fine, duplicate the small one, when both are permanent tables. But when I tried to change the big table to volatile table, it redistributed the bigger table to do a merge join! In the explaination, I think it knew how big the volatile table is (please see the expected output rows in step4), but seems I am wrong. So my question is: does the optimizer know the size of a volatile table? Below is a part of the explanation.

   4) We do an all-AMPs RETRIEVE step from SWENG.chk by way of an
      all-rows scan with no residual conditions into Spool 2 (all_amps),
      which is redistributed by hash code to all AMPs.  Then we do a
      SORT to order Spool 2 by row hash.  The result spool file will not
      be cached in memory.  The size of Spool 2 is estimated with high
      confidence to be 2,185,747 rows.  The estimated time for this step
      is 33.94 seconds.

   5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a
      RowHash match scan, which is joined to cdc_scratch.exch by way of
      a RowHash match scan with a condition of (
      "(cdc_scratch.exch.DAY_OF_RATE_DT >= :MIN_CRE_DT) AND
      (cdc_scratch.exch.DAY_OF_RATE_DT <= :MAX_CRE_DT)").  Spool 2 and
      cdc_scratch.exch are left outer joined using a merge join, with a
      join condition of ("(CURRENCY_ID = cdc_scratch.exch.CURNCY_ID) AND
      (CREATED_DT = cdc_scratch.exch.DAY_OF_RATE_DT)").  The result goes
      into Spool 1 (all_amps), which is redistributed by hash code to
      all AMPs.  Then we do a SORT to order Spool 1 by row hash.  The
      size of Spool 1 is estimated with index join confidence to be
      2,185,747 rows.  The estimated time for this step is 0.45 seconds.

Regards,

Ray

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