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

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
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.