Chapter 12, 14-Operator Implementation1 (9-26)

Chapter 12, 14-Operator Implementation1 (9-26) - Evaluating...

Info iconThis preview shows pages 1–6. 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

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: Evaluating Relational Operations: Part I Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 1 Relational Operators Select Project Join Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 2 Set operations (union, intersect, except) Aggregation Select Operator SELECT * Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 3 FROM Sailor S WHERE S.Age = 25 AND S.Salary > 100K Select Operator Three cases Case 1: No index on any selection attribute Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 4 Case 2: Have matching index on all selection attributes Case 3: Have matching index on some (but not all) selection attributes Case 1: No index on any selection attribute Assume that select operator is applied over a relation with N tuples stored in P data pages What is the cost of select operation in this Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 5 case (in terms of # I/Os)? Select Operator Three cases Case 1: No index on any selection attribute Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 6 Case 2: Have matching index on all selection attributes Case 3: Have matching index on some (but not all) selection attributes Case 2: Example SELECT * FROM Sailor S Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 7 Have B+-tree index on (Age, Salary) FROM Sailor S WHERE S.Age = 25 AND S.Salary > 100K Case 2: Cost Components Index Component 1: Traversing index Cost for B+-trees? Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 8 File For hash indices? Case 2: Cost Components Index Component 2: Traversing sub-set of data entries in index Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 9 File Case 2: Cost Components Index Component 3: Fetching actual data records (alternative 2 or 3) Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 10 File Cost of Component 1 D is cost of reading/writing one page to disk (using random disk I/O) Hash index Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 11 Cost = D B+-tree Cost = D * (height of tree) Cost of Component 2 N data entries (= # data tuples if alternative 2) Hash index Li h hi Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 12 Linear hashing B hash buckets Average cost = D * (N/B 1) B+ tree index L = average number of entries per leaf page S = Selectivity (fraction of tuples satisfying selection) Average cost = D * ((S * N/L) 1) Cost of Component 3 S*N data entries satisfy selection condition S is selectivity, N is total number of data entries T is number of data tuples per page Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 13 Hash index Worst-case cost = D * S * N (if unclustered index) B+ tree index...
View Full Document

Page1 / 17

Chapter 12, 14-Operator Implementation1 (9-26) - Evaluating...

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

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