Archives of the TeradataForum

Message Posted: Tue, 28 Jul 2009 @ 08:50:19 GMT

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

Hello,

1. Try shifting the join condition at the start of the query and check whether optimizer treats it differently. This may result in a merge join.

2. Are you sure that this should be a left outer join? You are performing a LOJ with a table that has a UPI. Generally, this means that Y.PRODUCT_CLASSES_LKUP should have all possible Y.PROD_CAT_CD, Y.PROD_CLASS_CD(since Y.PROD_CAT_CD, Y.PROD_CLASS_CD is the PK). If this is so, then an Inner join(rather than a LOJ) would be appropriate and will give the same result.

3. I'm pretty sure that you would have done this. But in case you haven't, please do collect stats on the joining columns(not individually, but in combination) in the table X and on the UPI in table Y

4. In case if there are any where clauses or any restrictive conditions in your entire query for tables X and Y, please do collect stats on them individually.

5. Try considering derived result sets wherever possible. This will reduce the number of rows accumulated in spool.

6. If you can't change the table structure of X, try adding a NUSI over PROD_CLASS_CD and PROD_CAT_CD and get the explain again. May be NUSI Bit mapping will be used as a result of this. But if the explain shows that NUSI bit mapping is not used...then remove the NUSI..It will cause unncessary overhead.

7. If all the above do not work, then consider using a JOIN Index. Also, if your query is part of some ETL load, then do consider usage of staging tables/GTT's to temporarily store results according to a PI you wish.

If possible, try sending me the SQL, allong with the table structures. I can try my hand in tuning this.

Thanks and Regards,

Suhail

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