qp_overview

qp_overview - CPS216: Data-Intensive Computing Systems...

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

View Full Document Right Arrow Icon
CPS216: Data-Intensive Computing Systems Introduction to Query Processing Shivnath Babu
Background image of page 1

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

View Full DocumentRight Arrow Icon
Query Processing Declarative SQL Query Query Plan Focus: Relational System (i.e., data is organized as tables, or relations) NOTE: You will not be tested on how well you know SQL. Understanding the SQL introduced in class will be sufficient (a primer follows). SQL is described in Chapter 6, GMUW.
Background image of page 2
SQL Primer Select <attribute list> From <relation list> Where <condition list> Example Filter Query over R(A,B,C): Select B From R Where R.A = “c” R.C > 10 We will focus on SPJ, or Select-Project-Join Queries
Background image of page 3

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

View Full DocumentRight Arrow Icon
SQL Primer (contd.) Select <attribute list> From <relation list> Where <condition list> Example Join Query over R(A,B,C) and S(C,D,E): Select B, D From R, S Where R.A = “c” S.E = 2 R.C = S.C We will focus on SPJ, or Select-Project-Join-Queries
Background image of page 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 Select B,D From R,S Where R.A = “c” S.E = 2 R.C=S.C
Background image of page 5

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

View Full DocumentRight Arrow Icon
• How do we execute this query? - Do Cartesian product - Select tuples - Do projection One idea Select B,D From R,S Where R.A = “c” S.E = 2 R.C=S.C
Background image of page 6
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 . . Bingo! Got one. .. Select B,D From R,S Where R.A = “c” S.E = 2 R.C=S.C
Background image of page 7

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

View Full DocumentRight Arrow Icon
Relational Algebra - can be used to describe plans Ex: Plan I B,D s R.A =“c” S.E=2 R.C=S.C X R S
Background image of page 8
Relational Algebra Primer (Chapter 5, GMUW) Select: s R.A =“c” R.C=10 Project: B,D Cartesian Product: R X S Natural Join: R S
Background image of page 9

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

View Full DocumentRight Arrow Icon
Relational Algebra - can be used to describe plans Ex: Plan I B,D s R.A =“c” S.E=2 R.C=S.C X R S OR: B,D [ s R.A=“c” S.E=2 R.C = S.C (RXS)]
Background image of page 10
Another idea: B,D s R.A = “c” s S.E = 2 R(A,B,C) S(C,D,E) Plan II natural join Select B,D From R,S Where R.A = “c” S.E = 2 R.C=S.C
Background image of page 11

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

View Full DocumentRight Arrow Icon
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” S.E = 2 R.C=S.C
Background image of page 12
Plan III Use R.A and S.C Indexes (1) Use R.A index to select R tuples
Background image of page 13

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

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

Page1 / 49

qp_overview - CPS216: Data-Intensive Computing Systems...

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

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