1 / 26

CS4432: Database Systems II

CS4432: Database Systems II. Lecture #13. 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

arista
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 #13 Professor Elke A. Rundensteiner query processing

  2. Query Processing Query in SQL  Query Plan in Algebra query processing

  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

  4. SELECT B, D FROM R, S WHERE R.A = “c” and S.E = 2 and R.C=S.C Answer B D 2 x 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 query processing

  5. How do we execute query? - Form Cartesian product of all tables in FROM-clause - Select tuples that match WHERE-clause - Project columns that occur in SELECT-clause One idea query processing

  6. Bingo! Got one... R X S 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 . . query processing

  7. But ? • Performance would be unacceptable! • We need a better approach for: • reasoning about queries, • their execution orders and • their respective costs query processing

  8. Relational Algebra ? Query: SELECT B, D FROM R, S WHERE R.A = “c” and S.E = 2 and R.C=S.C query processing

  9. Relational Algebra - can be used to describe plans... 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

  10. R1 Example Instances “Sailors” and “Reserves” relations S1 S2 query processing

  11. Relational Algebra • Basic operations: • Selection ( ) Selects a subset of rows from relation. • Projection ( ) Deletes unwanted columns from relation. • Cross-product( ) 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. • Additional operations: • Intersection, join, division, renaming: Not essential ! • Algebra is “closed”: Since each operation returns a relation, operationscan be composed ! query processing

  12. Projection query processing

  13. Selection query processing

  14. Union, Intersection, Set Difference • Operate on two union-compatible relations: • Same number of fields. • `Corresponding’ fields have same type. query processing

  15. Cross-Product • Each row of S1 is paired with each row of R1. • Conflict: Both S1 and R1 have a field called sid. • Renaming operator: query processing

  16. Joins • Condition Join : • Result schema same as that of cross-product. query processing

  17. Joins • Equi-Join: condition contains only equalities. • Result schema only one copy of fields for which equality is specified. • Natural Join: Equijoin on all common fields. query processing

  18. Division • Not primitive operator, but useful: Find sailors who have reserved allboats. • A has 2 fields x and y; B has only field y: • A/B = • i.e., A/B contains all x tuples (sailors) such that for everyy tuple (boat) in B, there is an xy tuple in A. query processing

  19. B1 B2 B3 A Examples of Division A/B A/B1 A/B2 A/B3 query processing

  20. Disqualified x values: A/B: all disqualified tuples Expressing A/B Using Basic Operators • Division is useful shorthand. • Idea: For A/B, compute all x values that are not `disqualified’ by some y value in B. query processing

  21. Solution 1: • Solution 2: • Solution 3: Find names of sailors who’ve reserved boat #103 query processing

  22. A more efficient solution: Find names of sailors who’ve reserved a red boat • Information about boat color only available in Boats; so need an extra join: A query optimizer can find this, given the first solution! query processing

  23. What happens if is replaced by in this query? Find sailors who’ve reserved a red or a green boat • Can identify all red or green boats, then find sailors who’ve reserved one of these boats: • Can also define Tempboats using union! (How?) query processing

  24. Find sailors who’ve reserved a red and a green boat • Must identify sailors who’ve reserved red boats, sailors who’ve reserved green boats, then find the intersection (sid is a key for Sailors): query processing

  25. Find names of sailors who’ve reserved all boats • Uses division; schemas of input relations to / must be carefully chosen: • To find sailors who’ve reserved all ‘Interlake’ boats: ..... query processing

  26. Relational Algebra representation used to describe plans... query processing

More Related