Creating Table Partitions

   This post explains how to do the table partition with examples.

    Vertica supports data partitioning at the table level, which divides one large table into smaller pieces. Partitions are a property of the table.

Example:

Table Creation script:

create table sample (id int,name varchar(10))

insert into sample values (1,'a');

insert into sample values (1,'b');

insert into sample values (1,'c');

insert into sample values (2,'a');

insert into sample values (2,'b');

insert into sample values (2,'c');

• I am going to create partitions based on ID Column. So first we need to set ID column as NOT NULL.

ALTER TABLE sample  alter column id set not null;

•  Then run the table partition script

ALTER TABLE sample  PARTITION BY id REORGANIZE;

• Use below commands to check the table partition status. If partition reorganize status is 100% then partitioning is completed. If any error is happened then error table will populate.
  select * from V_MONITOR.PARTITION_STATUS where table_name='sample'

  

select * from V_MONITOR.PARTITION_REORGANIZE_ERRORS where table_name='sample' 

See Also: Drop Partition