15.00 - Rules for Reconciliation Optimization of RANGE_N PPI Expressions Based on Updatable Current Date and Updatable Current Timestamp - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Rules for Reconciliation Optimization of RANGE_N PPI Expressions Based on Updatable Current Date and Updatable Current Timestamp

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 requests (see “ALTER TABLE TO CURRENT” on page 140).

  • 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 request 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 request 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 request 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 requests periodically instead of submitting ALTER TABLE requests 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 request instead of specifying an ALTER TABLE request 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.