Partition a Table By More Than One Column

Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert

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/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/partition-clause.htm

Have fun!

Sign In or Register to comment.