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

Parent child table segmentation

Parent table has been segmented in 3 nodes.How to ensure child table rows with same parent key are also in the same node?

Best Answer

  • marcothesanemarcothesane Employee
    Accepted Answer

    Do you mean this?

    CREATE TABLE par (
      par_id INTEGER NOT NULL
    , par_name VARCHAR(32)
    , CONSTRAINT pk_par PRIMARY KEY(par_id)
    )
    SEGMENTED BY HASH(par_id) ALL NODES;
    
    CREATE TABLE chi (
      chi_id INTEGER NOT NULL
    , par_id INTEGER NOT NULL
    , chi_name VARCHAR(32)
    , CONSTRAINT pk_chi PRIMARY KEY(chi_id)
    , CONSTRAINT fk_chi_par  FOREIGN KEY (
       par_id
      ) REFERENCES par(par_id)
    )
    SEGMENTED BY HASH(par_id) ALL NODES;
    
    

    If both are segmented by the hash of par_id, then, if a row of par with par_id of 42 is on node 3, then also a row in chi whose par_id is 42 till be on node 3.
    There is a technical term for that, that we call co-segmenting. This ensures that in case of a join no rows need to travel from one node to another for that.

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.