Archives of the TeradataForum

Message Posted: Mon, 19 Jan 2004 @ 13:09:09 GMT

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

Vikram,

This is a very inefficient plan. The optimizer should be duplicating the smaller table (Table Y) to all AMPs, sorting table X on the join columns (A and B), then joining tables X and Y AMP locally via a hash join. However, since the optimizer has no statistical information on the join columns, it is redistributing both tables on the join columns, then performing an AMP local product join. The expensive part of this process is the redistribution of the 23 million record table.

Hopefully, you are on V2R5. If so, you need to collect multi-column statistics on Columns A & B (Collect Stats on Table X Column (ColumnA, ColumnB)). If you do this for both tables, then the optimizer should behave properly (you can verify this by looking at the explain.)

If you are not on V2R5, and you cannot (due to space limitations) create a NUSI on Columns (A,B) for both tables, then your options are limited.

1) I'll assume you can create a multi-column NUSI on columns A and B for table Y (since it is so small.) In order to save enough space to create a NUSI on table X, try playing with column compression. Maybe you can save enough space (via compression) to create a NUSI on table X.

If not,

2) Remove all data from table Y, recollect stats, then repopulate table Y. Perhaps, if the optimizer thinks that no data exists in table Y, it will duplicate table Y to all AMPs.

This is effectively 'hinting' (which is discouraged), but since you cannot create the necessary NUSIs, then you don't have much of a choice. While this solution may help for this query, chances are it will hurt the performance of other queries using Table Y.

Hope this helps,

Clay

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