The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to create super projection with out insert operation when i create table??

HyeontaeJuHyeontaeJu Vertica Customer
edited September 2020 in General Discussion

How to create super projection with out insert operation when i create table??

Answers

  • HibikiHibiki Vertica Employee Employee

    I think you mean how to create Auto-Projection without loading the data. You can see the condition for creating it on the following page.
    https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/AdministratorsGuide/Projections/AutoProjections.htm

    But, Auto-Projection is not optimized. We recommend the customers to create the optimized super projection. If the projection has all columns of the anchor table, Vertica recognizes it as a super projection as follows.

    => CREATE TABLE table1 (id1 INT, id2 INT);
    => CREATE PROJECTION table1_super (id1, id2) AS SELECT id1, id2 FROM table1 ORDER BY id1, id2 SEGMENTED BY hash(id1, id2) ALL NODES KSAFE;
    
    => SELECT projection_schema, projection_basename, projection_name, is_super_projection FROM projections WHERE projection_basename = 'table1_super';
     projection_schema | projection_basename | projection_name | is_super_projection
    -------------------+---------------------+-----------------+---------------------
     public            | table1_super        | table1_super_b0 | t
     public            | table1_super        | table1_super_b1 | t
    

Leave a Comment

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