
Example:
dbadmin=> SELECT default_value, current_value, description
dbadmin-> FROM configuration_parameters
dbadmin-> WHERE parameter_name = 'MaxAutoSortColumns'; FROM configuration_parameters
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/latest/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/AdministratorsGuide/ConfiguringTheDB/ProjectionParameters.htm
Have fun!