Chapter12b - Database Management Systems R Ramakrishnan and...

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

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

Unformatted text preview: Database Management Systems, R. Ramakrishnan and Johannes Gehrke Evaluation of Relational Operations: Other Techniques Chapter 12, Part B Database Management Systems, R. Ramakrishnan and Johannes Gehrke Simple Selections ❖ Of the form ❖ Size of result approximated as size of R * reduction factor ; we will consider how to estimate reduction factors later. ❖ With no index, unsorted: Must essentially scan the whole relation; cost is M (#pages in R). ❖ With an index on selection attribute: Use index to find qualifying data entries, then retrieve corresponding data records. (Hash index useful only for equality selections.) SELECT * FROM Reserves R WHERE R.rname < ‘C%’ σ R attr value op R . ( ) Database Management Systems, R. Ramakrishnan and Johannes Gehrke Using an Index for Selections ❖ Cost depends on #qualifying tuples, and clustering. – Cost of finding qualifying data entries (typically small) plus cost of retrieving records (could be large w/o clustering). – In example, assuming uniform distribution of names, about 10% of tuples qualify (100 pages, 10,000 tuples). With a clustered index, cost is little more than 100 I/Os; if unclustered, upto 10,000 I/Os! ❖ Important refinement for unclustered indexes : 1. Find qualifying data entries. 2. Sort the rid’s of the data records to be retrieved. 3. Fetch rids in order. This ensures that each data page is looked at just once (though # of such pages likely to be higher than with clustering). Database Management Systems, R. Ramakrishnan and Johannes Gehrke General Selection Conditions ❖ Such selection conditions are first converted to conjunctive normal form (CNF) : (day<8/9/94 OR bid=5 OR sid=3 ) AND (rname=‘Paul’ OR bid=5 OR sid=3) ❖ We only discuss the case with no OR s (a conjunction of terms of the form...
View Full Document

Page1 / 15

Chapter12b - Database Management Systems R Ramakrishnan and...

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