Vertica Analytics Platform Version 9.2.x Documentation

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

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 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 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:

  • 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 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_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.

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