Archives of the TeradataForum
Message Posted: Tue, 11 Mar 2008 @ 22:28:15 GMT
Michael Larkins wrote:
That's why RANK is quite fast even on a large number of rows, if the number of qualifying rows is small.
ROW_NUMBER could use the same optimization, but apparently it's implemented using a different (i.e. slower) algorithm, probably spooling all rows before QUALIFY.
TOP without any ORDER will use "single AMP optimization" for up to 5000 rows (if the table is large), and "load distribution optimization" (i.e. several AMPS) for values > 5000 or small tables.
This is just reading the first datablock(s).
If it's still not retrieving the requested number of rows, then there's "execute step x", which seems to be a kind of "fallback" to a SAMPLE.
"TOP 10 WITH TIES ... ORDER BY yy" is rewritten as "QUALIFY RANK() OVER (ORDER BY yy) <= 10" "TOP 10 ... ORDER BY yy" -> "QUALIFY ROW_NUMBER() OVER (ORDER BY yy) <= 10" "TOP 10 PERCENT WITH TIES... ORDER BY yy" -> "QUALIFY PERCENT_RANK() OVER (ORDER BY yy) <= 0.1" "TOP 10 PERCENT ... ORDER BY yy" -> there's no shortcut, but it's equivalent to the PERCENT_RANK, just using ROW_NUMBER instead of RANK.
Only Explain can show this: if it's on a base table without any join/where/group it's just accessing the first datablocks and no spool is prepared, anything else creates the whole answer set before the STAT function.
This behaviour is similar to a SAMPLE.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|