Parent child table segmentation

sknair

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

marcothesane

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.

sknair

Thank You.