TOP n Row - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

As an option to the SELECT statement, the TOP n option automatically restricts the output of queries to a certain number of rows. This option provides a fast way to get a small sample of the data from a table without having to scan the entire table. For example, a user may want to examine the data in an Orders table by browsing through only 10 rows from that table.

The value of n can be passed into the operator by means of a macro, stored procedure, or USING request modifier parameter.

Performance Optimizations

TOP n option is optimized for handling TOP n and ANY n requests. Optimizations include:
  • Adding an AMP runtime optimization for TOP n PERCENT operations.
  • Extending ANY n optimization to INSERT ... SELECT and CREATE TABLE AS requests, views, and derived tables for all values of n.
  • Adding an optimization that avoids redistributing the rows for the hash partitioning case when the grouping columns of a window function contain the PI columns of the source relation.
  • Adding a RankLimit optimization for a TOP n operation that does not specify the WITH TIES option.
  • Adding runtime optimizations for TOP n in a request that specifies an ORDER BY specification.

TOP n Option Performance Considerations

For best performance, use the TOP n option instead of the QUALIFY clause with RANK or ROW_NUMBER.
  • In best cases, the TOP n option provides better performance.
  • In worse cases, the TOP n option provides equivalent performance.

If a SELECT statement using the TOP n option does not also specify an ORDER BY clause, the performance of the SELECT statement is better with BTEQ than with FastExport.