Projection Column Encoding
After you create a table and its projections, you can call ALTER TABLE...ALTER COLUMN to set or change the encoding type of an existing column in one or more projections. For example:
ALTER TABLE store.store_dimension ALTER COLUMN store_region ENCODING rle PROJECTIONS (store.store_dimension_p1_b0, store.store_dimension_p2);
In this example, the ALTER TABLE statement specifies to set RLE encoding on column store_region
for two projections: store_dimension_p1_b0
and store_dimension_p2
. The PROJECTIONS
list references the two projections by their projection name and base name, respectively. You can reference a projection either way; in both cases, the change is propagated to all buddies of the projection and stored in its DDL accordingly:
=> select export_objects('','store.store_dimension'); export_objects ------------------------------------------------------------------ CREATE TABLE store.store_dimension ( store_key int NOT NULL, store_name varchar(64), store_number int, store_address varchar(256), store_city varchar(64), store_state char(2), store_region varchar(64) ); CREATE PROJECTION store.store_dimension_p1 ( store_key, store_name, store_number, store_address, store_city, store_state, store_region ENCODING RLE ) AS SELECT store_dimension.store_key, store_dimension.store_name, store_dimension.store_number, store_dimension.store_address, store_dimension.store_city, store_dimension.store_state, store_dimension.store_region FROM store.store_dimension ORDER BY store_dimension.store_key SEGMENTED BY hash(store_dimension.store_key) ALL NODES KSAFE 1; CREATE PROJECTION store.store_dimension_p2 ( store_key, store_name, store_number, store_address, store_city, store_state, store_region ENCODING RLE ) AS SELECT ...
When you add or change a column's encoding type, it has no immediate effect on existing projection data. Vertica applies the encoding only to newly loaded data, and to existing data on mergeout.