Examples of Clustering

• B+ tree index on E.age can be used to get qualifying tuples.

  • How selective is the condition?
  • Is the index clustered?

• Consider the GROUP BY query.

  • If many tuples have E.age > 10, using E.age index and sorting the retrieved tuples may be costly.
  • Clustered E.dno index may be better!

• Equality queries and duplicates

  • Clustering on E.hobby helps!

SELECT E.dno

FROM Emp E

WHERE E.ageᡠ

SELECT E.dno, COUNT (*)

FROM Emp E

WHERE E.ageᡂ

GROUP BY E.dno

SELECT E.dno

FROM Emp E

WHERE E.hobby=‘Stamps’

Previous slide

Next slide

Back to first slide

View graphic version