Archives of the TeradataForum

Message Posted: Thu, 28 Aug 2014 @ 09:22:00 GMT

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

Yes, this is not a straight forward LEFT JOIN. They are built on top of views which has additional joins. This is not the complete query too. The complete query is mentioned below. I was confused as to why it was doing the product join. Now I am clear that product join is a join strategy where the optimizer duplicates the smaller table across all the AMPS (Please correct me if I am wrong). The query runs out of spool in the 5 th join (spool allocated - 100GB), as the count after every JOIN is drastically increasing. The stats are up to date on all the necessary columns (Join columns/ Indexes)

     DATABASE BAKTHRO;

     SELECT
     COLUMN1
     COLUMN2
     COLUMN3
     COLUMN4
     COLUMN5
     COLUMN6
     COLUMN7

     FROM RPT_SPEC_WEIGHT            A11
              LEFT OUTER JOIN      PLM_SPEC_ALLERGEN_VW1        A12
                ON         (A11.PRODUCT_GN_NBR = A12.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_SPEC_NUTRITION_VW       A13
                ON         (A11.PRODUCT_GN_NBR = A13.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_SPEC_CHEM_PROP_VW    A14
                ON         (A11.PRODUCT_GN_NBR = A14.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_SPEC_MICROBIOTIC_PROP_VW      A15
                ON         (A11.PRODUCT_GN_NBR = A15.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_SPEC_PHYSICAL_PROP_VW      A16
                ON         (A11.PRODUCT_GN_NBR = A16.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_SPEC_SENS_PROP_VW     A17
                ON         (A11.PRODUCT_GN_NBR = A17.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_PRODUCT_VW    A18
                ON         (A11.PRODUCT_GN_NBR = A18.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      RPT_PROD_CODE_VW               A19
                ON         (A11.PRODUCT_GN_NBR = A19.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_PROD_SPEC_VW A110
                ON         (A11.PRODUCT_GN_NBR = A110.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_SPEC_INGREDIENT_STMT_VW        A111
                ON         (A11.PRODUCT_GN_NBR = A111.PRODUCT_GN_NBR)

              LEFT OUTER JOIN      PLM_SPEC_NUTRITION_CONFIG_VW      A112
                ON         (A11.PRODUCT_GN_NBR = A112.PRODUCT_GN_NBR)

              WHERE A11.PRODUCT_GN_NBR = '2874.80469'

Pinal - the DB version is 13.10. Yes the object logging is enabled.

RPT_SPEC_WEIGHT is a view and the definition is given below. The column PRODUCT_GN_NBR is of CHAR datatype.

     REPLACE VIEW  BAKTHRO.RPT_SPEC_WEIGHT_VW AS LOCKING ROW FOR ACCESS SEL P.PRODUCT_GN_NBR,
     COLUMN1, COLUMN 2, .
     .
     .
     COLUMN N
     FROM BAKTHRO.PLM_PRODUCT P
         LEFT JOIN
     (
       SEL
         A.PRODUCT_GN_NBR,
       COLUMN1,
     COLUMN 2,
     .
     .
     .
     COLUMN N
     FROM BAKTHRO.PLM_SPEC_WEIGHT_VW A
     )  T
     ON T.PRODUCT_GN_NBR = P.PRODUCT_GN_NBR
     GROUP BY P.PRODUCT_GN_NBR;

The PI of PLM_PRODUCT is PRODUCT_GN_NBR and the PI of PLM_SPEC_WEIGHT and every other table is COLUMN ABC. I tried to include the column ABC too in the JOIN condition , which significantly brought down the total estimated time, however it is still running out of spool.

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