Optimal Reconciliation of CASE_N Partitioning Expressions Based on Updatable Current Date and Timestamp - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
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 Vantage 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, Vantage must scan all of the rows in the table to locate any rows that must be reconciled.

  • Vantage 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, Vantage 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, Vantage 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, Vantage 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, Vantage 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, Vantage scans the NO CASE partition during reconciliation.

      If you specify only 1 conditional expression using a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function, Vantage 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, Vantage 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, Vantage 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, Vantage 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, Vantage always scans it during reconciliation.

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

    • Vantage 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.