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.
PARTITION_RANGE_MIN
VARCHAR

Populated only if a projection specifies a partition range, the lowest and highest partition keys of the range. For example, following projection defines a range of orders that were placed since the first of the year:

=> CREATE PROJECTION ytd_orders AS SELECT * FROM store_orders ORDER BY order_date 
    ON PARTITION RANGE BETWEEN date_trunc('year',now())::date AND NULL;

Given that range, columns PARTITION_RANGE_MIN and PARTITION_RANGE_MAX contain the following values:

=> SELECT projection_name partition_range_min, partition_range_min, partition_range_max FROM projections WHERE projection_name ILIKE 'ytd_orders%';
 partition_range_min | partition_range_min | partition_range_max
---------------------+---------------------+---------------------
 ytd_orders_b1       | 2021-01-01          | infinity
 ytd_orders_b0       | 2021-01-01          | infinity
(2 rows)
PARTITION_RANGE_MAX
PARTITION_RANGE_MIN_EXPRESSION VARCHAR Populated only if a projection specifies partition range, the minimum and maximum range expressions as defined in the projection DDL.

For example, following projection defines a range of orders that were placed since the third quarter of last year:

=> CREATE PROJECTION q3_td AS SELECT * FROM store_orders ORDER BY order_date 
    ON PARTITION RANGE BETWEEN add_months(date_trunc('year',now()), -3)::date AND NULL;

Given that definition, PARTITION_RANGE_MIN_EXPRESSION and PARTITION_RANGE_MAX_EXPRESSION are set as follows:

=> SELECT projection_name, partition_range_min_expression, partition_range_max_expression FROM projections WHERE projection_name ILIKE 'Q3_td%';
 projection_name |       partition_range_min_expression        | partition_range_max_expression
-----------------+---------------------------------------------+--------------------------------
 q3_td_b1        | add_months(date_trunc('year', now()), (-3)) | NULL
 q3_td_b0        | add_months(date_trunc('year', now()), (-3)) | NULL
PARTITION_RANGE_MAX_EXPRESSION

See Also

PROJECTION_COLUMNS