- Specify DATE, CURRENT_DATE, or CURRENT_TIMESTAMP on only one side of a CASE_N conditional expression. The CASE_N specification can be optimal on the LHS or the RHS of the expression, but specified on both sides, the reconciliation cannot be optimized.
This optimization allows Vantage to skip 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 rows in the table to locate any rows that must be reconciled.
- If Vantage can determine that the rows of a partition remain in their current partition after any DATE, CURRENT_DATE, or CURRENT_TIMESTAMP values have been reconciled, Vantage does not scan the partition during reconciliation.
- 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)
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 */)
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)
Vantage does not scan the rows in the second partition, labeled "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*/)
Vantage scans the NO CASE partition. Because there is a gap of 3 months between the second and third expressions, this is a noncontiguous 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)
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 scans the partition 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 a DATE, CURRENT_DATE, or a CURRENT_TIMESTAMP function unless those partitions are defined before any conditional expressions that specify a DATE, CURRENT_DATE, or CURRENT_TIMESTAMP function. Otherwise, Vantage scans the partitions.
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.
The following rules and guidelines apply to specifying CASE_N partitioning expressions that can be reconciled optimally using ALTER TABLE TO CURRENT statements. See ALTER TABLE TO CURRENT Usage Notes.