optimize select top10 * from table1 &&compare&& select * from table1

Database

optimize select top10 * from table1 &&compare&& select * from table1

Hi all

I just confused when i submit the two query

first :select top10 * from table1,Teradata SQL Assisstant gives me an error:

2646:no more spool space for accoutname.

second: I submit select * from table1,it succeeds to get about 20million records.

This makes me confused:
why it does not work when i just want to get 10 record comparing with get all 20million records?

and here is the explain execution . Can someone tell me the difference in execution?
first :select top 10...
1) First, we lock a distinct ebay_qu_t."pseudo table" for read on a
RowHash to prevent global deadlock for
ebay_qu_t.qu_data_item_detail.
2) Next, we lock ebay_qu_t.qu_data_item_detail for read.
3) We do an all-AMPs STAT FUNCTION step from
ebay_qu_t.qu_data_item_detail by way of an all-rows scan with no
residual conditions 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 4. The size is estimated
with low confidence to be 24,726,000 rows (616,345,002,000 bytes).
4) We do an all-AMPs STAT FUNCTION step from
ebay_qu_t.qu_data_item_detail by way of an all-rows scan with no
residual conditions 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. The size is estimated with low
confidence to be 24,726,000 rows (616,345,002,000 bytes).
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.

second :select * from .........
1) First, we lock a distinct ebay_qu_t."pseudo table" for read on a
RowHash to prevent global deadlock for
ebay_qu_t.qu_data_item_detail.
2) Next, we lock ebay_qu_t.qu_data_item_detail for read.
3) We do an all-AMPs RETRIEVE step from ebay_qu_t.qu_data_item_detail
by way of an all-rows scan with no residual conditions into Spool
1 (group_amps), 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 1 is estimated with low confidence
to be 24,726,000 rows (616,345,002,000 bytes). The estimated time
for this step is 14.01 seconds.
4) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.

Thanks
Keith
1 REPLY
Teradata Employee

Re: optimize select top10 * from table1 &&compare&& select * from table1

Top without order by is non-deterministic Any N. Any N optimization is in Teradata 13.0. You are probably on pre-13.0 database. You can rewrite as

select * from table1 sample 10;

or use retlimit or rewrite as qualify row_number() over (order by 1) < = 10