GET_TABLE_PROJECTIONS

GET_TABLE_PROJECTIONS returns information relevant to the status of a table:

• the current K-Safety status of the database

  K represents the maximum number of nodes in a database that can fail and recover with no loss of data. In Vertica V2.1, the value of K can be zero (0) or one (1). The value of K can be one (1) only when the Physical Schema design meets certain requirements. The designs generated by the Database Designer are K-Safe.

  A physical schema consists of a set of projections used to store data on disk. The projections in the physical schema are based on the objects in the Logical Schema.

• the number of nodes (sites) in the database

  A node is a host configured to run an instance of Vertica. It is a member of a database cluster (see Node Definition). For a database to have the ability to recover from the failure of a node requires at least three nodes. Vertica Systems, Inc. recommends that you use a minimum of four nodes.

  A cluster generally refers a collection of hosts or a collection of nodes bound to a database. A cluster is not part of a database definition and thus does not have a name.

  An instance of Vertica consists of the running Vertica process and disk storage (catalog and data) on a host. There can be only one instance of Vertica running on a host at any time.

• the number of projections for which the specified table is the anchor table

  The anchor table of a join can be:

  • the fact table in a star schema
  • the central fact table in a snowflake schema
  • a dimension table in a snowflake schema that functions as a fact table (with a restriction)
• for each of those projections:
  • the projection's buddy projections

    Buddy projections are required for K-Safety. Two projections are considered to be buddies if they contain the same columns and have the same range segmentation or hash segmentation using different node ordering. Buddy projections usually have different sort orders for query performance purposes.

  • whether or not the projection is segmented

    Segmentation is the horizontal partitioning of a projection so that it can be stored on multiple nodes. The goal is to distribute physical data storage evenly across a database so that all nodes can participate in query execution. See also:

    • Hash Segmentation
    • Range Segmentation
  • whether or not the projection is safe
  • whether or not the projection is up-to-date

    A projection is up-to-date if it does not require a refresh in order to participate in query execution.

Syntax

GET_TABLE_PROJECTIONS('table')

Semantics

Notes

• You can use GET_TABLE_PROJECTIONS to monitor the progress of a projection data refresh (see ALTER PROJECTION).

  A refresh operation ensures that all projections on a node are up-to-date (can participate in query execution). This process may take a long time, depending on how much data is in the table(s).

Examples

=> SELECT GET_TABLE_PROJECTIONS('t1');

get_table_projections

-----------------------------------------------------------------------------------------------

Current system K is 0.

# of Sites: 1.

Table t1 has 2 projections.

Projection Name: [Segmented] [# of Buddies] [Buddy Projections] [Safe] [UptoDate]

----------------------------------------------------------

t1_p1 [Segmented: No] [# of Buddies: 0] [No buddy projections] [Safe: Yes] [UptoDate: Yes]

t1_sp1 [Segmented: No] [# of Buddies: 0] [No buddy projections] [Safe: Yes] [UptoDate: Yes]