Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Create custom superprojection does not take into account column encodings from CREATE TABLE

CREATE TABLE a ( 
data INTEGER NOT NULL 
); 
CREATE PROJECTION a_custom_super AS SELECT * from a; 
INSERT INTO a VALUES (123); 

SELECT EXPORT_OBJECTS('','a'); 

outputs 

CREATE TABLE mab_test.a 

data int NOT NULL 
); 



CREATE PROJECTION mab_test.a_custom_super 

data 

AS 
SELECT a.data 
FROM mab_test.a 
ORDER BY a.data 
UNSEGMENTED NODE v_verticatest_node0001; 


SELECT MARK_DESIGN_KSAFE(0); 


But when we specify encodings for columns in the original CREATE TABLE statement, a default super projection 'mab_test.a_super' is created instead of specifying this encoding for my custom superprojection. 

DROP TABLE a cascade; 
CREATE TABLE a ( 
data INTEGER NOT NULL ENCODING RLE 
); 
CREATE PROJECTION a_custom_super AS SELECT * from a; 
INSERT INTO a VALUES (123); 

SELECT EXPORT_OBJECTS('','a'); 

outputs 

CREATE TABLE mab_test.a 

data int NOT NULL 
); 



CREATE PROJECTION mab_test.a_super /*+basename(a),createtype(P)*/ 

data ENCODING RLE 

AS 
SELECT a.data 
FROM mab_test.a 
ORDER BY a.data 
SEGMENTED BY hash(a.data) ALL NODES ; 

CREATE PROJECTION mab_test.a_custom_super 

data 

AS 
SELECT a.data 
FROM mab_test.a 
ORDER BY a.data 
UNSEGMENTED NODE v_verticatest_node0001; 


SELECT MARK_DESIGN_KSAFE(0);

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.