Lecture 16 Indexing 3

Lecture 16 Indexing 3 - Examples of Clustered Indexes...

Info iconThis preview shows pages 1–5. Sign up to view the full content.

View Full Document Right Arrow Icon
Examples of Clustered Indexes v B+ tree index on E.age can be used to get qualifying tuples. § How selective is the condition? § Is the index clustered? SELECT E.dno FROM Emp E WHERE E.age>40 Clustered Unclustered Selective (5% retrieved) good not worth it Not selective (95% retrieved) useful, small improvement definitely not worth it
Background image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
37 Examples of Clustered Indexes § If many tuples have E.age > 10, using E.age index and sorting the retrieved tuples by E.dno may be costly. § Clustered E.dno index may be better! SELECT E.dno, COUNT (*) FROM Emp E WHERE E.age>10 GROUP BY E.dno Compare b+tree index on < age >, b+tree index on < dno >.
Background image of page 2
38 Examples of Clustered Indexes v Equality queries and duplicates: § Clustering on E.hobby helps! SELECT E.dno FROM Emp E WHERE E.hobby=’Stamps’
Background image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
39 Indexes with Composite Search Keys v Composite Search Keys : Search on a combination of fields. § Equality query : Every field value is equal to a constant value. E.g. wrt <sal,age> index: • age=20 and sal =75 § Range query: Some field value is not a constant. E.g.: • age =20; or age=20 and sal > 10 v Data entries in index sorted by search key to support range queries. § Lexicographic order
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 03/27/2012 for the course CLASSICS 122 taught by Professor Smith during the Spring '12 term at UMass (Amherst).

Page1 / 13

Lecture 16 Indexing 3 - Examples of Clustered Indexes...

This preview shows document pages 1 - 5. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online