Max parameter that can pass IN clause in Netezza

In Netezza, One can specify an IN clause, like this: (just like sql)

 SELECT * FROM user WHERE id IN (1000, 1001, 1002)

Does anyone know what’s the maximum number of parameters you can pass into IN?

I know Oracle allows up to 1,000 IN list values in a SQL statement.

Answer

The limit would be the maximum physical length of a SQL statement allowed by Netezza.

If the number of IN members exceeds a threshold specified by the INLIST_THRESHOLD setting, the system will write the list to disk for use in the query. If the number of members is at or below the threshold, the in list values will be compiled into the query and pushed to the FGA for disk-level filtering (faster). You can control the threshold setting forum discussion