Archives of the TeradataForum
Message Posted: Mon, 27 Jul 2009 @ 20:27:56 GMT
<-- Anonymously Posted: Monday, July 27, 2009 16:21 -->
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|