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?
Can we alter the existing super projection to add new field to the segment?
Moderator: NorbertKrupa
-
- Newbie
- Posts: 1
- Joined: Wed Aug 31, 2016 12:28 pm
- JimKnicely
- 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?
Hi,
You can create a new super projection which has the new hash segmentation, then drop the original projection...
Example:
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
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.