cs4432 database systems ii n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
CS4432: Database Systems II PowerPoint Presentation
Download Presentation
CS4432: Database Systems II

Loading in 2 Seconds...

play fullscreen
1 / 28

CS4432: Database Systems II - PowerPoint PPT Presentation


  • 73 Views
  • Uploaded on

CS4432: Database Systems II. Lecture #13 Query Processing. Professor Elke A. Rundensteiner. Query in SQL  Query Plan in Algebra (logical)  Other Query Plan in Algebra (logical). Query plan 1 (in relational algebra).  B,D

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
cs4432 database systems ii

CS4432: Database Systems II

Lecture #13

Query Processing

Professor Elke A. Rundensteiner

query processing - lecture 13

slide2
Query in SQL

Query Plan in Algebra (logical)

Other Query Plan in Algebra (logical)

query processing - lecture 13

query plan 1 in relational algebra
Query plan 1 (in relational algebra)

B,D

sR.A=“c” S.E=2  R.C=S.C

X

R S

query processing - lecture 13

query plan 2 in relational algebra
Query plan 2 (in relational algebra)

B,D

sR.A = “c”sS.E = 2

R S

natural join

query processing - lecture 13

relational algebra optimization
Relational algebra optimization
  • What are transformation rules ?
    • preserve equivalence
  • What are good transformations?
    • reduce query execution costs

query processing - lecture 13

rules natural joins cross products union
Rules:Natural joins & cross products & union

R S = S R

(R S) T = R (S T)

query processing - lecture 13

slide7
Note:
  • Carry attribute names in results, so order is not important
  • Can also write as trees, e.g.:

T R

R S S T

query processing - lecture 13

rules natural joins cross products union1
Rules:Natural joins & cross products & union

R S = S R

(R S) T = R (S T)

R x S = S x R

(R x S) x T = R x (S x T)

R U S = S U R

R U (S U T) = (R U S) U T

query processing - lecture 13

rules selects
Rules: Selects

sp1p2(R) =

sp1vp2(R) =

sp1 [ sp2 (R)]

[ sp1 (R)] U [ sp2 (R)]

query processing - lecture 13

bags vs sets
Bags vs. Sets

R = {a,a,b,b,b,c}

S = {b,b,c,c,d}

RUS = ?

  • Option 1 SUM

RUS = {a,a,b,b,b,b,b,c,c,c,d}

  • Option 2 MAX

RUS = {a,a,b,b,b,c,c,d}

query processing - lecture 13

slide11
Option 2 (MAX) makes this rule work:sp1vp2 (R) = sp1(R) U sp2(R)

Example: R={a,a,b,b,b,c}

P1 satisfied by a,b; P2 satisfied by b,c

sp1vp2 (R) = {a,a,b,b,b,c}sp1(R) = {a,a,b,b,b}sp2(R) = {b,b,b,c}sp1(R) U sp2 (R) = {a,a,b,b,b,c}

query processing - lecture 13

sum option makes more sense
“Sum” option makes more sense:

Senators (……) Rep (……)

T1 = pyr,state Senators; T2 = pyr,state Reps

T1 Yr State T2 Yr State

97 CA 99 CA

99 CA 99 CA

98 AZ 98 CA

Union?

query processing - lecture 13

executive decision
Executive Decision

-> Use “SUM” option for bag unions

-> Some rules cannot be used for bags

query processing - lecture 13

slide14

Rules: Project

Let: X = set of attributes

Y = set of attributes

XY = X U Y

pxy (R) =

px [py (R)]

query processing - lecture 13

rules s combined

[sp (R)] S

R [sq (S)]

Rules:s + combined

Let p = predicate with only R attribs

q = predicate with only S attribs

m = predicate with only R,S attribs

sp (R S) =

sq (R S) =

query processing - lecture 13

rules s combined continued
Rules:s + combined (continued)

Some Rules can be Derived:

spq (R S) =

spqm (R S) =

spvq (R S) =

query processing - lecture 13

do one others for homework
Do one, others for homework:

spq (R S) = [sp (R)] [sq (S)]

spqm (R S) =

sm[(sp R) (sq S)]

spvq (R S) =

[(sp R) S] U [R(sq S)]

query processing - lecture 13

derivation for first one
--> Derivation for first one:

spq (R S) =

sp [sq (R S) ] =

sp[ R sq (S) ] =

[sp (R)] [sq (S)]

query processing - lecture 13

rules p s combined

pxz

px

Rules:p,s combined

Let x = subset of R attributes

z = attributes in predicate P (subset of R attributes)

px[sp (R) ] =

{sp [ px (R) ]}

query processing - lecture 13

slide20

pxy{[pxz (R) ][pyz (S) ]}

Rules:p, combined

Let x = subset of R attributes

y = subset of S attributes

z = intersection of R,S attributes

pxy (R S)=

query processing - lecture 13

slide21

pxy {sp[pxz’ (R) pyz’ (S)]}

z’ = z U {attributes used in P }

pxy {sp(R S)} =

query processing - lecture 13

rules for s p combined with x
Rules for s,p combined with X

similar...

e.g., sp (R X S) = ?

query processing - lecture 13

rules s u combined
Rules s,U combined:

sp(R U S)= sp(R) U sp(S)

sp(R - S)= sp(R) - S = sp(R) - sp(S)

query processing - lecture 13

which are good transformations
Which are “good” transformations?

sp1p2 (R) sp1 [sp2 (R)]

sp (R S)  [sp (R)] S

R S  S R

px [sp(R)] px {sp [pxz(R)]}

query processing - lecture 13

conventional wisdom do projects early
Conventional wisdom: do projects early

Example: R(A,B,C,D,E) x={E} P: (A=3)  (B=“cat”)

px {sp(R)} vs. pE {sp{pABE(R)}}

query processing - lecture 13

what if we have a b indexes
What if we have A, B indexes?

But

B = “cat” A=3

Intersect pointers to get

pointers to matching tuples

query processing - lecture 13

bottom line
Bottom line:
  • No transformation is always good
  • Usually good: early selections

query processing - lecture 13

in textbook more transformations
In textbook: more transformations
  • Eliminate common sub-expressions
  • Other operations, such as, duplicate elimination and others.

query processing - lecture 13