Managing Partitions
Vertica provides various options to let you manage and monitor the partitions you create.
PARTITION_TABLE Function
The function PARTITION_TABLE physically separates partitions into separate containers. Only ROS containers with more than one distinct value participate in the split.
The following example creates a simple table states and partitions the data by state:
=> CREATE TABLE states (year INTEGER NOT NULL, state VARCHAR NOT NULL) PARTITION BY state; => CREATE PROJECTION states_p (state, year) AS SELECT * FROM states ORDER BY state, year UNSEGMENTED ALL NODES;
Run PARTITION_TABLE to partition the table states:
=> SELECT PARTITION_TABLE('states');
PARTITION_TABLE
---------------------------------------------------------------------------------
Task: partition operation
(Table: public.states) (Projection: public.states_p)
(1 row)
PARTITIONS System Table
You can display partition metadata, one row per partition key, per ROS container, by querying the PARTITIONS system table.
Given the unsegmented projection states_p replicated across three nodes, the following query on the PARTITIONS table returns twelve rows, representing twelve ROS containers:
=> SELECT PARTITION_KEY, ROS_ID, ROS_SIZE_BYTES, ROS_ROW_COUNT, NODE_NAME FROM partitions WHERE PROJECTION_NAME='states_p' order by ROS_ID; PARTITION_KEY | ROS_ID | ROS_SIZE_BYTES | ROS_ROW_COUNT | NODE_NAME ---------------+-------------------+----------------+---------------+------------------ VT | 45035996281231297 | 95 | 14 | v_vmart_node0001 PA | 45035996281231309 | 92 | 11 | v_vmart_node0001 NY | 45035996281231321 | 90 | 9 | v_vmart_node0001 MA | 45035996281231333 | 96 | 15 | v_vmart_node0001 VT | 49539595902704977 | 95 | 14 | v_vmart_node0002 PA | 49539595902704989 | 92 | 11 | v_vmart_node0002 NY | 49539595902705001 | 90 | 9 | v_vmart_node0002 MA | 49539595902705013 | 96 | 15 | v_vmart_node0002 VT | 54043195530075651 | 95 | 14 | v_vmart_node0003 PA | 54043195530075663 | 92 | 11 | v_vmart_node0003 NY | 54043195530075675 | 90 | 9 | v_vmart_node0003 MA | 54043195530075687 | 96 | 15 | v_vmart_node0003 (12 rows)
Restrictions
You cannot use volatile functions in a PARTITION BY expression. For example, the value of TIMESTAMPTZ depends on user settings.
