Optimal Reconciliation of RANGE_N PPI 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 RANGE_N partitioning expressions in a way that they can be reconciled optimally using ALTER TABLE TO CURRENT statements. See ALTER TABLE TO CURRENT.
  • A test expression in the RANGE_N function specifies the value used to determine the position in the specified range list.

    If the test expression specifies either a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function, then Teradata Database scans all the rows during reconciliation. This is because the value of the expression changes as the value for DATE, CURRENT_DATE, or CURRENT_TIMESTAMP changes, so each row becomes a candidate to be moved to a different partition.

    The test value cannot have a result data type of TIMESTAMP. Because of this, if you specify a CURRENT_TIMESTAMP function in the test value, you must employ it in a way that causes the result of the test value to be a value having the BYTEINT, SMALLINT, BYTEINT, or DATE data type.

  • Assume that you specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function in the starting expression of the first range and then submit an ALTER TABLE TO CURRENT statement on the table.

    If the starting expression of the first range that is resolved with a new DATE, CURRENT_DATE, or CURRENT_TIMESTAMP value falls on a partition boundary, then Teradata Database drops all of the partitions preceding the matched partition.

    This is equivalent to performing an ALTER TABLE statement that specifies a DROP RANGE clause.

  • If you specify either a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function in an ending expression, then the following things are true about a NO RANGE partition.
    IF NO RANGE is … THEN Teradata Database …
    specified scans rows from the NO RANGE partition to partition them based on the newly resolved date.
    not specified does not scan any rows.

    The partitioning expression is modified to accommodate the changed range values.

    This is equivalent to performing an ALTER TABLE statement that specifies an ADD RANGE clause.

  • If you specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function in both the starting expression and the ending expression, then both of the 2 preceding bullets apply.

    Because Teradata Database must scan rows in the NO RANGE partition when you specify a NO RANGE partition, you should specify either a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function in the partitioning expression without defining a NO RANGE partition if possible.

  • If a partitioning expression is defined with multiple ranges and you specify either a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function in the starting expression of the first range, the ending expression of the last range, or both, then an ALTER TABLE TO CURRENT reconciliation is optimized.

    If you specify a DATE or CURRENT_DATE function in ranges other than the starting expression of the first range or the ending expression of the last range, then Teradata Database scans all rows during reconciliation and repartitions them as is necessary.

  • If you specify either a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function in an ending expression, then the following things are true.
    • Specifying the DATE or CURRENT_DATE functions in RANGE_N conditions generally simplifies the ALTER TABLE logic, making it possible for you to submit ALTER TABLE TO CURRENT statements periodically instead of submitting ALTER TABLE statements to ADD and DROP ranges.
    • If you specify RANGE_N functions at levels greater than the first, you should specify DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions for both the starting expression of the first range and the ending Period expression of the last range.

      You can further optimize reconciliations by dropping and adding the same number of partitions; otherwise, Teradata Database must scan all of the rows in the table.

  • Specifying a DATE or CURRENT_DATE function in RANGE_N partitioning expressions often simplifies the ALTER TABLE logic for reconciling the values.

    Because of this, you can periodically submit an ALTER TABLE TO CURRENT statement instead of specifying an ALTER TABLE statement to ADD or DROP ranges.

  • If you specify RANGE_N functions in partitioning levels about the first level, both the starting expression of the first range and the ending expression of the last range should specify either a DATE, a CURRENT_DATE, or a CURRENT_TIMESTAMP function.

    Reconciliation operations should drop and add the same number of partitions; otherwise, Teradata Database must scan all of the rows in the specified table or join index.