17.05 - Using the TOP n Row Option - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Advanced SQL Engine
Teradata Database
Release Number
Release Date
January 2021
Content Type
Programming Reference
Publication ID
English (United States)

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.