Archives of the TeradataForum
Message Posted: Fri, 28 Aug 2009 @ 12:45:57 GMT
| Subj: | | Re: Spool space error |
| |
| From: | | Dieter Noeth |
Yasir Hassan Maken wrote:
| | When you execute something like Select Top n from table, then you should expect all rows from that table to be spooled first and then top
function to be applied in the next step. As you do not have any WHERE clause, so, all rows will be spooled and the query will fall over before
application of TOP function as your user do not have enough spool to accommodate all rows of the table. | |
No, when there's a plain "SELECT TOP x * FROM table" without WHERE or ORDER it's not expected to spool the table.
This is exactly the reason why it has been implemented:
"SAMPLE x" was too slow (but also not scanning all rows), so to get a few "sampled" rows some users simply submitted "SELECT * FROM table" in
QueryMan and waited for the "cancel aborting to settings?": On a 2020 AMP system the SAMPLE needed about two minutes independant of the table size
and a simple SELECT * was usually faster (unless you hit the huge tables).
But Teradata starts returning the answer set *after* it's fully created, so in the background lots of MBs or GBs are scanned to spool and then
discarded. A lot of unneccessary IOs.
Dieter
| 
