Impact of add column on superprojection in vertica DB

Question

I have a conceptual question in vertica DB. If I create a table 'abc' in vertica with columns a,b,c order by a,b, it will automatically create a superprojection for it. Now, If I alter table 'abc' add column 'd' to it, it will create a new superprojection. The question is, will the 'order by a,b' be impacted in this new superprojection? Will vertica retain this order by in the new superprojection? Also, will it also include the column 'd' to this order by? What is the default behaviour?

Answer 1

Will vertica retain this order by in the new superprojection?

It will retain the order by specified in the initial CREATE TABLE statement.

Also, will it also include the column 'd' to this order by?

Vertica will only add new columns to the super projection (this is the default behavior).

Walk through

Let's create the table & add data:

CREATE TABLE public.abc (
    a int,
    b int,
    c int
) ORDER BY a, b;

INSERT INTO public.abc (a, b, c) VALUES (1, 2, 3);

A super-projection is automatically added when data is added to the table:

CREATE PROJECTION public.abc /*+createtype(P)*/
(
 a,
 b,
 c
)
AS
 SELECT abc.a,
        abc.b,
        abc.c
 FROM public.abc
 ORDER BY abc.a,
          abc.b
SEGMENTED BY hash(abc.a, abc.b, abc.c) ALL NODES KSAFE 1;

Let's add a new column to the table:

 ALTER TABLE public.abc ADD COLUMN d int;

The new column gets added only to the projection columns and table columns in any super-projections (not in ORDER BY):

CREATE PROJECTION public.abc /*+createtype(P)*/
(
 a,
 b,
 c,
 d -- Added here
)
AS
 SELECT abc.a,
        abc.b,
        abc.c,
        abc.d -- Added here
 FROM public.abc
 ORDER BY abc.a,
          abc.b
SEGMENTED BY hash(abc.a, abc.b, abc.c) ALL NODES KSAFE 1;