442_RelOpEval - Operators Selection) Projection) Join)...

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

View Full Document Right Arrow Icon
Evaluation of Relational  Operators
Image of page 1

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

View Full Document Right Arrow Icon
Relational Operations We will consider how to implement: Selection   (     )    Selects a subset of rows from relation. Projection   (     )   Deletes unwanted columns from relation. Join   (        )  Allows us to combine two relations. Set-difference   (     )  Tuples in reln. 1, but not in reln. 2. Union   (     )  Tuples in reln. 1 and in reln. 2. Aggregation   ( SUM, MIN , etc.) and  GROUP BY Since each op returns a relation, ops can be  composed !  After  we cover the operations, we will discuss how to  optimize  queries formed by composing them. σ π - 
Image of page 2
Schema for Examples Similar to old schema;  rname  added for variations. Reserves: Each tuple is 40 bytes long,  100 tuples per page, 1000  pages. Sailors: Each tuple is 50 bytes long,  80 tuples per page, 500 pages.  Sailors ( sid : integer sname : string,  rating : integer,  age : real) Reserves ( sid : integer,  bid : integer,  day : dates rname : string)
Image of page 3

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

View Full Document Right Arrow Icon
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 . ( )
Image of page 4
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, 10000 tuples).  With a clustered  index, cost is little more than 100 I/Os; if unclustered, up to  10000 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). 
Image of page 5

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

View Full Document Right Arrow Icon
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  attr  op  value ).
Image of page 6
Image of page 7
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