Archives of the TeradataForum

Message Posted: Wed, 22 Mar 2006 @ 13:07:58 GMT


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

Subj:		Re: Usage of TOP function

From:		Dieter Noeth

Anomy.Anom wrote:

Im new to Teradata. I have seen an SQL written like Select TOP 50 * from <tablename> , which purportedly returns the 'first' 50 rows. But, since there cannot be 'first' or 'last' rows in a table of an RDBMS, there must be an implicit order by.

That's why TOP doesn't exist in Standard SQL :-)

My question now is whether TOP function without an order by selects rows at random or is there any default order by column. Please let me know.

The order is simlar to any select without order by:

As all tables in Teradata are ordered by ROWID the result set looks totally unordered.

If the requested number of rows is <= 5000 then one AMP will simply start scanning the first datablock(s) [Explain: "Single AMP optimization is used"].

If you rerun TOP (during the same session) it will return the same rows again, but if you logoff/logon the data will change -> the AMP is selected based on session/PE.

If the requested number of rows is > 5000 then all (?) AMPs start scanning [Explain: "Load distribution optimization is used"].

Btw, the only reason to use TOP is to have a quick look at data, because using SAMPLE is slooow on large system.

This means a select without joins/where/order:

     select top xxx * from table;

Probably anything else is internally rewritten using Standard SQL: RANK/PERCENT_RANK/ROW_NUMBER.

Dieter


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

Attachments

Library

Quick Reference