PROJECTIONS

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.

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.

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.

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)

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%]

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.

Specifies the type of live aggregate projection:

• GROUPBY

• TOPK

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)

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