TOP n Operator Out Performs QUALIFY RANK and QUALIFY ROW_NUMBER - Advanced SQL Engine - Teradata Database

SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
qtb1554762060450.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™

The QUALIFY clause with the RANK or ROW_NUMBER ordered analytical functions returns the same results as the TOP n operator.

For best performance, use the TOP option instead of the QUALIFY clause with RANK or ROW_NUMBER. In best case scenarios, the TOP n operator provides better performance; in worst case scenarios, the TOP n operator provides equivalent performance.

For example, these two statements have the same semantics, but the statement that specifies TOP n performs better than the statement that specifies QUALIFY ROW_NUMBER.

     SELECT TOP 10 *
     FROM sales
     ORDER BY county;

     SELECT *
     FROM sales
     QUALIFY ROW_NUMBER() OVER (ORDER BY COUNTY) <= 10;

Similarly, these two statements have the same semantics, but the statement that specifies TOP n performs better than the statement that specifies QUALIFY RANK.

     SELECT TOP 10 WITH TIES *
     FROM sales ORDER BY county;

     SELECT *
     FROM sales
     QUALIFY RANK() OVER (ORDER BY county) <= 10;