Auto-projections are superprojections that Vertica automatically generates for tables, both temporary and persistent. The following rules apply to all auto-projections:
- Vertica creates the auto-projection in the same schema as the table.
- Auto-projections conform to encoding, sort order, segmentation, and K-safety as specified in the table's creation statement.
- If the table creation statement contains an
AS SELECTclause, Vertica uses some properties of the projection definition's underlying query.
The conditions for creating auto-projections differ, depending on whether the table is temporary or persistent:
|Table type||Auto-projection trigger|
CREATE TABLE or
CREATE TEMPORARY TABLE omits a segmentation (
SEGMENTED BY or
ORDER BY clause, Vertica segments and sorts auto-projections as follows:
- If the table creation statement omits a segmentation (
UNSEGMENTED) clause, Vertica checks configuration parameter
SegmentAutoProjectionto determine whether to create an auto projection that is segmented or unsegmented. By default, this parameter is set to 1 (enable).
SegmentAutoProjectionis enabled and a table's creation statement also omits an
ORDER BYclause, Vertica segments and sorts the table's auto-projection according to the table's manner of creation:
CREATE [TEMPORARY] TABLEcontains an
AS SELECTclause and the query output is segmented, the auto-projection uses the same segmentation. If the result set is already sorted, the projection uses the same sort order.
- In all other cases, Vertica evaluates table column constraints to determine how to sort and segment the projection, as shown below:
Constraints Sorted by: Segmented by: Primary key Primary key Primary key Primary and foreign keys
1. Foreign keys
2. Primary key
Primary key Foreign keys only
Vertica orders segmentation according to data type size, as follows:
1. Small (< 8 byte) data type columns
2. Large (>8 byte) data type columns
None All columns excluding LONG data types, in the order specified by