Optimal Reconciliation of CASE_N Partitioning Expressions Based on Updatable Current Date and Timestamp - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™
The following rules and guidelines apply to specifying CASE_N partitioning expressions in a way that they can be reconciled optimally using ALTER TABLE TO CURRENT statements. See ALTER TABLE TO CURRENT.
  • You should specify DATE, CURRENT_DATE, or CURRENT_TIMESTAMP on one side of a CASE_N conditional expression only. The CASE_N specification can be optimal on either the LHS or the RHS of the expression, but you if you specify it on both side, the reconciliation cannot be optimized.

    This optimization makes it possible for Teradata Database to skip some partitions during the reconciliation; however, if you specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function on both sides of a conditional expression, Teradata Database must scan all of the rows in the table to locate any rows that must be reconciled.

  • Teradata Database does not scan a partition during reconciliation if it can determine that its rows will remain in their current partition after any DATE, CURRENT_DATE, or CURRENT_TIMESTAMP values have been reconciled.
  • If you specify NO CASE in a CASE_N expression and more than 1 conditional expression specifies the DATE or CURRENT_DATE function, Teradata Database scans the NO CASE partition during reconciliation only when the prior conditional expressions specified with CURRENT_DATE or CURRENT_TIMESTAMP are not contiguous.

    If you specify only 1 conditional expression using a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function, Teradata Database scans the NO CASE partition only when the partition corresponding to this conditional expression is skipped.

    For example, the following expressions are all able to take advantage of this optimization.
    • CASE_N (j ≥ CURRENT_DATE, NO CASE)

      For this case, Teradata Database does not scan the rows in the second partition during reconciliation.

    • CASE_N(j ≥ CURRENT_DATE           /* Current quarter */,

             j ≥ CURRENT_DATE - INTERVAL '3' MONTH

         AND j <  CURRENT_DATE           /* previous quarter */,

             NO CASE                     /* old quarters */)

      For this case, Teradata Database does not scan the rows in the last partition, annotated as “old quarters,” during reconciliation because the expressions are all contiguous.

    • If you specify NO CASE in the CASE_N expression and more than 1 conditional expression specifies CURRENT_DATE, Teradata Database scans the NO CASE partition during reconciliation.

      If you specify only 1 conditional expression using a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function, Teradata Database scans the NO CASE partition during reconciliation only when the prior conditional expressions specified with DATE, CURRENT_DATE or CURRENT_TIMESTAMP are not contiguous.

      For example, expressions such as the following provide such optimizations.

      CASE_N(j >= CURRENT_DATE /* current quarter */

             j ≥ CURRENT_DATE = INTERVAL ' 3 ' MONTH

         AND j < CURRENT_DATE /* previous quarter */, NO CASE)

      For this case, Teradata Database does not scan the rows in the second partition, labeled as “previous quarter,” during reconciliation.

      CASE_N(j ≥ CURRENT_DATE   /*current quarter*/,

             j ≥ CURRENT_DATE - INTERVAL '3' MONTH

         AND j < CURRENT_DATE    /*previous quarter*/,

         NO CASE                 /*old quarters*/)

      For this case, Teradata Database scans the NO CASE partition. Because there is a gap of 3 months between the second and third expressions, this is a non-contiguous case.

      CASE_N(j ≥ CURRENT_DATE               /*current quarter*/,

             j ≥ CURRENT_DATE - INTERVAL '3' MONTH

         AND j <  CURRENT_DATE               /*previous quarter*/,

             j <  CURRENT_DATE-INTERVAL '3' MONTH /*old quarter*/,

             NO CASE)

      For this case, Teradata Database scans partitions 1 and 2, but skips partitions 3 and 4 during reconciliation. The NO CASE partition is skipped because expressions 1, 2, and 3 are all contiguous.

    • If you specify an UNKNOWN partition in a CASE_N expression, Teradata Database always scans it during reconciliation.

      This also occurs if you specify a NO CASE OR UNKNOWN partition.

    • Teradata Database does not scan any partitions defined with conditional expressions that do not specify either a DATE, CURRENT_DATE, or a CURRENT_TIMESTAMP function unless they are defined prior to any conditional expressions that specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function. Otherwise, they are scanned.

      You should specify all conditional expressions that do not specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function before you define any conditional expressions that do specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function.

      For example, the following expressions are all able to take advantage of this optimization.

      • CASE_N(j ≥CURRENT_DATE /* current quarter */,

        j ≥CURRENT_DATE - INTERVAL '3' MONTH AND j < CURRENT_DATE

            /* previous quarter */, NO CASE)

      • CASE_N(j ≥CURRENT_DATE /* current quarter */,

        j ≥CURRENT_DATE-INTERVAL '3' MONTH AND j < CURRENT_DATE

            /* previous quarter */,

        j <CURRENT_DATE-INTERVAL '6' MONTH, /* old quarters */ NO CASE)

        In this non-contiguous case the system scans NO CASE, and there is a gap of 3 months between 2nd and 3rd expression.