Can we alter the existing super projection to add new field to the segment?

Moderator: NorbertKrupa

Post Reply
Verticauser-ka
Newbie
Newbie
Posts: 1
Joined: Wed Aug 31, 2016 12:28 pm

Can we alter the existing super projection to add new field to the segment?

Post by Verticauser-ka » Wed Aug 31, 2016 12:48 pm

My problem statement is to remove a column which is involved in the segmentation of the super projection, alter to add another field for segmentation.

Different ways i tried with are:
Drop the super projection, create a new projection. - not able to delete the super projection.
Create another super projection, make original super projection as buddy projection. - only option is to rename the super projection
Add another field to the segment of existing super projection. - trying to achieve this.

Is there any other way through which this can be attained?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Can we alter the existing super projection to add new field to the segment?

Post by JimKnicely » Thu Sep 22, 2016 8:03 pm

Hi,

You can create a new super projection which has the new hash segmentation, then drop the original projection...

Example:

Code: Select all

dbadmin=> create table test (col1 int, col2 varchar(100), col3 varchar(100)) order by col1, col2 segmented by hash (col1, col2) all nodes;
CREATE TABLE

dbadmin=> insert into test values (1, '1', '2');
 OUTPUT
--------
      1
(1 row)

dbadmin=> commit;
COMMIT

dbadmin=> select projection_name, is_super_projection, segment_expression from projections where anchor_table_name = 'test';
 projection_name | is_super_projection |     segment_expression
-----------------+---------------------+----------------------------
 test_super      | t                   | hash(test.col1, test.col2)
(1 row)

dbadmin=> create projection test_super2 as select * from test order by col1, col2 segmented by hash (col1, col3) all nodes;
WARNING 4468:  Projection <public.test_super2> is not available for query processing. Execute the select start_refresh() function to copy data into this projection.
          The projection must have a sufficient number of buddy projections and all nodes must be up before starting a refresh
CREATE PROJECTION

dbadmin=> select start_refresh();
             start_refresh
----------------------------------------
 Starting refresh background process.

(1 row)

dbadmin=> select make_ahm_now();
        make_ahm_now
-----------------------------
 AHM set (New AHM Epoch: 76)
(1 row)

dbadmin=> select projection_name, is_super_projection, segment_expression from projections where anchor_table_name = 'test';
 projection_name | is_super_projection |     segment_expression
-----------------+---------------------+----------------------------
 test_super      | t                   | hash(test.col1, test.col2)
 test_super2     | t                   | hash(test.col1, test.col3)
(2 rows)

dbadmin=> drop projection test_super cascade;
DROP PROJECTION

dbadmin=> select projection_name, is_super_projection, segment_expression from projections where anchor_table_name = 'test';
 projection_name | is_super_projection |     segment_expression
-----------------+---------------------+----------------------------
 test_super2     | t                   | hash(test.col1, test.col3)
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “General”