Notes06 - Query Processing CS 245: Database System...

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

View Full Document Right Arrow Icon
CS 245 Notes 6 1 CS 245: Database System Principles Notes 6: Query Processing Steven Whang CS 245 Notes 6 2 Query Processing Q Query Plan Focus: Relational System • Others? CS 245 Notes 6 3 Example Select B,D From R,S Where R.A = “c” S.E = 2 R.C=S.C CS 245 Notes 6 4 R A B C S C D E a 1 10 10 x 2 b 1 20 20 y 2 c 2 10 30 z 2 d 2 35 40 x 1 e 3 45 50 y 3 Answer B D 2 x CS 245 Notes 6 5 • How do we execute query? - Do Cartesian product - Select tuples - Do projection One idea CS 245 Notes 6 6 RXS R.A R.B R.C S.C S.D S.E a 1 10 10 x 2 a 1 10 20 y 2 . . C 2 10 10 x 2 . . Bingo! Got one. ..
Background image of page 1

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

View Full DocumentRight Arrow Icon
CS 245 Notes 6 7 Relational Algebra - can be used to describe plans. .. Ex: Plan I Π B,D σ R.A=“c” S.E=2 R.C=S.C X R S OR: Π B,D [ σ R.A=“c” S.E=2 R.C = S.C (RXS)] CS 245 Notes 6 8 Another idea: Π B,D σ R.A = “c” σ S.E = 2 R S Plan II natural join CS 245 Notes 6 9 R S A B C σ (R) σ (S) C D E a 1 10 A B C C D E 10 x 2 b 1 20 c 2 10 10 x 2 20 y 2 c 2 10 20 y 2 30 z 2 d 2 35 30 z 2 40 x 1 e 3 45 50 y 3 CS 245 Notes 6 10 Plan III Use R.A and S.C Indexes (1) Use R.A index to select R tuples with R.A = “c” (2) For each R.C value found, use S.C index to find matching tuples (3) Eliminate S tuples S.E 2 (4) Join matching R,S tuples, project B,D attributes and place in result CS 245 Notes 6 11 R S A B C C D E a 1 10 10 x 2 b 1 20 20 y 2 c 2 10 30 z 2 d 2 35 40 x 1 e 3 45 50 y 3 A C I1 I2 =“c” <c,2,10> <10,x,2> check=2? output: <2,x> next tuple: <c,7,15> CS 245 Notes 6 12 Overview of Query Optimization
Background image of page 2
Notes 6 13 parse convert apply laws estimate result sizes consider physical plans estimate costs pick best execute {P1,P2,…. .} {(P1,C1),(P2,C2). ..} Pi answer SQL query parse tree logical query plan “improved” l.q.p l.q.p. +sizes statistics CS 245 Notes 6 14 Example: SQL query SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE ‘%1960’ ); (Find the movies with stars born in 1960) CS 245 Notes 6 15 Example: Parse Tree <Query> <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Tuple> IN <Query> title StarsIn <Attribute> ( <Query> ) starName <SFW> SELECT <SelList> FROM <FromList> WHERE <Condition> <Attribute> <RelName> <Attribute> LIKE <Pattern> name MovieStar birthDate ‘%1960’ CS 245 Notes 6 16 Example: Generating Relational Algebra Π title σ StarsIn <condition> <tuple> IN Π name <attribute> σ birthdate LIKE ‘%1960’ starName MovieStar Fig. 7.15: An expression using a two-argument σ , midway between a parse tree and relational algebra CS 245 Notes 6 17 Example: Logical Query Plan Π title σ starName=name StarsIn Π name σ birthdate LIKE ‘%1960’ MovieStar Fig. 7.18: Applying the rule for IN conditions × CS 245 Notes 6 18 Example: Improved Logical Query Plan Π title starName=name StarsIn Π name σ birthdate LIKE ‘%1960’ MovieStar Fig. 7.20: An improvement on fig. 7.18. Question:
Background image of page 3

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

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

Page1 / 14

Notes06 - Query Processing CS 245: Database System...

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

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