How to alter the Projection.

[Jess.mic]

I have added some column in the table, so can I alter the projection to include those column??

Please let me know how can I alter the projection.

[nnani]

Hello Jess,

Welcome to Vertica Forms.

You cannot alter existing projections using any 'ALTER' keyword
All you can do is just alter the already existing projection's name using the 'ALTER' command

If you have added the new columns to your table and you want to make the changes accordingly into projection,
there are two options
1. Either create new projection with the all the columns existing in your table currently, you can do it manually using the CREATE PROJECTION statement
2. You runt the DBD and it wil do it for you.

The new projection will be having all your existing columns in the refered table.

[Julie]

Hello,

When I add a column to a table it is automatically added to the projections:

Code: Select all

dbadmin=> create table test (c1 int, c2 int, primary key (c1)) segmented by c1 all nodes;
CREATE TABLE
dbadmin=> select export_objects('', 'test');
                                                                                                                                                                    export_objects                                                                                                                                                                     
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

CREATE TABLE public.test
(
    c1 int NOT NULL,
    c2 int
);

ALTER TABLE public.test ADD CONSTRAINT C_PRIMARY PRIMARY KEY (c1); 


CREATE PROJECTION public.test /*+createtype(P)*/ 
(
 c1,
 c2
)
AS
 SELECT test.c1,
        test.c2
 FROM public.test
 ORDER BY test.c1
SEGMENTED BY test.c1 ALL NODES KSAFE 1;


SELECT MARK_DESIGN_KSAFE(1);

(1 row)

dbadmin=> alter table test add column c3 int;
ALTER TABLE
dbadmin=> select export_objects('', 'test');
                                                                                                                                                                                     export_objects                                                                                                                                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 

CREATE TABLE public.test
(
    c1 int NOT NULL,
    c2 int,
    c3 int
);

ALTER TABLE public.test ADD CONSTRAINT C_PRIMARY PRIMARY KEY (c1); 


CREATE PROJECTION public.test /*+createtype(P)*/ 
(
 c1,
 c2,
 c3
)
AS
 SELECT test.c1,
        test.c2,
        test.c3
 FROM public.test
 ORDER BY test.c1
SEGMENTED BY test.c1 ALL NODES KSAFE 1;


SELECT MARK_DESIGN_KSAFE(1);

(1 row)

[nnani]

Hello Julie,

I have gone through your sql, but unable to get it.

It seems you are manually creating projections after altering the table public.test

But isn't it Overwriting the previous projection and not altering it.

Please correct me, if I am wrong.

[Julie]

Hi nnani,

I am using Vertica 6.1.2. Maybe this is a new feature? I notice that only super projections are altered in this way. If I create my own projection, then add a column to a table, that column is not automatically added to the projection.

Here is an example:

Code: Select all

dbadmin=> create table test (c1 int, c2 int) segmented by c1 all nodes;
CREATE TABLE

dbadmin=> create projection test_julie as select c1 from test segmented by c1 all nodes;
CREATE PROJECTION

dbadmin=> select projection_name, is_super_projection from projections;
 projection_name | is_super_projection
-----------------+---------------------
 test_b0         | t
 test_b1         | t
 test_julie      | f
(3 rows)

dbadmin=> select projection_name, projection_column_name
dbadmin-> from projection_columns
dbadmin-> order by projection_name, sort_position;
 projection_name | projection_column_name
-----------------+------------------------
 test_b0         | c1
 test_b0         | c2
 test_b1         | c1
 test_b1         | c2
 test_julie      | c1
(5 rows)

Now I will add a column to the table TEST. It will automatically get added to the super projections, but not the projection I manually created...

Code: Select all

dbadmin=> alter table test add column c3 varchar(100);
ALTER TABLE

dbadmin=> select projection_name, projection_column_name
dbadmin-> from projection_columns
dbadmin-> order by projection_name, sort_position;
 projection_name | projection_column_name
-----------------+------------------------
 test_b0         | c3
 test_b0         | c1
 test_b0         | c2
 test_b1         | c3
 test_b1         | c1
 test_b1         | c2
 test_julie      | c1
(7 rows)

What I don't like is the column c3 was place first in the sort order?!? I wonder why?

[nnani]

Thanks Julie,


I am working with Vertica 6.1.1, I tried to peep into the Release notes but nothing is mentioned in there
You can check the release notes for 6.1.2, might be you can get this update

Anyways, The new column added into the super projection automatically first in the sort order might be due to the timestamp the the columns were created in the projection. The new the timestamp the first may be the order.

Its just a guess

[scutter]

Julie - check the actual sort_position values. I suspect that the sort_position of the newly added column is actually NULL and gets printed first for that reason.

--Sharon