Archives of the TeradataForum

Message Posted: Thu, 20 Oct 2005 @ 12:44:00 GMT

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

Your spool space problems is caused by the implied SELECT DISTINCT within your sub-select and the differences between a SELECT DISTINCT and a SELECT ....GROUP BY.....both provide the same answer but work differently and depending on your data one will be more efficient that the other.

When there are few duplicates to remove a SELECT DISTINCT is more efficient

When there are many duplicates to remove a SELECT ... GROUP BY is more efficient

Here's why:

With a SELECT DISTINCT, Teradata sends all qualifying rows back to the PE AMP, which then sorts to remove duplicates (all work being done on one amp - possible out of spool for that one amp if it's a large result set.

With a SELECT ....GROUP BY, each amp sorts to remove duplicates prior to sending their results to the PE AMP, which then does a final sort to remove any possible duplicates.....does an extra sort, for if lots of duplicates, the work is spread across all your amps instead of making one do all the work.

I encourage by business users to use GROUP BY unless they know there are very few duplicates.

Ruth Fenwick
Database Services

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