Lecture8

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 10, using E.age FROM Emp E index and sor6ng the retrieved tuples may WHERE E.age>10 GROUP BY E.dno be costly. Clustered E.dno index may be beger! •  Equality queries and duplicates: –  Clustering on E.hobby helps! SELECT E.dno FROM Emp E WHERE E.hobby=Stamps Indexes with Composite Search Keys •  Composite Search Keys: Search on a combina6on 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 •  Data entries in index sorted by search key to support range queries. –  –  Lexicographic order, or Spa6al order. Examples of composite key indexes using lexicographic order. 11,80 11 12,10 12 12,20 13,75 name age sal 10,12 20,12 75,13 10 cal 11 80 joe 12 <age, sal> bob 12 20 sue 13 12 13 75 <age> 10 Data records sorted by name 80,11 <sal, age> Data entries in index sorted by <sal,age> 20 75 80 <sal> Data entries sorted by <sal> Composite Search Keys •  To retrieve Emp records with age=30 AND sal=4000, an index on <age,sal> would be beger than an index on age or an index on sal. –  Choice of index key orthogonal to clustering etc. •  If condi6on is: 20<age<30 AND 3000<sal<5000: –  Clustered tree index on <age,sal> or <sal,age> is best. •  If condi6on is: age=30 AND 3000<sal<5000: –  Clustered <age,sal> index much beger than <sal,age> index! •  Composite indexes are larger, updated mo...
View Full Document

This document was uploaded on 03/12/2014 for the course CSCI 165B at UC Davis.

Ask a homework question - tutors are online