Archives of the TeradataForum

Message Posted: Tue, 13 Jun 2000 @ 19:52:04 GMT

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

The most likely reason for your Hot Amp situation is that the optimizer is redistributing an intermediate spool file based on the date column. This is typically done to join to another table or spool file using the date column in the join criteria. In these queries, are you joining to another table using the date column as the join condition? Perhaps a date reference table or, more likely, a very large table. If so, you need to modify the query to get the optimizer to move the other table (either by duplicating on all Amps or redistribution) so that the join on this column occurs across all amps.

Since this problem appeared after the upgrade to V2R3, the optimizer must be executing a different path. Do you possibly have EXPLAINs for this type of query prior to V2R3 to compare?

I agree with John Street that Statistics is a likely place to start looking for a solution. My experience has been that the optimizer utilizes statistics somewhat differently between V2R2 and V2R3.

Regards,

Thomas F. Stanek
TFS Consulting

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