Skip to main content
Question

Parent child table segmentation

  • May 31, 2020
  • 2 replies
  • 6 views

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?

2 replies

marcothesane
Forum|alt.badge.img+1
  • Participating Frequently
  • May 31, 2020

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
  • Author
  • New Participant
  • June 1, 2020

Thank You.