{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Notes06

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

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

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 • Others? 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 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 CS 245 Notes 6 6 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

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

View Full Document
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 R S 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 R S 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 R S Plan II natural join
3 CS 245 Notes 6 13 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 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 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 I 1 I 2 CS 245 Notes 6 17 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 I 1 I 2 =“c” <c,2,10> CS 245 Notes 6 18 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 I 1 I 2 =“c” <c,2,10> <10,x,2>

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

View Full Document
4 CS 245 Notes 6 19 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 I 1 I 2 =“c” <c,2,10> <10,x,2> check=2? output: <2,x> CS 245 Notes 6 20 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 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>
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}