 |
 |
Archives of the TeradataForum
Message Posted: Fri, 28 Aug 2009 @ 11:03:16 GMT
Prasad,
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.
Your explain plan would probably look like one given below.
Explanation
-------------------------------------------------- | |
| | 3) | We do an all-AMPs RETRIEVE step from "tablename" by way of an all-rows scan with no residual conditions into Spool 2 (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The input table will not be cached in memory, but it is eligible for synchronized scanning.
The result spool file will not be cached in memory. The size of Spool 2 is estimated with high confidence to be 204,849,278 rows. The estimated
time for this step is 1 minute and 24 seconds.
| |
| | 4) | We do an all-AMPs STAT FUNCTION step from Spool 2 by way of an all-rows scan into Spool 5, which is built locally on the AMPs. The result
rows are put into Spool 1 (group_amps), which is built locally on the AMPs. This step is used to retrieve the TOP 10 rows. Single AMP
optimization is used. If this step retrieves less than 10 rows, then execute step 5.
| |
| | 5) | We do an all-AMPs STAT FUNCTION step from Spool 2 (Last Use) by way of an all-rows scan into Spool 5 (Last Use), which is built locally on
the AMPs. The result rows are put into Spool 1 (group_amps), which is built locally on the AMPs. This step is used to retrieve the TOP 10 rows.
| |
| | 6) | Finally, we send out an END TRANSACTION step to all AMPs involved ...
| |
In case you just want ANY 10 rows from the table, then it would be better to go for
Select * FROM table SAMPLE 10; as this one wont spool out.
Hope it helps. Cheers.
Regards,
Yasir
| |
