1 / 21

CS4432: Database Systems II

CS4432: Database Systems II. Lecture #14 Query Processing Overview. Professor Elke A. Rundensteiner. Query Processing. Query in SQL  Query Plan in Algebra. Example. Data: relation R (A, B, C) relation S (C, D, E) Query: SELECT B, D FROM R, S

cera
Download Presentation

CS4432: Database Systems II

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CS4432: Database Systems II Lecture #14 Query Processing Overview Professor Elke A. Rundensteiner query processing - lecture 14

  2. Query Processing Query in SQL  Query Plan in Algebra query processing - lecture 14

  3. Example Data: relation R (A, B, C) relation S (C, D, E) Query: SELECT B, D FROM R, S WHERE R.A = “c” and S.E = 2 and R.C=S.C query processing - lecture 14

  4. Relational Algebra – default plan Ex: Plan I B,D sR.A=“c” S.E=2  R.C=S.C X R S OR: B,D [sR.A=“c” S.E=2  R.C = S.C (RXS)] query processing - lecture 14

  5. Another idea: Plan II B,D sR.A = “c”sS.E = 2 R S natural join query processing - lecture 14

  6. R S A B C s (R) s(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 SELECT B,D FROM R,S WHERE R.A = “c” and S.E = 2 and R.C=S.C query processing - lecture 14

  7. Yet another idea: Plan III B,D sS.E = 2 sR.A = “c” R S natural join query processing - lecture 14

  8. 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 query processing - lecture 14

  9. =“c” <c,2,10> <10,x,2> check=2? output: <2,x> next tuple: <c,7,15> 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 query processing - lecture 14

  10. Overview of Query Optimization query processing - lecture 14

  11. SQL query parse parse tree convert answer logical query plan execute apply laws statistics Pi “improved” l.q.p pick best estimate result sizes {(P1,C1),(P2,C2)...} l.q.p. +sizes estimate costs consider physical plans {P1,P2,…..} query processing - lecture 14

  12. SQL query parse parse tree convert answer logical query plan execute apply laws statistics Pi “improved” l.q.p pick best estimate result sizes {(P1,C1),(P2,C2)...} l.q.p. +sizes estimate costs consider physical plans {P1,P2,…..} query processing - lecture 14

  13. Example: SQL query Query : Find the movies with stars born in 1960 SELECT title FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE ‘%1960’ ); query processing - lecture 14

  14. <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’ Example: Parse Tree query processing - lecture 14

  15. 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 query processing - lecture 14

  16. Example: Logical Query Plan title starName=name  StarsIn name birthdate LIKE ‘%1960’ MovieStar Fig. 7.18: Applying the rule for IN conditions query processing - lecture 14

  17. Example: Improved Logical Query Plan title Question: Push project to StarsIn? starName=name StarsIn name birthdate LIKE ‘%1960’ MovieStar Fig. 7.20: An improvement on fig. 7.18. query processing - lecture 14

  18. Example: Estimate Result Sizes Need expected size StarsIn MovieStar P s query processing - lecture 14

  19. Example: One Physical Plan Parameters: join order, memory size, project attributes,... Hash join SEQ scan index scan Parameters: Select Condition,... StarsIn MovieStar query processing - lecture 14

  20. Example: Estimate costs L.Q.P P1 P2 …. Pn C1 C2 …. Cn Pick best! query processing - lecture 14

  21. Query Optimization • Relational algebra level … • Logical rewriting • Detailed query plan level … • Estimate costs • Generate and compare plans query processing - lecture 14

More Related