When the logic of a significant number of your DML requests against a table typically specify DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions, consider taking advantage of the functionality provided by updatable current dates and updatable current timestamps to partition the rows of the tables you most frequently access with such queries.
A partitioned primary index enables Vantage to partition the rows of a table or noncompressed join index so that row subsets can be accessed efficiently without resorting to full-table scans. If the partitioning expression is defined using an updatable current date or updatable current timestamp, the partition that contains the most recent rows can be defined to be as narrow as possible to optimize efficient access to those rows. An additional benefit of an updatable current date or updatable current timestamp for a partitioning is that the partitioning expression can be designed not to need to be changed as a function of time.
To do this, you can specify the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions in the partitioning expression of a table or noncompressed join index and then periodically update the resolution of their values. This enables rows to be repartitioned on the newly resolved values of the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions whenever necessary. You can update the resolution of your partitioning scheme by submitting appropriate ALTER TABLE TO CURRENT statements.
To realize the optimal benefit from partitioning a table or join index using updatable current date or updatable current timestamp values, you must periodically reconcile their partitioning to a more recent date or timestamp value using ALTER TABLE TO CURRENT statements. See ALTER TABLE TO CURRENT Usage Notes.
Although you can specify the DATE, CURRENT_DATE, and CURRENT_TIMESTAMP functions anywhere in a partitioning expression where a date or timestamp constant is valid, optimize the partition definitions to be reconcilable optimally using ALTER TABLE TO CURRENT statements.
Use care in designing your partitioning expressions to minimize the expense of reconciling the rows partitioned on newly resolved DATE, CURRENT_DATE, CURRENT_TIMESTAMP, or CURRENT_DATE constants using ALTER TABLE TO CURRENT statements. Reconciling the row can be expensive in terms of the time required to scan the table to locate the rows that need to be reconciled and the time required to move or delete rows.
If more than a small percentage of the rows in a table or noncompressed join index must be reconciled without being able to take advantage of the optimizations that are available to you, another form of partitioning may be more advantageous for your applications. An example of such an optimization is full partition deletion of rows.
- Optimal Reconciliation of CASE_N Partitioning Expressions Based on Updatable Current Date and Timestamp
- Optimal Reconciliation of RANGE_N PPI Expressions Based on Updatable Current Date and Timestamp
- Optimal Reconciliation of CASE Partitioning Expressions Based on Updatable Current Date and Timestamp
- Optimal Reconciliation of Partitioning Expressions Based on Updatable Current Date and Timestamp
- You can specify the DATE and CURRENT_DATE functions anywhere in a partitioning expression that a date is valid.
You can specify a CURRENT_TIMESTAMP function anywhere in a partitioning expression that a timestamp is valid.
However, the result of a test value must not have the TIMESTAMP data type in a RANGE_N function.
- The report produced by a SHOW TABLE or SHOW JOIN INDEX statement returns the user-specified partitioning expression containing the user-specified expression with DATE, CURRENT_DATE, or CURRENT_TIMESTAMP and not the resolved date or timestamp value.
You can retrieve the last resolved DATE or CURRENT_DATE value, CURRENT_TIMESTAMP value, or both using ResolvedDTSV[X] .
- The report produced by a SHOW request (see SHOW request) returns the user-specified partitioning expression.
The report produced by a SHOW QUALIFIED request returns the last resolved date and timestamp for the DATE, CURRENT_DATE, and CURRENT_TIMESTAMP specified in the partitioning expression.
- The following statements are true for partitioning expressions that specify a RANGE_N function with a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function.
Partitioning Expression ALTER TABLE Statement Cannot Do the Following Single-level ADD or DROP ranges to the partitioning expression. Multilevel ADD or DROP ranges to the level of the partitioning expression. Instead, you must use an ALTER TABLE TO CURRENT statement to ADD or DROP such ranges from a table. See ALTER TABLE TO CURRENT.
- If you create a new table using the CREATE TABLE … AS statement, the following rules apply.
- If the source table has a partitioning defined using the DATE, CURRENT_DATE, or CURRENT_TIMESTAMP functions, and the new table is a copy of the source table and its index definitions, then Vantage copies the newly created table with the resolved date and timestamp from the source table.
- In all other cases, Vantage resolves any DATE, CURRENT_DATE, or CURRENT_TIMESTAMP values in the partitioning definition of the target table to the creation date, creation timestamp, or both, for the new table.