Advanced Database Systems: Query Execution and Plan Optimization, Slides of Database Management Systems (DBMS)

Download Advanced Database Systems: Query Execution and Plan Optimization and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Advanced Database Systems

Notes 07:Query Execution

parse

Query rewriting

Physical plan generation

execute result

SQL query

parse tree

statistics logical query plan

physical query plan

Query Processing - In class order

Modern DBMS Architecture

Disk(s)

Applications

OS

Parser

Query Optimizer

Query Executor

Storage Manager

Logical query plan

Physical query plan

Access method API calls

SQL

Storage system API calls File system API calls

DBMS

Locial Plans Vs. Physical Plans

πB,D

σR.A = “c”

R

S

Natural join

Best logical plan

R S

Index scan (^) Table scan

Hash join

Project

πB,D

σR.A = “c”

R

S

Materialization

Materialized here

πB,D

σR.A = “c”

R

S

Iterators: Pipelining

 Each operator supports:

• Open()
• GetNext()
• Close()

Iterator for Select

Open() { /** initialize child */ Child.Open(); }

GetNext() { LOOP: t = Child.GetNext(); IF (t == EOT) { /** no more tuples */ RETURN EOT; } ELSE IF (t.A == “c”) RETURN t; ENDLOOP: }

Close() { /** inform child */ Child.Close(); }

σR.A = “c”

• NLJ (conceptually)

for each r ∈ Lexp do

for each s ∈ Rexp do

if Lexp.C = Rexp.C, output r,s

Iterator for Nested Loop Join

Lexp Rexp

Example 1: Left-Deep Plan

R1(A,B)

TableScan

R2(B,C)

TableScan

R3(C,D)

TableScan

TNLJ

TNLJ

Question: What is the sequence of getNext() calls?

Example 1 (contd.)

• Assume Statistics:
  • B(R1) = 1000 blocks, T(R1) = 10,000 tuples
  • B(R2) = 500 blocks, T(R2) = 5000 tuples
  • B(R3) = 1000 blocks, T(R3) = 10,000 tuples
  • Let X = R1 Join (R1.B = R2.B) R
  • T(X) = 1,000,000 tuples, B(X) = 200,000 blocks
  • Let Output = 1000 tuples
• Questions:
  • Number of getNext() calls?
  • Number of disk I/Os?
  • Assume we have 1000 blocks of memory, how can we improve the plan?

Example 2 (contd.)

• Assume Statistics:
  • B(R1) = 1000 blocks, T(R1) = 10,000 tuples
  • B(R2) = 500 blocks, T(R2) = 5000 tuples
  • B(R3) = 1000 blocks, T(R3) = 10,000 tuples
  • Let X = R1 Join (R1.B = R2.B) R
  • T(X) = 1,000,000 tuples, B(X) = 200,000 blocks
  • Let Output = 1000 tuples
• Questions:
  • Number of getNext() calls?
  • Number of disk I/Os?
  • Assume we have 1000 blocks of memory, how can we improve the plan?

Questions to think about

• What "shape" of plan works best for nested loop

joins: 'Left deep' (Example 1) or 'right deep'

(Example 2)?

• Will sorting help for nested loop join? (Hint: think

about clustered vs. unclustered indexes)

• Can materialization help for nested loop join?
• Generalize Example 1 (and 2) to 'n' relations:

What is the optimal use of M blocks of memory?

(I don't know the answer :-) )

Example 3 (contd.)

• Naive materialization:

• Compute hash join of R1, R2 (called X)
• Write output X to disk
• The (outer) hash join reads X (table scan) and

reads R3 and performs hash join

• What is the cost of naive materialization?

• Suggest an improved processing strategy

that shaves 2 B(X) from the above cost

Example 3 (contd.)

• Can this be completely pipelined if you

have limited memory?

• How much memory do you need to be

able to pipeline this plan?