{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Lecture 16 Indexing 3

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

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

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

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

View Full Document
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 >.
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’

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

View Full Document
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.
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

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
Ask a homework question - tutors are online