16.10 - InListRewriteOption - Teradata Database

Teradata Database Utilities

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-26
dita:mapPath
zll1480972831047.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval

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.