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:
|
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:
Rebalancing does not change the |
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 hash(clicks.user_id, (clicks.click_time)::date) indicate that the projection was created with the following expression:
|
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 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:
|
HAS_EXPRESSIONS | BOOLEAN |
Specifies whether this projection has expressions that define the column values. |
IS_AGGREGATE_PROJECTION | BOOLEAN |
Specifies whether this projection is a live aggregate projection. |
AGGREGATE_TYPE | VARCHAR |
Specifies the type of live aggregate projection:
|
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 => 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, => 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 |