Partition Clause

Specifies partitioning of table data, through a PARTITION BY clause in the table definition:

PARTITION BY partition‑expression [ GROUP BY group‑expression ] [ ActivePartitionCountExpr ]
PARTITION BY partition‑expression

For each table row, resolves to a partition key that is derived from one or more table columns.

Avoid partitioning tables on LONG VARBINARY and LONG VARCHAR columns. Doing so can adversely impact performance.
GROUP BY group‑expression

For each table row, resolves to a partition group key that is derived from the partition key. Vertica uses group keys to merge partitions into separate partition groups. GROUP BY must use the same expression as PARTITION BY. For example:

...PARTITION BY (i+j) GROUP BY (
     CASE WHEN (i+j) < 5 THEN 1 
          WHEN (i+j) < 10 THEN 2 
          ELSE 3);

For details on partitioning table data by groups, see Partition Grouping and Hierarchical Partitioning in the Administrator's Guide.
ActivePartitionCountExpr

Specifies how many partitions are active for this table, specified as follows:

  • In partition clause of CREATE TABLE:
    ACTIVEPARTITIONCOUNT integer
  • In partition clause of ALTER TABLE:
    SET ACTIVEPARTITIONCOUNT integer

This setting supersedes configuration parameter ActivePartitionCount. For details on usage, see Active and Inactive Partitions in the Administrator's Guide.

Partitioning Requirements and Restrictions

PARTITION BY expressions can specify leaf expressions, functions, and operators. The following requirements and restrictions apply:

  • All table projections must include all columns referenced in the expression; otherwise, Vertica cannot resolve the expression.
  • The expression can reference multiple columns, but it must resolve to a single non-null value for each row.
  • All leaf expressions must be constants or table columns.
  • All other expressions must be functions and operators. The following restrictions apply to functions:
  • The expression cannot include queries.

GROUP BY expressions do not support modulo (%) operations.

Examples

See Defining Partitions and Hierarchical Partitioning in the Administrator's Guide

See Also

Partitioning Tables in the Administrator's Guide