Purpose
Specifies the type of IN-list processing that will be used for rewrite processing of queries that have IN-lists.
Field Group
Performance
Valid Range
0 through 4
| Value | Description |
|---|---|
| 0 | Disable all IN-list rewrite processing |
| 1 | Rewrite IN-lists into subqueries. Teradata Database uses this type of processing for IN-lists that are not part of CASE expressions. |
| 2 | Rewrite IN-lists into outer joins. Teradata Database 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 Support personnel. |
Default
1
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 Topics
- For more information on large IN-list processing, see SQL Request and Transaction Processing.
- For information on other DBS Control fields related to IN-list processing, see InListRewriteThreshold and MaxNumInListRewrite.