CALENDAR_HIERARCHY_DAY

Specifies to group DATE partition keys into a hierarchy of years, months, and days.

Specifies to group DATE partition keys into a hierarchy of years, months, and days. The Vertica Tuple Mover regularly evaluates partition keys against the current date, and merges partitions as needed into the appropriate year and month partition groups.

This is a meta-function. You must call meta-functions in a top-level SELECT statement.

Behavior type

Volatile

Syntax

CALENDAR_HIERARCHY_DAY( partition-expression[, active-months[, active-years] ] )

Parameters

partition-expression

The DATE expression on which to group partition keys, which must be identical to the table's PARTITION BY expression.

active-months

An integer ≥ 0 that specifies how many months preceding MONTH(CURRENT_DATE) to store unique partition keys in separate partitions.

If you specify 1, only partition keys of the current month are stored in separate partitions.

If you specify 0, all partition keys of the current month are merged into a partition group for that month.

For details, see Hierarchical partitioning.

Default: 2

active-years

An integer ≥ 0, specifies how many years preceding YEAR(CURRENT_DATE) to partition group keys by month in separate partitions.

If you specify 1, only partition keys of the current year are stored in month partition groups.

If you specify 0, all partition keys of the current and previous years are merged into year partition groups.

For details, see Hierarchical partitioning.

Default: 2

Important

The CALENDAR_HIERARCHY_DAY algorithm assumes that most table activity is focused on recent dates. Setting active-years and active-months to a low number ≥ 2 serves to isolate most merge activity to date-specific containers, and incurs minimal overhead. Vertica recommends that you use the default setting of 2 for active-years and active-months. For most users, these settings achieve an optimal balance between ROS storage and performance.

As a best practice, never set active-years and active-months to 0.

Usage

Specify this function in a table partition clause, as its GROUP BY expression:

PARTITION BY partition-expression
  GROUP BY CALENDAR_HIERARCHY_DAY(
     group-expression
      [, active-months[, active-years] ] )

For example:

=> CREATE TABLE public.store_orders
(
    order_no int,
    order_date timestamp NOT NULL,
    shipper varchar(20),
    ship_date date
);
...
=> ALTER TABLE public.store_orders
      PARTITION BY order_date::DATE
      GROUP BY CALENDAR_HIERARCHY_DAY(order_date::DATE, 3, 2) REORGANIZE;

Examples

See Hierarchical partitioning.