PROJECTIONS

Provides information about projections.

Column Name Data Type Description
PROJECTION_SCHEMA_ID INTEGER

A unique numeric ID that identifies the specific schema that contains the projection and is assigned by the Vertica catalog.

PROJECTION_SCHEMA VARCHAR

The name of the schema that contains the projection.

PROJECTION_ID INTEGER

A unique numeric ID that identifies the projection and is assigned by the Vertica catalog.

PROJECTION_NAME VARCHAR

The projection name for which information is listed.

PROJECTION_BASENAME VARCHAR The base name used for other projections. For auto-created projections, projection_basename is identical to the anchor_table_name. For a manually-created projection, projection_basename is the name you supply.
OWNER_ID INTEGER

A unique numeric ID that identifies the projection owner and is assigned by the Vertica catalog.

OWNER_NAME VARCHAR

The name of the projection's owner.

ANCHOR_TABLE_ID INTEGER

For pre-join projections, the unique numeric identification (OID) of the anchor table. If the projection is not a pre-join projection, this value is the OID of the table from which the projection was created.

A projection has only one anchor (fact) table.

ANCHOR_TABLE_NAME VARCHAR

For pre-join projections, the name of the anchor table. If the projection is not a pre-join projection, the name of the table from which the projection was created.

NODE_ID INTEGER

A unique numeric ID (OID) for any nodes that contain any unsegmented projections.

NODE_NAME VARCHAR

The names of any nodes that contain the projection.

This column returns information for unsegmented projections only.

IS_PREJOIN BOOLEAN

Specifies whether the projection is a pre-join projection, where t is true and f is false.

CREATED_EPOCH INTEGER

The epoch in which the projection was created.

CREATE_TYPE VARCHAR

The method in which the projection was created:

  • CREATE PROJECTION: A custom projection created using a CREATE PROJECTION statement.
  • CREATE TABLE: A superprojection that was automatically created when its associated table was created using a CREATE TABLE statement.
  • ALTER TABLE: The system automatically created the key projection in response to a non-empty table.
  • CREATE TABLE WITH PROJ CLAUSE: A superprojection created using a CREATE TABLE statement.
  • DELAYED_CREATION: A superprojection that was automatically created when data was loaded into its associated table.
  • DESIGNER: A new projection created by the Database Designer.
  • SYSTEM TABLE: A projection that was automatically created for a system table.

Rebalancing does not change the CREATE_TYPE value for a projection.

VERIFIED_FAULT_TOLERANCE INTEGER

The projection K-safe value. This value can be greater than the database K-safety value (if more replications of a projection exist than are required to meet the database K-safety). This value cannot be less than the database K-safe setting.

IS_UP_TO_DATE BOOLEAN

Specifies whether the projection is up to date. Projections must be up to date to be used in queries.

HAS_STATISTICS BOOLEAN

Specifies whether there are statistics for any column in the projection:

  • This column returns true only when all non-epoch columns for a table have full statistics. Otherwise, the column returns false. See ANALYZE_STATISTICS().
  • Projections that have no data never have full statistics. Use the PROJECTION_STORAGE system table to determine whether your projection contains data.
IS_SEGMENTED BOOLEAN Specifies whether the projection is segmented.
SEGMENT_EXRESSION VARCHAR

The segmentation expression used for the projection. In the following example for the clicks_agg projection, the values hash(clicks.user_id, (clicks.click_time)::date)indicate that the projection was created with the following expression:

...segmented by hash(clicks.user_id, (clicks.click_time)::date)

SEGMENT_RANGE VARCHAR

The percentage of projection data stored on each node, according to the segmentation expression. For example, segmenting a projection by HASH() on all nodes results in a segment_range value such as the following:

segment_range            | implicit range: v_testcr_node0005[33.3%] v_testcr_node0006[33.3%] v_testcr_node0004[33.3%]

IS_SUPER_PROJECTION BOOLEAN

Specifies whether a projection is a superprojection.

AGGREGATE_TYPE VARCHAR

Specifies the type of live aggregate projection:

  • GROUPBY
  • TOPK
IS_KEY_CONSTRAINT_PROJECTION

BOOLEAN

Indicates whether a projection is a key constraint projection:

  • t: A key constraint projection that validates a key constraint. (Vertica uses the projection to efficiently enforce at least one enabled key constraint.)
  • f: Not a projection that validates a key constraint.
HAS_EXPRESSIONS BOOLEAN

Specifies whether this projection has expressions that define the column values. HAS_EXPRESSIONS is always true for live aggregate projections.

IS_AGGREGATE_PROJECTION BOOLEAN

Specifies whether this projection is a live aggregate projection.