Archives of the TeradataForum

Message Posted: Mon, 27 Jul 2009 @ 20:27:56 GMT

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

<-- Anonymously Posted: Monday, July 27, 2009 16:21 -->

hi all

my main table 'X' is left outer join'd with the other table Y by statement as given below.

     LEFT OUTER JOIN
     Y.PRODUCT_CLASSES_LKUP
     ON Y.PROD_CAT_CD = X.PROD_CAT_CD
     AND Y.PROD_CLASS_CD = X.PROD_CLASS_CD

Here PROD_CAT_CD,PROD_CLASS_CD is a UPI in Y but not index in X so the join was done by way of product join. But i cannot change the X def to include these fields as index due to some reason so could you please suggest if there is any other way to tune this.

Also please suggest me if that is the only way. As this view have many such type of joins I need to change all those.

Here is the explain step.

     2) We do an all-AMPs JOIN step from Spool 154 (Last Use) by way
        of an all-rows scan, which is joined to Spool 155 (Last Use)
        by way of an all-rows scan.  Spool 154 and Spool 155 are
        left outer joined using a product join, with a join condition
        of ("(PROD_CLASS_CD = PROD_CLASS_CD) AND (PROD_CAT_CD =
        PROD_CAT_CD)").  The result goes into Spool 170 (all_amps)
        (compressed columns allowed), which is built locally on the
        AMPs.  Then we do a SORT to order Spool 170 by row hash.  The
        result spool file will not be cached in memory.  The size of
        Spool 170 is estimated with low confidence to be 661,239,301
        rows.  The estimated time for this step is 9 minutes and 50
        seconds.

Regards

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