CPS216: Advanced Database Systems Notes 02:Query Processing (Overview)

1
CPS216 Advanced Database SystemsNotes 02Query
Processing (Overview)

• Shivnath Babu

2
Query Processing

• Declarative SQL Query ? Query Plan

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.
Focus Relational System (i.e., data is organized
as tables, or relations)
3
Course Outline

• 50 of the class is about core DBMS concepts
• Query execution, query optimization,
  transactions, recovery, etc.
• Textbook material
• 50 of the class is on what is happening today
  in data management
• New developments on textbook material
• Web search Google, Yahoo!
• Solid State Drives (Flash Drives)
• Simplifying database mgmt., self-tuning databases
• Data streams
• Data integration (structured data unstructured
  data)
• Data mining

4
Course Logistics

• Reference Database Systems The Complete Book,
  by H. Garcia-Molina, J. D. Ullman, and J. Widom
• Web site http//www.cs.duke.edu/courses/fall08/cp
  s216
• Grading
• Project 30
• Homework Assignments 20
• Midterm 20
• Final 30

5
SQL Primer
We will focus on SPJ, or Select-Project-Join
Queries

• Select ltattribute listgt
• From ltrelation listgt
• Where ltcondition listgt
• Example Filter Query over R(A,B,C)
• Select B
• From R
• Where R.A c ? R.C gt 10

6
SQL Primer (contd.)
We will focus on SPJ, or Select-Project-Join-Queri
es

• Select ltattribute listgt
• From ltrelation listgt
• Where ltcondition listgt
• 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

7
R A B C S C D E a 1 10 10 x 2 b 1 20 2
0 y 2 c 2 10 30 z 2 d 2 35 40 x 1 e 3 45 50
y 3
Select B,D From R,S Where R.A c ? S.E
2 ? R.CS.C
8
How do we execute this query?
Select B,D From R,S Where R.A c ? S.E
2 ? R.CS.C

• - Do Cartesian product
• - Select tuples
• - Do projection

One idea
9
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 . .
Select B,D From R,S Where R.A c ? S.E 2
? R.CS.C
10
Relational Algebra - can be used to
describe plans

• Ex Plan I
• ?B,D
• sR.Ac? S.E2 ? R.CS.C
• X
• R S

11
Relational Algebra Primer (Chapter 5, GMUW)

• Select sR.Ac? R.C10
• Project ?B,D
• Cartesian Product R X S
• Natural Join R S

12
Relational Algebra - can be used to
describe plans

• Ex Plan I
• ?B,D
• sR.Ac? S.E2 ? R.CS.C
• X
• R S

OR ?B,D sR.Ac? S.E2 ? R.C S.C (RXS)
13
Another idea

• ?B,D
• sR.A c sS.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.CS.C
14
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.CS.C
15
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
16
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
I1
I2
17
Overview of Query Processing
SQL query
parse
parse tree
Query rewriting
Query Optimization
logical query plan
statistics
Physical plan generation
physical query plan
execute
Query Execution
result
18
Example Query

• Select B,D
• From R,S
• Where R.A c ? R.CS.C

19
Example Parse Tree
ltQuerygt
ltSFWgt
SELECT ltSelListgt FROM ltFromListgt
WHERE ltCondgt
ltAttributegt ltSelListgt ltRelNamegt ltFromListgt
ltCondgt AND ltCondgt
B ltAttributegt R ltRelNamegt
ltAttrgt ltOpgt ltConstgt
D
S
R.A

c
Select B,D From R,S Where R.A c ? R.CS.C
ltAttrgt ltOpgt ltAttrgt
R.C
S.C

20
Along with Parsing

• Semantic checks
• Do the projected attributes exist in the
  relations in the From clause?
• Ambiguous attributes?
• Type checking, ex R.A gt 17.5
• Expand views

21
SQL query
Initial logical plan
parse
Rewrite rules
parse tree
Query rewriting
Logical plan
statistics
logical query plan
Physical plan generation
Best logical plan
physical query plan
execute
result
22
Initial Logical Plan
?B,D
Select B,D From R,S Where R.A c ? R.CS.C
?R.A c ? R.C S.C
X
R
S
Relational Algebra ?B,D sR.Ac? R.C S.C
(RXS)
23
Apply Rewrite Rule (1)
?B,D
?B,D
?R.C S.C
?R.A c ? R.C S.C
?R.A c
X
X
R
S
R
S
?B,D sR.CS.C ?R.Ac(R X S)
24
Apply Rewrite Rule (2)
?B,D
?B,D
?R.C S.C
?R.C S.C
?R.A c
X
?R.A c
S
X
R
S
R
?B,D sR.CS.C ?R.Ac(R) X S
25
Apply Rewrite Rule (3)
?B,D
?B,D
?R.C S.C
Natural join
?R.A c
X
S
?R.A c
S
R
R
?B,D ?R.Ac(R) S
26
SQL query
Initial logical plan
parse
Rewrite rules
parse tree
Query rewriting
Logical plan
statistics
logical query plan
Physical plan generation
Best logical plan
physical query plan
execute
result