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, identical to ANCHOR_TABLE_NAME .
  • For a manually-created projection, the name specified in the CREATE PROJECTION statement.
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

The unique numeric identification (OID) of the projection's anchor table.

ANCHOR_TABLE_NAME VARCHAR

The name of the projection's anchor table.

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

Deprecated, always set to f (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 CREATE PROJECTION.
  • CREATE TABLE: A superprojection that was automatically created when its associated table was created using CREATE TABLE.
  • ALTER TABLE: The system automatically created the key projection in response to a non-empty table.
  • CREATE TABLE WITH PROJ CLAUSE: A superprojection that was automatically created using CREATE TABLE.
  • DELAYED_CREATION: A superprojection that was automatically created when data was loaded for the first time into a new table.
  • DESIGNER: A projection created by 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 projection data is up to date. Only up-to-date projections are available to participate in query execution.

HAS_STATISTICS BOOLEAN

Specifies whether there are statistics for any column in the projection. HAS_STATISTICS returns true only when all non-epoch columns for a table or table partition have full statistics. For details, see Collecting Table Statistics and Collecting Partition Statistics.

Projections that have no data never have full statistics. Query system table PROJECTION_STORAGE 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 following 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 the HASH function on all nodes results in a SEGMENT_RANGE value such as the following:

implicit range: v_vmart_node0002[33.3%] v_vmart_node0003[33.3%] v_vmart_node0001[33.3%]
IS_SUPER_PROJECTION BOOLEAN

Specifies whether a projection is a superprojection.

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.

AGGREGATE_TYPE VARCHAR

Specifies the type of live aggregate projection:

  • GROUPBY
  • TOPK
IS_SHARED BOOLEAN Indicates whether the projection is located on shared storage.

See Also

PROJECTION_COLUMNS