442_RelOpEval

442_RelOpEval - EvaluationofRelational Operators...

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

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

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

View Full DocumentRight 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. σ π - 
Background 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)
Background image of page 3

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

View Full DocumentRight 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 . ( )
Background 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). 
Background image of page 5

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

View Full DocumentRight 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 ). An index 
Background image of page 6
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 02/24/2011 for the course CS 442 taught by Professor Mlittman during the Fall '08 term at Rutgers.

Page1 / 28

442_RelOpEval - EvaluationofRelational Operators...

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

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