Archives of the TeradataForum

Message Posted: Mon, 01 Sep 2014 @ 11:21:41 GMT

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

You do realize that the WHERE clause can't be evaluated until AFTER the outer joins are performed? Your WHERE A11.PRODUCT_GN_NBR = '2874.80469' is only evaluated after all of the outer joins have been completed resulting in an enormous spool file.

I'm sorry, but this appears to be a bad query that is attempting to join:

1.) every record in RPT_SPEC_WEIGHT to every record in PLM_SPEC_ALLERGEN_VW1

2.) every record in that result to every row in PLM_SPEC_CHEM_PROP_VW

3.) every record in that result to every row in PLM_SPEC_MICROBIOTIC_PROP_VW

4.) every record in that result to every row in PLM_SPEC_PHYSICAL_PROP_VW

5.) every record in that result to every row in PLM_SPEC_SENS_PROP_VW

6.) every record in that result to every row in PLM_PRODUCT_VW

7.) every record in that result to every row in RPT_PROD_CODE_VW

8.) every record in that result to every row in PLM_PROD_SPEC_VW

9.) every record in that result to every row in PLM_SPEC_INGREDIENT_STMT_VW

10.) every record in that result to every row in PLM_SPEC_NUTRITION_CONFIG_VW

11.) From the result in #10, return any records WHERE A11.PRODUCT_GN_NBR = '2874.80469'

Might I humbly suggest that the query writer reevaluate and rethink their use of LEFT OUTER joins?

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