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

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

View Full Document Right Arrow Icon
1 CS 245 Notes 6 1 CS 245: Database System Principles Notes 6: Query Processing Hector Garcia-Molina CS 245 Notes 6 2 Query Processing Q Query Plan CS 245 Notes 6 3 Query Processing Q Query Plan Focus: Relational System •O the rs? CS 245 Notes 6 4 Example Select B,D From R,S Where R.A = “c” S.E = 2 R.C=S.C CS 245 Notes 6 5 R ABC S CDE a 1 10 10 x 2 b12 0 2 0 y2 c 2 10 30 z 2 d23 5 4 0 x1 e 3 45 50 y 3 CS 245 Notes 6 6 R S a 1 10 10 x 2 0 2 0 c 2 10 30 z 2 5 4 0 e 3 45 50 y 3 Answer B D 2 x
Background image of page 1

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

View Full DocumentRight Arrow Icon
2 CS 245 Notes 6 7 • How do we execute query? - Do Cartesian product - Select tuples - Do projection One idea CS 245 Notes 6 8 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 . . CS 245 Notes 6 9 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. .. CS 245 Notes 6 10 Relational Algebra - can be used to describe plans. .. Ex: Plan I B,D R.A=“c” S.E=2 R.C=S.C X RS CS 245 Notes 6 11 Relational Algebra - can be used to describe plans. .. Ex: Plan I B,D R.A=“c” S.E=2 R.C=S.C X OR: B,D [ R.A=“c” S.E=2 R.C = S.C (RXS)] CS 245 Notes 6 12 Another idea: B,D R.A = “c” S.E = 2 Plan II natural join
Background image of page 2
3 CS 245 Notes 6 13 RS 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 14 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 CS 245 Notes 6 15 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 16 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 AC I 1 I 2 CS 245 Notes 6 17 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 I 1 I 2 =“c” <c,2,10> CS 245 Notes 6 18 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 I 1 I 2 =“c” <c,2,10> <10,x,2>
Background image of page 3

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

View Full DocumentRight Arrow Icon
4 CS 245 Notes 6 19 RS 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 AC I 1 I 2 =“c” <c,2,10> <10,x,2> check=2? output: <2,x> CS 245 Notes 6 20 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 I 1 I 2 =“c” <c,2,10> <10,x,2> check=2? output: <2,x> next tuple: <c,7,15> CS 245 Notes 6 21 Overview of Query Optimization CS 245 Notes 6 22 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 23 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 24 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
Background image of page 4
Image of page 5
This is the end of the preview. Sign up to access the rest of the document.

Page1 / 19

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

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

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