In Vertica, actual physical data is stored in projections and not in tables. This tutorials provides most of the information related to projections in Vertica.

Vertica Projections

Projections are one kind of objects in Vertica where actual data for tables is stored. There can be multiple projections on a table but there will/should be atleast 1 projection which must have all the columns included in projection definition and such projections with all columns included are called 'Super Projection'. Other projections may be created considering usage of columns in the query and it may have few specific columns required for query. Each projection will have its own set of data.


→ Projections Definition: Getting the projections definition.

SELECT EXPORT_OBJECTS('','schema_name.projection_name');

→ List of projections: Checking all the projections created on vertica table.

SELECT projection_schema, projection_name, projection_basename,is_super_projection FROM PROJECTIONS WHERE LOWER(anchor_table_name) = 'table_name' ;
 
 Note: Value 'true' in is_super_projection column specify that it is a Super Projection.

→ Projection Refresh Status: To check the projection refresh status.

select * from PROJECTION_REFRESHES where projection_name='projection_name' ;
Note:
→ Size of Projection: Finding the size of projection in Vertica.

SELECT projection_schema, projection_name, SUM (used_bytes) / (1024^3) AS size_in_gb FROM v_monitor.projection_storage WHERE LOWER(projection_schema) = 'schema_name' AND LOWER(projection_name) = 'projection_name' GROUP BY projection_schema, projection_name;


→ Restriction in Projections: There are some restrictions while creating projections. Few of them are listed below: