Partitioning existing table data

Use ALTER TABLE to partition or repartition an existing table, as specified by the PARTITION BY clause:

ALTER TABLE table-name PARTITION BY partition-expression [ GROUP BY group-expression ] [ REORGANIZE ];

For example, you might repartition the store_orders table, defined earlier. The following ALTER TABLE divides all store_orders data into monthly partitions for each year, each partition key identifying the order date year and month:

=> ALTER TABLE store_orders
     PARTITION BY EXTRACT(YEAR FROM order_date)*100 + EXTRACT(MONTH FROM order_date)
         GROUP BY EXTRACT(YEAR from order_date)*100 + EXTRACT(MONTH FROM order_date);
NOTICE 8364:  The new partitioning scheme will produce partitions in 42 physical storage containers per projection
WARNING 6100:  Using PARTITION expression that returns a Numeric value
HINT:  This PARTITION expression may cause too many data partitions.  Use of an expression that returns a more accurate value, such as a regular VARCHAR or INT, is encouraged
WARNING 4493:  Queries using table "store_orders" may not perform optimally since the data may not be repartitioned in accordance with the new partition expression
HINT:  Use "ALTER TABLE public.store_orders REORGANIZE;" to repartition the data

After executing this statement, Vertica drops existing partition keys. However, the partition clause omits REORGANIZE, so existing data remains stored according to the previous partition clause. This can put table partitioning in an inconsistent state and adversely affect query performance, DROP_PARTITIONS, and node recovery. In this case, you must explicitly request Vertica to reorganize existing data into new partitions, in one of the following ways:

• Issue ALTER TABLE...REORGANIZE:

  ALTER TABLE table-name REORGANIZE;
  

• Call the Vertica meta-function PARTITION_TABLE.

For example:

=> ALTER TABLE store_orders REORGANIZE;
NOTICE 4785:  Started background repartition table task
ALTER TABLE

ALTER TABLE...REORGANIZE and PARTITION_TABLE operate identically: both split any ROS containers where partition keys do not conform with the new partition clause. On executing its next mergeout, the Tuple Mover merges partitions into the appropriate ROS containers.