TOP n Operator Out Performs QUALIFY RANK and QUALIFY ROW_NUMBER - 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™

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;