17.10 - InListRewriteOption - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Utilities

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Publication ID
English (United States)

Specifies the type of IN-list processing that will be used for rewrite processing of queries that have IN-lists.

Field Group


Valid Range

0 through 4

Value Description
0 Disable all IN-list rewrite processing
1 Rewrite IN-lists into subqueries.

Vantage uses this type of processing for IN-lists that are not part of CASE expressions.

2 Rewrite IN-lists into outer joins.
Vantage uses this type of processing for IN-lists that are part of CASE expressions.
Teradata recommends that you enable outer-join-based rewrite processing only when there are large IN-lists in CASE-expressions.
3 Options 1 and 2 are enabled
4 Reserved for use by Teradata Professional Services and Teradata Services personnel.



Changes Take Effect

After the DBS Control Record has been written.

Usage Notes

The default setting is appropriate for most situations, and generally provides the best query performance.

IN-list rewrite operations preclude predicate simplification and block optimization during query processing. So in rare cases, using an IN-list rewrite for a query with an IN-list can result in a performance degradation, compared to processing without the rewrite. In these cases, you should increase the value of InListRewriteThreshold to higher than the number values in the query.

Related Information

  • For more information on large IN-list processing, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.
  • For information on other DBS Control fields related to IN-list processing, see InListRewriteThreshold and MaxNumInListRewrite.