Additional projection for primary key constraint

Best Answer

• Options

  marcothesane - Select Field - Administrator

  June 2020 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...

  4