Additional projection for primary key constraint

If a table has a super projection sorted on primary key, will an additional projection for primary key constraint help with query performance?

Best Answer

  • marcothesanemarcothesane Employee
    Accepted Answer

    I suppose you created the table like I did below here, or in a similar way - that is, specifying the primary key at CREATE time:
    The default projection that comes out of that is sorted by and segmented by the hash of the primary key.

    CREATE TABLE foo (
      foo_id    INT NOT NULL
    ,  foo_name VARCHAR(32)
    , CONSTRAINT pk_foo PRIMARY KEY(foo_id)
    );
    -- Insert a row to force the creation of a projection
    INSERT INTO FOO VALUES(1,'Arthur');COMMIT;
    -- let Vertica show what it created:
    SELECT EXPORT_OBJECTS('','foo',FALSE);
    -- out 
    -- out CREATE TABLE dbadmin.foo
    -- out (
    -- out     foo_id int NOT NULL,
    -- out     foo_name varchar(32),
    -- out     CONSTRAINT pk_foo PRIMARY KEY (foo_id) DISABLED
    -- out );
    -- out 
    -- out 
    -- out CREATE PROJECTION dbadmin.foo_super /*+basename(foo),createtype(L)*/ 
    -- out (
    -- out  foo_id,
    -- out  foo_name
    -- out )
    -- out AS
    -- out  SELECT foo.foo_id,
    -- out         foo.foo_name
    -- out  FROM dbadmin.foo
    -- out  ORDER BY foo.foo_id
    -- out SEGMENTED BY hash(foo.foo_id) ALL NODES OFFSET 0;
    

    So the projection you seem to intend to build would be a complete waste of time and resources...

Answers

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.