Archives of the TeradataForum

Message Posted: Wed, 18 Jun 2014 @ 20:24:37 GMT

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

A user has a SQL with a product join. I'd like to know how to remove the product join.

Thanks,

     SELECT
     .
     .
     .
     .
     WHERE
     .
     .
     AND
       (
        VWDB.TAB1.ID_TYPE_CD  = 1
        AND
        (
         VWDB.View1.PRIM_ID_IND  =  'N'
         OR
        VWDB_View1.STAT_CD  <>  1
         OR
       VWDB.View2.PRD_STATUS_CD  <>  6
         OR
        VWDB.View2.DW_VER_END_DT  <>  {d '9999-12-31'}
         OR
         VWDB_VWS.View2.ACCT_TYPE_CD  <>  250
         OR
       VWDB.View2.TAX_PRD_END_DT  <>  {d '9999-12-31'}
        )
       )



     5) We execute the following steps in parallel.
           1) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of
              an all-rows scan, which is joined to Spool 5 (Last Use) by
              way of an all-rows scan.  Spool 4 and Spool 5 are joined
              using a product join, with a join condition of (
              "((PRD_STATUS_CD >= 7) OR ((PRD_STATUS_CD <= 5) OR
              ((DW_VER_END_DT <= DATE '9999-12-30') OR ((DW_VER_END_DT >
              DATE '9999-12-31') OR ((ACCT_TYPE_CD <= 249) OR
              ((ACCT_TYPE_CD >= 251) OR ((TAX_PRD_END_DT <= DATE
              '9999-12-30') OR (TAX_PRD_END_DT > DATE '9999-12-31'))))))))
              OR ((STAT_CD >= 2) OR ((STAT_CD <= 0) OR (PRIM_ID_IND =
              'N')))").  The result goes into Spool 6 (all_amps)
              (compressed columns allowed), which is built locally on the
              AMPs.  Then we do a SORT to order Spool 6 by the hash code of
          ......).  The result
              spool file will not be cached in memory.  The size of Spool 6
              is estimated with no confidence to be 177,149,415,595 rows (
              13,463,355,585,220 bytes).  The estimated time for this step
              is 890 hours and 7 minutes.

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