16.10 - IN-List Rewrite - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K
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 Utilities , B035-1102 . The following rules apply for the IN-list rewrite processing:
  • Subquery-based IN-list rewrite (for lists that are not in CASE expressions) is disabled for IN-lists that are disjuncted with other conditions.
  • Outer-join-based IN-list rewrite (for lists that are part of CASE expressions) is disabled in the following cases:
    • IN-list that involves fields from multiple tables
    • IN-list expression contains aggregate or analytics (statistics) functions
    • IN-list expression involves fields from derived tables, views, or scalar subquery (SSQ)
    • Expression alias of the CASE expression is not in the GROUP BY list
    • IN-list is in a correlated term
    • IN-list is in a nested CASE expression

IN-list query rewrite processing 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 processing 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 ;