DBDI 30/05/2007
Lecture 15 1
DBDI / Lecture 15
Query Evaluation,
Processing & Optimisation
Dr. Ala Al-Zobaidie
The slides are based on the textbook Database Systems by Thomas Connolly & Carolyn Begg
30/05/2007 Lecture 15 2
Objectives
• Purpose of query optimisation
• The steps of query evaluation
– Static versus dynamic query optimisation
• Optimisation Techniques
– Heuristic
–Graphs
– Cost Optimisation
–Semantic
• An example with a typical tool
• Distributed optimisation
30/05/2007 Lecture 15 3
SQL – What, Not How
• The idea of declarative languages
like SQL is that you simply say
WHAT you want
– You don’t say how the work is to be
done
– It simplifies the life of the RDMS user
enormously
• The y simply don’t have to care
about low level details
• Only about the business problem
they are trying to solve
• And the RDMS is free to control system
performance
• But the work has to be done
– And done quickly and accurately
30/05/2007 Lecture 15 4
Relational Algebra
• Remember that underlying the relational
model is the Relation Algebra RA
– Set and sub-sets and intersections and so on
• The RDMS takes the SQL and converts it
to the RA
– But if you remember RA there were lots of
ways to write down the same thing
– And (from the RDMS) lots of orders in which
those operations can take place
30/05/2007 Lecture 15 5
Example - Different Strategies
Find all Managers who work at a London
branch.
SELECT *
FROM Staff s, Branch b
WHERE
s.branchNo = b.branchNo AND
(s.position = ‘Manager’ AND
b.city = ‘London’);
30/05/2007 Lecture 15 6
Strategy One
Staff Branch
staff.branchNo =
branch.branchNo
staff.position =
‘manager’
branch.city =
‘London’
σ(position='Manager') ∧(city='London')
(Staff Staff.branchNo = Branch.branch No Branch)
