√2011-09-26-OperatorImplementation1

√2011-09-26-OperatorImplementation1 -...

Info icon This preview shows pages 1–6. Sign up to view the full content.

View Full Document Right Arrow Icon
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
Image of page 1

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

View Full Document Right Arrow Icon
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
Image of page 2
Case 2: Example SELECT * FROM Sailor S Database Management Systems 3ed, R. Ramakrishnan and Johannes Gehrke 7 Have B+-tree index on (Age, Salary) 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
Image of page 3

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

View Full Document Right Arrow Icon
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)
Image of page 4
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 Worst-case cost = D * S * N / T (if clustered index) Same as hash index Putting it all together Total cost of select operations using
Image of page 5

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

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

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern