Auto-Projection Column Sort Order
Jim_Knicely
- Select Field - Administrator
Auto-projections are superprojections that Vertica automatically generates for tables, both temporary and persistent. Vertica uses the current value of the MaxAutoSortColumns configuration parameter to determine which table columns are included in the auto-projection sort order.
Example:
dbadmin=> SELECT default_value, current_value, description
dbadmin-> FROM configuration_parameters
dbadmin-> WHERE parameter_name = 'MaxAutoSortColumns';
default_value | current_value | description
---------------+---------------+-----------------------------------------------------------------------------------------
8 | 8 | Max number of columns used in auto projection sort expression (0 is to use all columns)
(1 row)
dbadmin=> CREATE TABLE test_sort_order (
dbadmin(> c1 INT,
dbadmin(> c2 INT,
dbadmin(> c3 INT,
dbadmin(> c4 INT,
dbadmin(> c5 INT,
dbadmin(> c6 INT,
dbadmin(> c7 INT,
dbadmin(> c8 INT,
dbadmin(> c9 INT,
dbadmin(> c10 INT);
CREATE TABLE
dbadmin=> INSERT INTO test_sort_order (c1) SELECT 1;
OUTPUT
--------
1
(1 row)
dbadmin=> SELECT DISTINCT projection_column_name, sort_position
dbadmin-> FROM projection_columns
dbadmin-> WHERE table_name = 'test_sort_order'
dbadmin-> AND sort_position IS NOT NULL
dbadmin-> ORDER BY sort_position;
projection_column_name | sort_position
------------------------+---------------
c1 | 0
c2 | 1
c3 | 2
c4 | 3
c5 | 4
c6 | 5
c7 | 6
c8 | 7
(8 rows)
dbadmin=> ALTER DATABASE test_db SET MaxAutoSortColumns = 4;
ALTER DATABASE
dbadmin=> DROP TABLE test_sort_order;
DROP TABLE
dbadmin=> CREATE TABLE test_sort_order (
dbadmin(> c1 INT,
dbadmin(> c2 INT,
dbadmin(> c3 INT,
dbadmin(> c4 INT,
dbadmin(> c5 INT,
dbadmin(> c6 INT,
dbadmin(> c7 INT,
dbadmin(> c8 INT,
dbadmin(> c9 INT,
dbadmin(> c10 INT);
CREATE TABLE
dbadmin=> INSERT INTO test_sort_order (c1) SELECT 1;
OUTPUT
--------
1
(1 row)
dbadmin=> SELECT DISTINCT projection_column_name, sort_position
dbadmin-> FROM projection_columns
dbadmin-> WHERE table_name = 'test_sort_order'
dbadmin-> AND sort_position IS NOT NULL
dbadmin-> ORDER BY sort_position;
projection_column_name | sort_position
------------------------+---------------
c1 | 0
c2 | 1
c3 | 2
c4 | 3
(4 rows)
Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/ProjectionParameters.htm
Have fun!
Tagged:
0