IBM Is there a limit to the number of items that can be listed within an "in" list as part of a where clause? - United States  TECHNOTE (TROUBLESHOOTING)

PROBLEM(ABSTRACT)
 Is there a limit to the number of items that can be listed within an "in" list as part of a where clause? 

CAUSE
Every time the query optimizer finds a "(NOT) IN" statement in the query, a decision must be made:
Option A: Evaluate the condition sequentially for every member of the IN-list, or 
Option B: create a temporal virtual table from this list and use JOIN logic for evaluation. 

If the amount of members in the IN-list is smaller than the value of INLIST_THRESHOLD, then option A is used. 
If the amount of members in the IN-list is equal or bigger than the value of INLIST_THRESHOLD, then option B is used.


ENVIRONMENT
Any IBM PureData System for Analytics with any NPS Release



RESOLVING THE PROBLEM
The short answer is no limit.


By default we create a virtual table for any "in list" with more than 5 values.

The only limitation with large "in list" statements would be the memory needed to compile the query. If the query is too complex, you can potentially run out of memory, but that will not affect other sessions since this happens inside the postgres process assigned to that session. The parameter that is responsible for this behaviour is inlist_threshold and it is safe to increase this value to 100, but not more.

show inlist_threshold;

set inlist_threshold=<value>;

 

PRODUCT ALIAS/SYNONYM
 pda netezza
Is there a limit to the number of items that can be listed within an "in" list as part of a where clause? Is there a limit to the number of items that can be listed within an "in" list as part of a where clause? 
The short answer is no limit.


By default we create a virtual table for any "in list" with more than 5 values.

The only limitation with large "in list" statements would be the memory needed to compile the query. If the query is too complex, you can potentially run out of memory, but that will not affect other sessions since this happens inside the postgres process assigned to that session. The parameter that is responsible for this behaviour is inlist_threshold and it is safe to increase this value to 100, but not more.