Sort order for newly added columns

[ansumanverma]

I have created a table with order by clause in along with create table. The sort_position column in Projection_columns table is reflected respective to position provided in ORDER by clause. Now if I add new columns in this table , sort_position column is null and as per my knowledge Vertica uses the order in which new columns are specified in the column definition as the projection's sort order.
But I want to specify the sort order for newly added columns so that sort_position in Projection_columns is not null. Is there any way to do this?


Anshuman

[JimKnicely]

Hi Anshuman,

I think that the only way to add columns to the sort order of a projection is to re-create it with the new sort order.

Example:

Code: Select all

dbadmin=> create table test (col1 int, col2 varchar(1)) order by col1, col2;
CREATE TABLE
dbadmin=> select projection_name, projection_column_name, column_position, sort_position from projection_columns where projection_name ilike 'test%';
 projection_name | projection_column_name | column_position | sort_position 
-----------------+------------------------+-----------------+---------------
 test_b0         | col1                   |               0 |             0
 test_b0         | col2                   |               1 |             1
 test_b1         | col1                   |               0 |             0
 test_b1         | col2                   |               1 |             1
(4 rows)

Now I will add a column, and see that the sort position for the new column is null:

Code: Select all

dbadmin=> alter table test add column col3 int;
ALTER TABLE
dbadmin=> select projection_name, projection_column_name, column_position, sort_position from projection_columns where projection_name ilike 'test%';
 projection_name | projection_column_name | column_position | sort_position 
-----------------+------------------------+-----------------+---------------
 test_b0         | col1                   |               0 |             0
 test_b0         | col2                   |               1 |             1
 test_b0         | col3                   |               2 |              
 test_b1         | col1                   |               0 |             0
 test_b1         | col2                   |               1 |             1
 test_b1         | col3                   |               2 |              
(6 rows)

Now I will rename the projections to back them up before I create the new ones...

Code: Select all

dbadmin=> alter projection test_b0 rename to test_b0_bk;
ALTER PROJECTION

dbadmin=> alter projection test_b1 rename to test_b1_bk;
ALTER PROJECTION

We can use the EXPORT_OBJECTS command to see the CREATE PROJECTION statements for the projections used for the TEST table.

Code: Select all

dbadmin=> select export_objects('', 'test');
                                                                                                                                                                                                                                                                                                                      export_objects                                                                                                                                                                                                                                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

CREATE TABLE public.test
(
    col1 int,
    col2 varchar(1),
    col3 int
);



CREATE PROJECTION public.test_b0_bk /*+createtype(P)*/ 
(
 col1,
 col2,
 col3
)
AS
 SELECT test.col1,
        test.col2,
        test.col3
 FROM public.test
 ORDER BY test.col1,
          test.col2
SEGMENTED BY hash(test.col1, test.col2) ALL NODES ;

CREATE PROJECTION public.test_b1_bk /*+createtype(P)*/ 
(
 col1,
 col2,
 col3
)
AS
 SELECT test.col1,
        test.col2,
        test.col3
 FROM public.test
 ORDER BY test.col1,
          test.col2
SEGMENTED BY hash(test.col1, test.col2) ALL NODES OFFSET 1;


SELECT MARK_DESIGN_KSAFE(1);

(1 row)

Now I can edit the CREATE PROJECTION statements and add the new COL3 column to the ORDER BY and HASH clauses and then run them:

Code: Select all

dbadmin=> CREATE PROJECTION public.test_b0 /*+createtype(P)*/ 
dbadmin-> (
dbadmin(>  col1,
dbadmin(>  col2,
dbadmin(>  col3
dbadmin(> )
dbadmin-> AS
dbadmin->  SELECT test.col1,
dbadmin->         test.col2,
dbadmin->         test.col3
dbadmin->  FROM public.test
dbadmin->  ORDER BY test.col1,
dbadmin->           test.col2,
dbadmin->           test.col3
dbadmin-> SEGMENTED BY hash(test.col1, test.col2, test.col3) ALL NODES ;
CREATE PROJECTION
dbadmin=> 
dbadmin=> CREATE PROJECTION public.test_b1 /*+createtype(P)*/ 
dbadmin-> (
dbadmin(>  col1,
dbadmin(>  col2,
dbadmin(>  col3
dbadmin(> )
dbadmin-> AS
dbadmin->  SELECT test.col1,
dbadmin->         test.col2,
dbadmin->         test.col3
dbadmin->  FROM public.test
dbadmin->  ORDER BY test.col1,
dbadmin->           test.col2,
dbadmin->           test.col3
dbadmin-> SEGMENTED BY hash(test.col1, test.col2, test.col3) ALL NODES OFFSET 1;
CREATE PROJECTION

Then I can drop the backup projections:

Code: Select all

dbadmin=> drop projection test_b0_bk, test_b1_bk;
DROP PROJECTION

Finally I can check if my new column COL3 in the test table is part of the sort for the projections:

Code: Select all

dbadmin=> select projection_name, projection_column_name, column_position, sort_position from projection_columns where projection_name ilike 'test%';
 projection_name | projection_column_name | column_position | sort_position 
-----------------+------------------------+-----------------+---------------
 test_b0_super   | col1                   |               0 |             0
 test_b0_super   | col2                   |               1 |             1
 test_b0_super   | col3                   |               2 |             2
 test_b1_super   | col1                   |               0 |             0
 test_b1_super   | col2                   |               1 |             1
 test_b1_super   | col3                   |               2 |             2
(6 rows)