This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

Partition functions

This section contains partition management functions specific to Vertica.

1: CALENDAR_HIERARCHY_DAY
2: COPY_PARTITIONS_TO_TABLE
3: DROP_PARTITIONS
4: DUMP_PROJECTION_PARTITION_KEYS
5: DUMP_TABLE_PARTITION_KEYS
6: MOVE_PARTITIONS_TO_TABLE
7: PARTITION_PROJECTION
8: PARTITION_TABLE
9: PURGE_PARTITION
10: SWAP_PARTITIONS_BETWEEN_TABLES

This section contains partition management functions specific to Vertica.

1 - 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.

2 - COPY_PARTITIONS_TO_TABLE

Copies partitions from one table to another.

Copies partitions from one table to another. This lightweight partition copy increases performance by initially sharing the same storage between two tables. After the copy operation is complete, the tables are independent of each other. Users can perform operations on one table without impacting the other. These operations can increase the overall storage required for both tables.

Note

Although they share storage space, Vertica considers the partitions as discrete objects for license capacity purposes. For example, copying a one TB partition would only consume one TB of space. Your Vertica license, however, considers them as separate objects consuming two TB of space.

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

Behavior type

Volatile

Syntax

COPY_PARTITIONS_TO_TABLE (
    '[[{namespace. | database. }]schema.]source-table',
    'min-range-value',
    'max-range-value',
    '[[{namespace. | database. }]schema.]target-table'
     [, 'force-split']
)

Arguments

{ namespace. | database. }

Name of the database or namespace that contains table:

Database name: If specified, it must be the current database. In Eon Mode databases, the database name is valid only if the object is in the default namespace.
Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

You cannot specify both a database and namespace name.

For Eon Mode databases, the namespaces of staging-table and target-table must have the same shard count.

schema

Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.

source-table

The source table of the partitions to copy.

min-range-value, max-range-value

The minimum and maximum value of partition keys to copy, where min‑range‑value must be ≤ max‑range‑value. To specify a single partition key, min‑range‑value and max‑range‑value must be equal.

target-table

The target table of the partitions to copy. If the table does not exist, Vertica creates a table from the source table's definition, by calling CREATE TABLE with LIKE and INCLUDING PROJECTIONS clause. The new table inherits ownership from the source table. For details, see Replicating a table.

force-split

Optional Boolean argument, specifies whether to split ROS containers if the range of partition keys spans multiple containers or part of a single container:

true: Split ROS containers as needed.
false (default): Return with an error if ROS containers must be split to implement this operation.

Privileges

Non-superuser, one of the following:

Owner of source and target tables
TRUNCATE (if force-split is true) and SELECT on the source table, INSERT on the target table

If the target table does not exist, you must also have CREATE privileges on the target schema to enable table creation.

Table attribute requirements

The following attributes of both tables must be identical:

Column definitions, including NULL/NOT NULL constraints
Segmentation
Partition clause
Number of projections
Shard count (Eon Mode only)
Projection sort order
Primary and unique key constraints. However, the key constraints do not have to be identically enabled. For more information on constraints, see Constraints.

Note
If the target table has primary or unique key constraints enabled and copying or moving the partitions will insert duplicate key values into the target table, Vertica rolls back the operation.
Check constraints. For MOVE_PARTITIONS_TO_TABLE and COPY_PARTITIONS_TO_TABLE, Vertica enforces enabled check constraints on the target table only. For SWAP_PARTITIONS_BETWEEN_TABLES, Vertica enforces enabled check constraints on both tables. If there is a violation of an enabled check constraint, Vertica rolls back the operation.
Number and definitions of text indices.

Additionally, If access policies exist on the source table, the following must be true:

Access policies on both tables must be identical.
One of the following must be true:
- The executing user owns the source table.
- AccessPolicyManagementSuperuserOnly is set to true. See Managing access policies for details.

Table restrictions

The following restrictions apply to the source and target tables:

If the source and target partitions are in different storage tiers, Vertica returns a warning but the operation proceeds. The partitions remain in their existing storage tier.
The target table cannot be immutable.
The following tables cannot be used as sources or targets:
- Temporary tables
- Virtual tables
- System tables
- External tables

Examples

If you call COPY_PARTITIONS_TO_TABLE and the target table does not exist, the function creates the table automatically. In the following example, the target table partn_backup.tradfes_200801 does not exist. COPY_PARTITIONS_TO_TABLE creates the table and replicates the partition. Vertica also copies all the constraints associated with the source table except foreign key constraints.

=> SELECT COPY_PARTITIONS_TO_TABLE (
          'prod_trades',
          '200801',
          '200801',
          'partn_backup.trades_200801');
COPY_PARTITIONS_TO_TABLE
-------------------------------------------------
 1 distinct partition values copied at epoch 15.
(1 row)

3 - DROP_PARTITIONS

Drops the specified table partition keys.

Note

This function supersedes meta-function DROP_PARTITION, which was deprecated in Vertica 9.0.

Drops the specified table partition keys.

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

Behavior type

Volatile

Syntax

DROP_PARTITIONS (
    '[[database.]schema.]table-name',
    'min-range-value',
    'max-range-value'
    [, 'force-split']
)

Arguments

[database.]schema

Database and schema. The default schema is public. If you specify a database, it must be the current database.

table-name

The target table. The table cannot be used as a dimension table in a pre-join projection and cannot have out-of-date (unrefreshed) projections.

min-range-value, max-range-value

The minimum and maximum value of partition keys to drop, where min‑range‑value must be ≤ max‑range‑value. To specify a single partition key, min‑range‑value and max‑range‑value must be equal.

force-split

Optional Boolean argument, specifies whether to split ROS containers if the range of partition keys spans multiple containers or part of a single container:

true: Split ROS containers as needed.
false (default): Return with an error if ROS containers must be split to implement this operation.

Note

In rare cases, DROP_PARTITIONS executes at the same time as a mergeout operation on the same ROS container. As a result, the function cannot split the container as specified and returns with an error. When this happens, call DROP_PARTITIONS again.

Privileges

One of the following:

DBADMIN
Table owner
USAGE privileges on the table schema and TRUNCATE privileges on the table

Examples

See Dropping partitions.

4 - DUMP_PROJECTION_PARTITION_KEYS

Dumps the partition keys of the specified projection.

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

Behavior type

Volatile

Syntax

DUMP_PROJECTION_PARTITION_KEYS( '[[database.]schema.]projection-name')

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
projection-name: Projection name

Privileges

Non-superuser: TRUNCATE on anchor table

Examples

The following statements create the table and projection online_sales.online_sales_fact and online_sales.online_sales_fact_rep, respectively, and partitions table data by the column call_center_key:

=> CREATE TABLE online_sales.online_sales_fact
(
    sale_date_key int NOT NULL,
    ship_date_key int NOT NULL,
    product_key int NOT NULL,
    product_version int NOT NULL,
    customer_key int NOT NULL,
    call_center_key int NOT NULL,
    online_page_key int NOT NULL,
    shipping_key int NOT NULL,
    warehouse_key int NOT NULL,
    promotion_key int NOT NULL,
    pos_transaction_number int NOT NULL,
    sales_quantity int,
    sales_dollar_amount float,
    ship_dollar_amount float,
    net_dollar_amount float,
    cost_dollar_amount float,
    gross_profit_dollar_amount float,
    transaction_type varchar(16)
)
PARTITION BY (online_sales_fact.call_center_key);

=> CREATE PROJECTION online_sales.online_sales_fact_rep AS SELECT * from online_sales.online_sales_fact unsegmented all nodes;

The following DUMP_PROJECTION_PARTITION_KEYS statement dumps the partition key from the projection online_sales.online_sales_fact_rep:

=> SELECT DUMP_PROJECTION_PARTITION_KEYS('online_sales.online_sales_fact_rep');

Partition keys on node v_vmart_node0001
  Projection 'online_sales_fact_rep'
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 200
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 199
   ...
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 2
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 1

 Partition keys on node v_vmart_node0002
  Projection 'online_sales_fact_rep'
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 200
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: 199
...
(1 row)

5 - DUMP_TABLE_PARTITION_KEYS

Dumps the partition keys of all projections for the specified table.

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

Behavior type

Volatile

Syntax

DUMP_TABLE_PARTITION_KEYS ( '[[database.]schema.]table-name' )

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
table-name: Name of the table

Privileges

Non-superuser: TRUNCATE on table

Examples

The following example creates a simple table called states and partitions the data by state:

=> CREATE TABLE states (year INTEGER NOT NULL,
       state VARCHAR NOT NULL)
       PARTITION BY state;
=> CREATE PROJECTION states_p (state, year) AS
       SELECT * FROM states
       ORDER BY state, year UNSEGMENTED ALL NODES;

Now dump the partition keys of all projections anchored on table states:

=> SELECT DUMP_TABLE_PARTITION_KEYS( 'states' );
      DUMP_TABLE_PARTITION_KEYS                                                               --------------------------------------------------------------------------------------------
 Partition keys on node v_vmart_node0001
  Projection 'states_p'
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: VT
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: PA
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: NY
   Storage [ROS container]
     No of partition keys: 1
     Partition keys: MA

 Partition keys on node v_vmart_node0002
...
(1 row)

6 - MOVE_PARTITIONS_TO_TABLE

Moves partitions from one table to another.

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

Behavior type

Volatile

Syntax

MOVE_PARTITIONS_TO_TABLE (
    '[[{namespace. | database. }]schema.]source-table',
    'min-range-value',
    'max-range-value',
    '[[{namespace. | database. }]schema.]target-table'
     [, force-split]
)

Arguments

{ namespace. | database. }

Name of the database or namespace that contains table:

Database name: If specified, it must be the current database. In Eon Mode databases, the database name is valid only if the object is in the default namespace.
Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

You cannot specify both a database and namespace name.

For Eon Mode databases, the namespaces of staging-table and target-table must have the same shard count.

schema

Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.

source-table

The source table of the partitions to move.

min-range-value, max-range-value

The minimum and maximum value of partition keys to move, where min‑range‑value must be ≤ max‑range‑value. To specify a single partition key, min‑range‑value and max‑range‑value must be equal.

target-table

The target table of the partitions to move. If the table does not exist, Vertica creates a table from the source table's definition, by calling CREATE TABLE with LIKE and INCLUDING PROJECTIONS clause. The new table inherits ownership from the source table. For details, see Replicating a table.

force-split

Optional Boolean argument, specifies whether to split ROS containers if the range of partition keys spans multiple containers or part of a single container:

true: Split ROS containers as needed.
false (default): Return with an error if ROS containers must be split to implement this operation.

Privileges

Non-superuser, one of the following:

Owner of source and target tables
SELECT, TRUNCATE on the source table, INSERT on the target table

If the target table does not exist, you must also have CREATE privileges on the target schema to enable table creation.

Table attribute requirements

The following attributes of both tables must be identical:

Column definitions, including NULL/NOT NULL constraints
Segmentation
Partition clause
Number of projections
Shard count (Eon Mode only)
Projection sort order
Primary and unique key constraints. However, the key constraints do not have to be identically enabled. For more information on constraints, see Constraints.

Note
If the target table has primary or unique key constraints enabled and copying or moving the partitions will insert duplicate key values into the target table, Vertica rolls back the operation.
Check constraints. For MOVE_PARTITIONS_TO_TABLE and COPY_PARTITIONS_TO_TABLE, Vertica enforces enabled check constraints on the target table only. For SWAP_PARTITIONS_BETWEEN_TABLES, Vertica enforces enabled check constraints on both tables. If there is a violation of an enabled check constraint, Vertica rolls back the operation.
Number and definitions of text indices.

Additionally, If access policies exist on the source table, the following must be true:

Access policies on both tables must be identical.
One of the following must be true:
- The executing user owns the source table.
- AccessPolicyManagementSuperuserOnly is set to true. See Managing access policies for details.

Table restrictions

The following restrictions apply to the source and target tables:

If the source and target partitions are in different storage tiers, Vertica returns a warning but the operation proceeds. The partitions remain in their existing storage tier.
The target table cannot be immutable.
The following tables cannot be used as sources or targets:
- Temporary tables
- Virtual tables
- System tables
- External tables

Examples

See Archiving partitions.

7 - PARTITION_PROJECTION

Splits containers for a specified projection.

Splits ROS containers for a specified projection. PARTITION_PROJECTION also purges data while partitioning ROS containers if deletes were applied before the AHM epoch.

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

Behavior type

Volatile

Syntax

PARTITION_PROJECTION ( '[[database.]schema.]projection')

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
projection``: The projection to partition.

Privileges

Table owner
USAGE privilege on schema

Examples

In this example, PARTITION_PROJECTION forces a split of ROS containers on the states_p projection:

=> SELECT PARTITION_PROJECTION ('states_p');
  PARTITION_PROJECTION
------------------------
 Projection partitioned
(1 row)

8 - PARTITION_TABLE

Invokes the to reorganize ROS storage containers as needed to conform with the current partitioning policy.

Invokes the Tuple Mover to reorganize ROS storage containers as needed to conform with the current partitioning policy.

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

Behavior type

Volatile

Syntax

PARTITION_TABLE ( '[schema.]table-name')

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
table-name: The table to partition.

Privileges

Table owner
USAGE privilege on schema

Restrictions

You cannot run PARTITION_TABLE on a table that is an anchor table for a live aggregate projection or a Top-K projection.
To reorganize storage to conform to a new policy, run PARTITION_TABLE after changing the partition GROUP BY expression.

9 - PURGE_PARTITION

Purges a table partition of deleted rows.

Purges a table partition of deleted rows. Similar to PURGE and PURGE_PROJECTION, this function removes deleted data from physical storage so you can reuse the disk space. PURGE_PARTITION removes data only from the AHM epoch and earlier.

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

Behavior type

Volatile

Syntax

PURGE_PARTITION ( '[[database.]schema.]table', partition-key )

Parameters

[database.]schema: Database and schema. The default schema is public. If you specify a database, it must be the current database.
table: The partitioned table to purge.
partition-key: The key of the partition to purge.

Privileges

Table owner
USAGE privilege on schema

Examples

The following example lists the count of deleted rows for each partition in a table, then calls PURGE_PARTITION() to purge the deleted rows from the data.

=> SELECT partition_key,table_schema,projection_name,sum(deleted_row_count)
   AS deleted_row_count FROM partitions
   GROUP BY partition_key,table_schema,projection_name
   ORDER BY partition_key;

 partition_key | table_schema | projection_name | deleted_row_count
---------------+--------------+-----------------+-------------------
 0             | public       | t_super         |                 2
 1             | public       | t_super         |                 2
 2             | public       | t_super         |                 2
 3             | public       | t_super         |                 2
 4             | public       | t_super         |                 2
 5             | public       | t_super         |                 2
 6             | public       | t_super         |                 2
 7             | public       | t_super         |                 2
 8             | public       | t_super         |                 2
 9             | public       | t_super         |                 1
(10 rows)
=> SELECT PURGE_PARTITION('t',5); -- Purge partition with key 5.
                            purge_partition
------------------------------------------------------------------------
 Task: merge partitions
(Table: public.t) (Projection: public.t_super)
(1 row)

=> SELECT partition_key,table_schema,projection_name,sum(deleted_row_count)
   AS deleted_row_count FROM partitions
   GROUP BY partition_key,table_schema,projection_name
   ORDER BY partition_key;


 partition_key | table_schema | projection_name | deleted_row_count
---------------+--------------+-----------------+-------------------
 0             | public       | t_super         |                 2
 1             | public       | t_super         |                 2
 2             | public       | t_super         |                 2
 3             | public       | t_super         |                 2
 4             | public       | t_super         |                 2
 5             | public       | t_super         |                 0
 6             | public       | t_super         |                 2
 7             | public       | t_super         |                 2
 8             | public       | t_super         |                 2
 9             | public       | t_super         |                 1
(10 rows)

10 - SWAP_PARTITIONS_BETWEEN_TABLES

Swaps partitions between two tables.

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

Behavior type

Volatile

Syntax

SWAP_PARTITIONS_BETWEEN_TABLES (
    '[[{namespace. | database. }]schema.]staging-table',
    'min-range-value',
    'max-range-value',
    '[[{namespace. | database. }]schema.]target-table'
     [, force-split]
)

Arguments

{ namespace. | database. }

Name of the database or namespace that contains table:

Database name: If specified, it must be the current database. In Eon Mode databases, the database name is valid only if the object is in the default namespace.
Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, Vertica assumes the object is in the default namespace.

You cannot specify both a database and namespace name.

For Eon Mode databases, the namespaces of staging-table and target-table must have the same shard count.

schema

Name of the schema, by default public. If you specify the namespace or database name, you must provide the schema name, even if the schema is public.

staging-table

The staging table from which to swap partitions.

min-range-value, max-range-value

The minimum and maximum value of partition keys to swap, where min‑range‑value must be ≤ max‑range‑value. To specify a single partition key, min‑range‑value and max‑range‑value must be equal.

target-table

The table to which the partitions are to be swapped. The target table cannot be the same as the staging table.

force-split

Optional Boolean argument, specifies whether to split ROS containers if the range of partition keys spans multiple containers or part of a single container:

true: Split ROS containers as needed.
false (default): Return with an error if ROS containers must be split to implement this operation.

Privileges

Non-superuser, one of the following:

Owner of source and target tables
Target and source tables: TRUNCATE, INSERT, SELECT

Requirements

The following attributes of both tables must be identical:

Column definitions, including NULL/NOT NULL constraints
Segmentation
Partition clause
Number of projections
Shard count (Eon Mode only)
Projection sort order
Primary and unique key constraints. However, the key constraints do not have to be identically enabled. For more information on constraints, see Constraints.

Note
If the target table has primary or unique key constraints enabled and copying or moving the partitions will insert duplicate key values into the target table, Vertica rolls back the operation.
Check constraints. For MOVE_PARTITIONS_TO_TABLE and COPY_PARTITIONS_TO_TABLE, Vertica enforces enabled check constraints on the target table only. For SWAP_PARTITIONS_BETWEEN_TABLES, Vertica enforces enabled check constraints on both tables. If there is a violation of an enabled check constraint, Vertica rolls back the operation.
Number and definitions of text indices.

Additionally, If access policies exist on the source table, the following must be true:

Access policies on both tables must be identical.
One of the following must be true:
- The executing user owns the target table.
- AccessPolicyManagementSuperuserOnly is set to true.

Restrictions

The following restrictions apply to the source and target tables:

If the source and target partitions are in different storage tiers, Vertica returns a warning but the operation proceeds. The partitions remain in their existing storage tier.
The target table cannot be immutable.
The following tables cannot be used as sources or targets:
- Temporary tables
- Virtual tables
- System tables
- External tables

Examples

See Swapping partitions.

Partition functions

1 - CALENDAR_HIERARCHY_DAY

Behavior type

Syntax

Parameters

Important

Usage

Examples

2 - COPY_PARTITIONS_TO_TABLE

Note

Behavior type

Syntax

Arguments

Privileges

Table attribute requirements

Note

Table restrictions

Examples

See also

3 - DROP_PARTITIONS

Note

Behavior type

Syntax

Arguments

Note

Privileges

Examples

See also

4 - DUMP_PROJECTION_PARTITION_KEYS

Behavior type

Syntax

Parameters

Privileges

Examples

See also

5 - DUMP_TABLE_PARTITION_KEYS

Behavior type

Syntax

Parameters

Privileges

Examples

See also

6 - MOVE_PARTITIONS_TO_TABLE

Behavior type

Syntax

Arguments

Privileges

Table attribute requirements

Note

Table restrictions

Examples

See also

7 - PARTITION_PROJECTION

Behavior type

Syntax

Parameters

Privileges

Examples

See also

8 - PARTITION_TABLE

Behavior type

Syntax

Parameters

Privileges

Restrictions

See also

9 - PURGE_PARTITION

Behavior type

Syntax

Parameters

Privileges

Examples

See also

10 - SWAP_PARTITIONS_BETWEEN_TABLES

Behavior type

Syntax

Arguments

Privileges

Requirements

Note