Auto-Projections
Auto-projections are superprojections that Vertica automatically generates for tables, both temporary and persistent. In general, Vertica automatically creates projections when you load data for the first time into a new table, and no projections have yet been defined for that table. 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 | CREATE TABLE statement contains one of these clauses:
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 |
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 as follows:
- If the table creation statement omits a segmentation (
SEGMENTED BY
orUNSEGMENTED
) clause, Vertica checks configuration parameterSegmentAutoProjection
to determine whether to create an auto projection that is segmented or unsegmented. By default, this parameter is set to 1 (enable). - If
SegmentAutoProjection
is enabled and a table's creation statement also omits anORDER BY
clause, Vertica segments and sorts the table's auto-projection according to the table's manner of creation:- If
CREATE [TEMPORARY] TABLE
contains anAS 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 1. Foreign keys
2. Primary keyPrimary key Foreign keys only 1. Foreign keys
2. Remaining columns excluding LONG data types, up to the limit set in configuration parameterMaxAutoSortColumns
(by default 8).All columns excluding LONG data types, up to the limit set in configuration parameter
MaxAutoSegColumns
(by default 8).Vertica orders segmentation as follows:
- Small (≤ 8 byte) data type columns: Columns are specified in the same order as they are defined in the table CREATE statement.
- Large (>8 byte) data type columns: Columns are ordered by ascending size.
None All columns excluding LONG data types, in the order specified by CREATE TABLE
. - If
For example, the following table is defined with no primary or foreign keys:
=> CREATE TABLE testAutoProj(c10 char (10), v1 varchar(140) DEFAULT v2||v3, i int, c5 char(5), v3 varchar (80), d timestamp, v2 varchar(60), c1 char(1)); CREATE TABLE => INSERT INTO testAutoProj VALUES ('1234567890', DEFAULT, 1, 'abcde', 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor ', current_timestamp, 'incididunt ut labore et dolore magna aliqua. Eu scelerisque', 'a'); OUTPUT -------- 1 (1 row) => COMMIT; COMMIT
Before the INSERT statement loads data into this table for the first time, Vertica automatically creates a superprojection for the table:
=> SELECT export_objects('', 'testAutoProj_b0'); -------------------------------------------------------- CREATE PROJECTION public.testAutoProj_b0 /*+basename(testAutoProj),createtype(L)*/ ( c10, v1, i, c5, v3, d, v2, c1 ) AS SELECT testAutoProj.c10, testAutoProj.v1, testAutoProj.i, testAutoProj.c5, testAutoProj.v3, testAutoProj.d, testAutoProj.v2, testAutoProj.c1 FROM public.testAutoProj ORDER BY testAutoProj.c10, testAutoProj.v1, testAutoProj.i, testAutoProj.c5, testAutoProj.v3, testAutoProj.d, testAutoProj.v2, testAutoProj.c1 SEGMENTED BY hash(testAutoProj.i, testAutoProj.c5, testAutoProj.d, testAutoProj.c1, testAutoProj.c10, testAutoProj.v2, testAutoProj.v3, testAutoProj.v1) ALL NODES OFFSET 0; SELECT MARK_DESIGN_KSAFE(1); (1 row)