Archives of the TeradataForum

Message Posted: Fri, 16 Apr 2004 @ 20:34:29 GMT

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

Rohit,

I have found some information on spool usage with UNION ALL in the V2R5 "Performance Optimization", p. 15-12. I don't know which release you are on and whether you have access to both R4 and R5. If you could build the test case on both releases and compare spool usage figures, we could get more insight into this issue.

"Improvements to Performance of UNION ALL Set Operator

Historically, when spool files resulted from a UNION ALL set operation, the Teradata database did not maintain information about how spools files were hashed, even though the Optimizer might be able to use the information later. This practice caused unnecessary spool file redistribution when the Optimizer performed a join on the hash columns of the spool file later in the query plan.

To avoid the cost of redistribution, the system, as of Teradata V2R5.0, saves the primary index of a local spool file containing multiple selects to base tables that have been combined using the UNION ALL operator.

Usage Notes

No new syntax is required for this feature. The Optimizer will simply attempt this optimization if all sets are base table selects involving the primary index and each set operation is UNION ALL. Each set must be hashed on the same columns (by position in the select list). Also, join terms involving these columns must exist to observe the optimization."

Regards,

Victor

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