Optimal Reconciliation of RANGE_N PPI Expressions Based on Updatable Current Date and Timestamp - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
ft:locale
en-US
ft:lastEdition
2024-12-11
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905
The following rules and guidelines apply to specifying RANGE_N partitioning expressions that can be reconciled optimally using ALTER TABLE TO CURRENT statements. See ALTER TABLE TO CURRENT Usage Notes.
  • 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 Vantage 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. Therefore, if you specify a CURRENT_TIMESTAMP function in the test value, you must use the function 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 Vantage drops all 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.
    NO RANGE Result
    Specified Scans rows from the NO RANGE partition to partition the rows based on the newly resolved date.
    Not specified Does not scan 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, the preceding bullets apply.

    Because Vantage must scan rows in the NO RANGE partition when you specify a NO RANGE partition, 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 Vantage scans all rows during reconciliation and repartitions rows as 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 typically simplifies the ALTER TABLE logic, allowing 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, 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, Vantage 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.

    Therefore, 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 must specify either a DATE, a CURRENT_DATE, or a CURRENT_TIMESTAMP function.

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