Auto-Projections

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 SELECT clause, Vertica uses some properties of the projection definition's underlying query.

Auto-Projection Triggers

The conditions for creating auto-projections differ, depending on whether the table is temporary or persistent:

Table type Auto-projection trigger

Temporary CREATE TEMPORARY TABLE statement unless it includes NO PROJECTION.

Persistent

Table type	Auto-projection trigger
Temporary	`CREATE TEMPORARY TABLE` statement unless it includes `NO PROJECTION`.
Persistent	`CREATE TABLE` statement contains one of these clauses: `AS SELECT` `ENCODED BY` `ORDER BY` `SEGMENTED BY` / `UNSEGMENTED` `KSAFE` If none of these conditions is true, Vertica automatically creates a superprojection (if one does not already exist) only when you first load data into the table with `INSERT` or `COPY`.

CREATE TABLE statement contains one of these clauses:

AS SELECT
ENCODED BY
ORDER BY
SEGMENTED BY / UNSEGMENTED
KSAFE

If none of these conditions is true, Vertica automatically creates a superprojection (if one does not already exist) only when you first load data into the table with INSERT or COPY.

Default Segmentation and Sort Order

If CREATE TABLE or CREATE TEMPORARY TABLE omits a segmentation (SEGMENTED BY or UNSEGMENTED) or ORDER BY clause, Vertica segments and sorts auto-projections according to the table's manner of creation:

If CREATE [TEMPORARY] TABLE contains an AS SELECT clause, 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	Foreign keys Primary key	Primary key
Foreign keys only	Foreign keys Remaining columns excluding LONG data types, in the order specified by `CREATE TABLE`.	All columns excluding LONG data types, up to the limit set in configuration parameter `MaxAutoSegColumns` (by default 32). Vertica orders segmentation according to data type size, as follows: Small (< 8 byte) data type columns Large (>8 byte) data type columns
None	All columns excluding LONG data types, in the order specified by `CREATE TABLE`.