Parent child table segmentation

Best Answer

• Options

  marcothesane - Select Field - Administrator

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

  0