Unsegmented projections

In many cases, dimension tables are relatively small, so you do not need to segment them. Accordingly, you should design a K-safe database so projections for its dimension tables are replicated without segmentation on all cluster nodes. You create unsegmented projections with a CREATE PROJECTION statement that includes the clause UNSEGMENTED ALL NODES. This clause specifies to create identical instances of the projection on all cluster nodes.

The following example shows how to create an unsegmented projection for the table store.store_dimension:

=> CREATE PROJECTION store.store_dimension_proj (storekey, name, city, state)
             AS SELECT store_key, store_name, store_city, store_state
             FROM store.store_dimension
             UNSEGMENTED ALL NODES;
CREATE PROJECTION

Vertica uses the same name to identify all instances of the unsegmented projection—in this example, store.store_dimension_proj. The keyword ALL NODES specifies to replicate the projection on all nodes:

=> \dj store.store_dimension_proj
                         List of projections
 Schema |         Name         |  Owner  |       Node       | Comment
--------+----------------------+---------+------------------+---------
 store  | store_dimension_proj | dbadmin | v_vmart_node0001 |
 store  | store_dimension_proj | dbadmin | v_vmart_node0002 |
 store  | store_dimension_proj | dbadmin | v_vmart_node0003 |
(3 rows)

For more information about projection name conventions, see Projection naming.

Automatic unsegmentation for non-superprojection tables loaded with small files

OpenText™ Analytics Database can automatically create unsegmented projections for tables that are created without a superprojection when they are first populated using a COPY operation with small source files. This behavior improves performance for small tables by eliminating the overhead of distributed processing.

This capability is intended for environments that contain many small tables or staging tables that are frequently reloaded.

The behavior is controlled by the configuration parameter MaxSourceSizeForUnsegAutoProj. When a file loaded by COPY is smaller than or equal to the value specified for this parameter, the database creates the projections for the new table as unsegmented. If the file size exceeds the configured value, or if the parameter is set to 0, the database applies its default segmentation behavior.

For configuration details of MaxSourceSizeForUnsegAutoProj, see Projection parameters.