Clustering and Joins

• Clustering is especially important when accessing inner tuples in INL.

  • Should make index on E.dno clustered.

• Suppose that the WHERE clause is instead:

  WHERE E.hobby=‘Stamps’ AND E.dno=D.dno
  
  • If many employees collect stamps, Sort-Merge join may be worth considering. A clustered index on D.dno would help.

• Bottom line: Clustering is useful whenever many tuples are to be retrieved.

SELECT E.ename, D.mgr

FROM Emp E, Dept D

WHERE D.dname=‘Toy’ AND E.dno=D.dno

Previous slide

Next slide

Back to first slide

View graphic version