Partition a Table By More Than One Column

Posted April 3, 2019 by Jim Knicely, Vertica Principal Solution Architect

The Vertica partitioning capability divides one large table into smaller pieces based on values in one or more columns. Partitions can make data lifecycle management easier and improve the performance of queries whose predicate is included in the partition expression.

To partition a table by more than one column, use the HASH function!

Example: dbadmin=> CREATE TABLE some_fact_table (c1 INT NOT NULL, c2 INT NOT NULL, c3 VARCHAR(1) NOT NULL, c4 INT, c5 VARCHAR(100)) PARTITION BY (c1, c2); ERROR 4331: PARTITION BY expression cannot return a tuple dbadmin=> CREATE TABLE some_fact_table (c1 INT NOT NULL, c2 INT NOT NULL, c3 VARCHAR(1) NOT NULL, c4 INT, c5 VARCHAR(100)) PARTITION BY HASH(c1, c2); CREATE TABLE Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/partition-clause.htm

Have fun!