CODEWITHSUNDEEP
projectionvertica
user1979831
user1979831

Reputation: 67

Impact of add column on superprojection in vertica DB

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?

Upvotes: 2

Views: 416

Answers (1)

Kermit
Kermit

Reputation: 34055

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;

Upvotes: 5

Related Questions