Archives of the TeradataForum

Message Posted: Tue, 11 Mar 2008 @ 20:34:33 GMT

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

Anomy:

All of your observations are correct regarding TOP needing more spool when used with joins, sorting, etc. Even though your elaboration is on the mark for more involved SQL, it probably goes well beyond the scope of the original question since his example did use an order by. Whether or not it contains a join would be determined by whether xyz were a view instead of a table and if so, whether it is a single or multi-table view (join) or anything other than simple AMP local spooling. All we were given was:

No matter what else it might be doing, it is definitely not doing a sort. So, there is no requirement to bring back the lowest or highest values. Even so, the EXPLAIN shows that TOP is implemented as a STAT function. I have heard that RANK (which requires an ORDER BY) when done with a QUALIFY will only place the requested number of rows on each AMP (i.e. 5 highest or lowest) locally and then globally return the requested values/rows (highest of the high or lowest of the low). It would be my hope that TOP might be implemented using the same logic as RANK/QUALIFY - but only the developers can tell us that for sure since the details of a STAT FUNCTION are not present in the EXPLAIN. Either way, as you indicated, it will use more spool than a simple select, but hopefully not all rows as you seem to imply.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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