When the number of values in an IN-list for IN or NOT IN predicates exceeds the system setting, the IN-list is checked to see whether IN-list rewrite processing is applicable. 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 does 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 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 ;