Select TOP n

Database
Enthusiast

Select TOP n

Hello,

"SELECT TOP n from table" is very fast in some case, even on large tables, and is showing an odd behaviour on other tables,
with a three steps explain .. to get 10 records in one minute ! (V2R6)

How to understand the first all-rows scan in step below ?

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 ...