Query Processing - Advanced Database System - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Query Processing - Advanced Database System - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Advanced Database Systems

Notes 02:Query Processing

(Overview)

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.

SQL Primer (contd.)

Select

From

Where

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

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

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

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

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)]

Another idea:

ΠB,D

σR.A = “c” σ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

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

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

c 7 15

A C

I 1 I 2

=“c”

<c,2,10> (^) <10,x,2>

check=2?

output: <2,x>

next tuple: <c,7,15>

Example Query

Select B,D

From R,S

Where R.A = “c” ∧ R.C=S.C

Example: Parse Tree

SELECT FROM WHERE

AND

B R

S

R.A = “c”

R.C = S.C

D

Select B,D From R,S Where R.A = “c” ∧ R.C=S.C

parse

Query rewriting

Physical plan generation

execute

result

SQL query

parse tree

statistics logical query plan

physical query plan

Initial logical plan

“Best” logical plan

Logical plan

Rewrite rules

Initial Logical Plan

Relational Algebra: ΠB,D [ σR.A=“c”∧ R.C = S.C (RXS)]

Select B,D From R,S

Where R.A = “c” ∧

R.C=S.C

πB,D

σR.A = “c” Λ R.C = S.C

X

R S