17.10 - IN-List Rewrite - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

When the number of values in an IN-list for IN or NOT IN predicates exceeds the setting of the INListRewriteThreshold field in DBS Control, the IN-list is checked to see whether IN-list rewrite processing is applicable. For more information on DBS Control fields related to IN-list rewrite processing, see Teradata Vantage™ - Database Utilities, B035-1102. The IN-list rewrite processing spools the IN-list values and rewrites the IN-list to a subquery referencing the spool.

IN-list query rewrite for a list that is not in a CASE expression

Assume the following query.
SELECT SUM(net_rev_amt)
FROM  prod_db.fxf_ship_rev_credit_comp  a
WHERE a.payor_cust_nbr IN ('14390630' ,'15449611','15454443',…);
Because the IN-list is not in a CASE-expression, subquery-based rewrite is applied, resulting in the following query. (The Optimizer will do join planning after the IN-list rewrite is applied.)
SELECT SUM(prod_db.a.net_rev_amt ) 
FROM prod_db.fxf_ship_rev_credit_comp  a
WHERE a.payor_cust_nbr  IN 
              (SELECT InListSpool_1.payor_cust_nbr 
               FROM InListSpool_1);

IN-list query rewrite for a list that is in a CASE expression

Assume the following query.
SELECT SUM(net_rev_amt)
FROM  prod_db.fxf_ship_rev_credit_comp  a
WHERE CASE WHEN a.payor_cust_nbr IN ('14390630' ,'15449611',...)
       THEN rev_shp_cnt  ELSE rev_pcs END > 120 ;
Because the IN-list is in a CASE expression, outer-join-based IN-list rewrite is applied, resulting in the following query.
SELECT SUM(prod_db.a.net_rev_amt ) 
FROM ship_rev a 
LEFT OUTER JOIN InListSpool_1 
ON ( (a.payor_cust_nbr = InListSpool_1.payor_cust_nbr )
WHERE (( CASE WHEN (NOT (InListSpool_1.payor_cust_nbr IS NULL )) 
              THEN (a.rev_shp_cnt ) 
              ELSE (a.rev_pcs ) END ))>  120 ;