Database Management Systems

Query optimization

DB
MG
1
 DBMS Architecture

SQL INSTRUCTION

OPTIMIZER

CONCURRENCY CONTROL
MANAGEMENT OF ACCESS
METHODS

BUFFER MANAGER RELIABILITY MANAGEMENT

Index Files
System DATABASE
Catalog
Data Files

2
DB
MG
 Query optimizer

It selects an efficient strategy for query execution

It is a fundamental building block of a relational
DBMS
It guarantees the data independence property
The form in which the SQL query is written does
not affect the way in which it is implemented
A physical reorganization of data does not require
rewriting SQL queries

3
DB
MG
 Query optimizer

It automatically generates a query execution plan

It was formerly hard-coded by a programmer
The automatically generated execution plan is
usually more efficient
It evaluates many different alternatives
It exploits statistics on data, stored in the system
catalog, to make decisions
It exploits the best known strategies
It dynamically adapts to changes in the data
distribution

4
DB
MG
 Query optimizer
SQL
QUERY

LEXICAL, SYNTACTIC
AND SEMANTIC
ANALYSIS

5
DB
MG
 Lexical, syntactic and semantic analysis

Analysis of a statement to detect

Lexical errors
e.g., misspelled keywords
Syntactic errors
errors in the grammar of the SQL language
Semantic errors
references to objects which do not actually exist in
the database (e.g, attributes or tables)
information in the data dictionary is needed

6
DB
MG
 Lexical, syntactic and semantic analysis

Output
Internal representation in (extended) relational
algebra
Why relational algebra?
It explicitly represents the order in which operators
are applied
It is procedural (different from SQL)
There is a corpus of theorems and properties
exploited to modify the initial query tree

7
DB
MG
 Query optimizer
SQL
QUERY

LEXICAL, SYNTACTIC
AND SEMANTIC DATA
ANALYSIS DICTIONARY

INTERNAL REPRESENTATION
BASED ON RELATIONAL ALGEBRA

ALGEBRAIC
OPTIMIZATION

8
DB
MG
 Algebraic optimization

Execution of algebraic transformations

considered to be always beneficial
Example: anticipation of selection with respect to
join
Should eliminate the difference among different
formulations of the same query
This step is usually independent of the data
distribution
Output
Query tree in “canonical” form

9
DB
MG
 Query optimizer
SQL
QUERY

LEXICAL, SYNTACTIC
AND SEMANTIC DATA
ANALYSIS DICTIONARY

INTERNAL REPRESENTATION
BASED ON RELATIONAL ALGEBRA

ALGEBRAIC
OPTIMIZATION

“CANONICAL” QUERY TREE

COST BASED
OPTIMIZATION

10
DB
MG
 Cost based optimization

Selection of the “best” execution plan by

evaluating execution cost
Selection of
the best access method for each table
the best algorithm for each relational operator
among available alternatives
Based on a cost model for access methods and
algorithms
Generation of the code implementing the best
strategy

11
DB
MG
 Cost based optimization

Output
Access program in executable format
It exploits the internal structures of the DBMS
Set of dependencies
conditions on which the validity of the query plan
depends
e.g., the existence of an index

12
DB
MG
 Query optimizer
SQL
QUERY

LEXICAL, SYNTACTIC
AND SEMANTIC DATA
ANALYSIS DICTIONARY

INTERNAL REPRESENTATION
BASED ON RELATIONAL ALGEBRA

ALGEBRAIC
OPTIMIZATION

“CANONICAL” QUERY TREE

COST BASED DATA PROFILES

OPTIMIZATION (STATISTICS ON
DATA)

13
DB
MG
ACCESS PROGRAM SET OF DEPENDENCIES
 Execution modes

Compile and go
Compilation and immediate execution of the
statement
No storage of the query plan
Dependencies are not needed

14
DB
MG
 Execution modes

Compile and store

The access plan is stored in the database together
with its dependencies
It is executed on demand
It should be recompiled when the data structure
changes

15
DB
MG
 Database Management Systems

Algebraic optimization

DB
MG
16
 Algebraic optimization
SQL
QUERY

LEXICAL, SYNTACTIC
AND SEMANTIC DATA
ANALYSIS DICTIONARY

INTERNAL REPRESENTATION
BASED ON RELATIONAL ALGEBRA

ALGEBRAIC
OPTIMIZATION

“CANONICAL” QUERY TREE

COST BASED DATA PROFILES

OPTIMIZATION (STATISTICS ON
DATA)

17
DB
MG
ACCESS PROGRAM SET OF DEPENDENCIES
 Algebraic optimization

It is based on equivalence transformations

Two relational expressions are equivalent if they
both produce the same query result for any
arbitrary database instance
Interesting transformations
reduce the size of the intermediate result to be
stored in memory
prepare an expression for the application of a
transformation which reduces the size of the
intermediate result

18
DB
MG
 Transformations

1. Atomization of selection
sF1 Ʌ F2 (E) ≡ sF2 (sF1 (E)) ≡ sF1 (sF2 (E))

19
DB
MG
 Transformations

1. Atomization of selection
sF1 Ʌ F2 (E) ≡ sF2 (sF1 (E)) ≡ sF1 (sF2 (E))
2. Cascading projections
pX(E) ≡ pX (pX,Y(E))

20
DB
MG
 Transformations

1. Atomization of selection
sF1 Ʌ F2 (E) ≡ sF2 (sF1 (E)) ≡ sF1 (sF2 (E))
2. Cascading projections
pX(E) ≡ pX (pX,Y(E))
3. Anticipation of selection with respect to join
(pushing selection down)
sF (E1 E2) ≡ E1 (sF (E2))
F is a predicate on attributes in E2 only

21
DB
MG
 Transformations

4. Anticipation of projection with respect to join

pL(E1 p E2) ≡ pL ((pL1, J(E1)) p (pL2,J(E2)))
L1 = L - Schema(E2)
L2 = L - Schema(E1)
J = set of attributes needed to evaluate join
predicate p

22
DB
MG
 Transformations

5. Join derivation from Cartesian product

sF (E1  E2 ) ≡ E1 F E2
predicate F only relates attributes in E1 and E2

23
DB
MG
 Transformations

5. Join derivation from Cartesian product

sF (E1  E2 ) ≡ E1 F E2
predicate F only relates attributes in E1 and E2
6. Distribution of selection with respect to union
sF(E1  E2 ) ≡ (sF (E1) )  (sF (E2) )

24
DB
MG
 Transformations

5. Join derivation from Cartesian product

sF (E1  E2 ) ≡ E1 F E2
predicate F only relates attributes in E1 and E2
6. Distribution of selection with respect to union
sF(E1  E2 ) ≡ (sF (E1) )  (sF (E2) )
7. Distribution of selection with respect to
difference
sF(E1 – E2) ≡ (sF (E1) ) – (sF (E2) )
≡ (sF (E1)) – E2

25
DB
MG
 Transformations

8. Distribution of projection with respect to union

pX(E1  E2) ≡ (pX(E1))  (pX(E2))

26
DB
MG
 Transformations

8. Distribution of projection with respect to union

pX(E1  E2) ≡ (pX(E1))  (pX(E2))

Can projection be distributed with respect to

difference?
pX (E1 - E2) ≡ (pX(E1)) - (pX(E2))

27
DB
MG
 Transformations

8. Distribution of projection with respect to union

pX(E1  E2) ≡ (pX(E1))  (pX(E2))

Can projection be distributed with respect to

difference?
pX (E1 - E2) ≡ (pX(E1)) - (pX(E2))

This equivalence only holds if X includes the

primary key or a set of attributes with the same
properties (unique and not null)

28
DB
MG
 Transformations

9. Other properties
sF1 V F2(E) ≡ (sF1 (E))  (sF2 (E))
sF1 Ʌ F2(E) ≡ (sF1 (E))  (sF2 (E))

29
DB
MG
 Transformations

10. Distribution of join with respect to union

E (E1  E2) ≡ (E E1)  (E E2 )

All binary operators are commutative and

associative except for difference

30
DB
MG
 Example

Tables
EMP (Emp#, ………, Dept#, Salary)
DEPT (Dept#, DName,……………)

SQL query

SELECT DISTINCT DName

FROM EMP, DEPT
WHERE EMP.Dept#=DEPT.Dept#
AND Salary > 1000;

31
DB
MG
 Example: Algebraic transformations

pDName (sEMP.Dept#=DEPT.Dept# Ʌ Salary >1000 (EMP  DEPT))

32
DB
MG
 Example: Algebraic transformations

pDName (sEMP.Dept#=DEPT.Dept# Ʌ Salary >1000 (EMP  DEPT))

Prop #1

pDName(sSalary >1000 (sEMP.Dept#=DEPT.Dept# (EMPDEPT))

33
DB
MG
 Example: Algebraic transformations

pDName (sEMP.Dept#=DEPT.Dept# Ʌ Salary >1000 (EMP  DEPT))

Prop #1

pDName(sSalary >1000 (sEMP.Dept#=DEPT.Dept# (EMPDEPT))

Prop #5

pDName(sSalary >1000 (EMP DEPT)

34
DB
MG
 Example: Algebraic transformations

pDName(sSalary >1000 (EMP DEPT)

Prop #3

pDName(sSalary >1000 (EMP)) DEPT)

35
DB
MG
 Example: Algebraic transformations

pDName(sSalary >1000 (EMP DEPT)

Prop #3

pDName(sSalary >1000 (EMP)) DEPT)

Prop #2 and #4

pDName ((pDept# (sSalary >1000(EMP)) (pDept#,DName(DEPT)))

36
DB
MG
 Example: Query tree

Final query tree

pDName

pDept# pDept#,DName

sSalary>1000 DEPT

EMP

37
DB
MG
 Example: Cardinalities

Cardinality (EMP) ≈ 10,000

Cardinality (DEPT) ≈ 100
Cardinality (EMP where Salary > 1000) ≈ 50

38
DB
MG
 Database Management Systems

Cost based optimization

DB
MG
39
 Cost based optimization
SQL
QUERY

LEXICAL, SYNTACTIC
AND SEMANTIC DATA
ANALYSIS DICTIONARY

INTERNAL REPRESENTATION
BASED ON RELATIONAL ALGEBRA

ALGEBRAIC
OPTIMIZATION

“CANONICAL” QUERY TREE

COST BASED DATA PROFILES

OPTIMIZATION (STATISTICS ON
DATA)

40
DB
MG
ACCESS PROGRAM SET OF DEPENDENCIES
 Cost based optimization

It is based on
Data profiles
statistical information describing data distribution for
tables and intermediate relational expressions
Approximate cost formulas for access operations
Allow evaluating the cost of different alternatives for
executing a relational operator

41
DB
MG
 Database Management Systems

Data profiles

DB
MG
42
 Table profiles

Quantitative information on the characteristics of

tables and columns
cardinality (# of tuples) in each table T
also estimated for intermediate relational
expressions
size in bytes of tuples in T
size in bytes of each attribute Aj in T
number of distinct values of each attribute in T
cardinality of the active domain of the attribute
min and max values of each attribute Aj in T

43
DB
MG
 Table profiles

Table profiles are stored in the data dictionary

Profiles should be periodically refreshed by re-
analyzing data in the tables
Update statistics command
Executed on demand
immediate execution during transaction processing
would overload the system

44
DB
MG
 Data profiles

Table profiles are exploited to estimate the size

of intermediate relational expressions
For the selection operator
Card (sAi = v (T)) ≈ Card (T)/ Val (Ai in T)
Val (Ai in T) = # of distinct values of Ai in T (active
domain)
It holds only under the hypothesis of uniform
distribution

45
DB
MG
 Database Management Systems

Access operators

DB
MG
46
 Query tree

Internal representation of the relational

expression as a query tree
pDName

pDept# pDept#,DName

sSalary>1000 DEPT

EMP

47
DB
MG
 Query tree

Leaves correspond to the physical structures

tables, indices
Intermediate nodes are operations on data
supported by the given physical structure
e.g., scan, join, group by

48
DB
MG
 Sequential scan

Executes sequential access to all tuples in a table

also called full table scan
Operations performed during a sequential scan
Projection
discards unnecessary columns
Selection on a simple predicate (Ai=v)
Sorting based on an attribute list
Insert, update, delete

49
DB
MG
 Sorting

Classical algorithms in computer science are

exploited
e.g., quick sort
Size of data is relevant
memory sort
sort on disk

50
DB
MG
 Predicate evaluation

If available, it may exploit index access

B+-tree, hash, or bitmap
Simple equality predicate Ai=v
Hash, B+-tree, or bitmap are appropriate
Range predicate v1 ≤ Ai ≤ v2
only B+-tree is appropriate
For predicates with limited selectivity full table
scan is better
if available, consider bitmap

51
DB
MG
 B+-tree versus bitmap

Bitmap VS B-Tree
600

500
Disk space (MB)

400

300

200

100

0
0 5 10 15 20 25 30 35 40 45
NK
B-Tree Bitmap

B-tree NRLen(Pointer)
Bitmap NR  NK  1 bit Courtesy of Golfarelli, Rizzi,
”Data warehouse, teoria e
Len(Pointer) = 48 bit pratica della progettazione”,
McGraw Hill 2006 52
DB
MG
 Predicate evaluation

Conjunction of predicates Ai= v1 Ʌ Aj= v2

The most selective predicate is evaluated first
Table is read through the index
Next the other predicates are evaluated on the
intermediate result
Optimization
First compute the intersection of bitmaps or RIDs
coming from available indices
Next table read and evaluation of remaining
predicates

53
DB
MG
 Example: Predicate evaluation

Which female students living in Piemonte are

exempt from enrollment fee?
RID Gender Exempt Region
1 M Y Piemonte
2 F Y Liguria
3 M N Puglia
4 M N Sicilia Gender Exempt Piemonte

5 F Y Piemonte 0 1 1
1 1 0
0 0 0
0 0 0
1 1 1

DB RID 5 54
MG
 Predicate evaluation

Disjunction of predicates Ai= v1 V Aj= v2

Index access can be exploited only if all predicates
are supported by an index
otherwise full table scan

55
DB
MG
 Join operation

A critical operation for a relational DBMS

connection between tables is based on values
instead of pointers
size of the intermediate result is typically larger
than the smaller table
Different join algorithms
Nested loop
Merge scan join
Hash join
Bitmapped join

56
DB
MG
 Nested loop

Outer table Inner table

A A
external
scan a

a a

internal a
or direct scan

join
attribute 57
DB
MG
 Nested loop

A single full scan is done on the outer table

For each tuple in the outer table
a full scan of the inner table is performed, looking
for corresponding values
Also called “brute force”

58
DB
MG
 Nested loop

Efficient when
inner table is small and fits in memory
optimized scan
join attribute in the inner table is indexed
index scan
Execution cost
The nested loop join technique is not symmetric
The execution cost depends on which table takes
the role of inner table

59
DB
MG
 Merge scan
right
Left table left scan Right table
scan
A A
a a

b
d
b
b
c
e
e

join
attribute 60
DB
MG
 Merge scan

Both tables are sorted on the join attributes

The two tables are scanned in parallel
tuple pairs are generated on corresponding values
Execution cost
The merge scan technique is symmetric
requires sorting both tables
may be sorted by a previous operation
may be read through a clustered index on join
attributes
More used in the past
efficient for large tables, because sorted tables
may be stored on disk 61
DB
MG
 Hash Join
From From
left table right table
Buckets for Buckets for
HASH(a) HASH(a)
left table right table
d e
e m
a a
c w

j j
p z

Join
62
DB
MG
Attribute
 Hash join

Application of the same hash function to the join

attributes in both tables
Tuples to be joined end up in the same buckets
collisions are generated by tuples yielding the same
hash function result with different attribute value
A local sort and join is performed into each bucket
Very fast join technique

63
DB
MG
 Bitmapped join index

Bit matrix that precomputes the join between

two tables A and B
One column for each RID in table A
One row for each RID in table B
Position (i, j) of the matrix is
1 if tuple with RID j in table A joins with tuple with
RID i in table B
RID 1 2 … n
0 otherwise 1 0 0 … 1
Updates may be slow 2 0 1 … 0
3 0 0 … 1
4 1 0 … 0
… … … … 0 64
DB
MG
 Bitmapped join

Typically used in OLAP queries

joining several tables with a large central table
Example
Exam table, joined to Student and Course tables
Exploits one or more bitmapped join indices
One for each pair of joined tables
Access to the large central table is the last step

65
DB
MG
 Bitmapped join

Complex queries may exploit jointly

bitmapped join indices
bitmap indices for predicates on single tables

66
DB
MG
 Example: Bitmapped join

Average score of male students for exams of

courses in the first year of the master degree
STUDENT (Reg#, SName, Gender)
COURSE (Course#, CName, CourseYear)
EXAM (Reg#, Course#, Date, Grade)

SELECT AVG (Grade)

FROM STUDENT S, EXAM E, COURSE C
WHERE E.Reg# = S.Reg#
AND E.Course# = C.Course#
AND CourseYear = ‘1M’
AND Gender = ‘M’;

67
DB
MG
 Bitmapped join

… FROM EXAM E, COURSE C Bitmap for CourseYear attribute

WHERE E.Course# = C.Course# RID … 1M … …
AND CourseYear = ‘1M’ … 1 0 1 … 0
2 0 0 … 0
RIDs 1 and 4 3 0 0 … 1
Bitmapped join index 4 0 1 … 0
for Course-Exams join
5 1 0 … 0
RID 1 … 4 …
1 0 … 1 1 1 4 RIDCY

2 0 … 1 0 0 1 1

3 0 … 0 1 0 OR 1 = 1

4 1 … 0 0 0 0 0

… … … … … 1 0 1
… … …
68
DB
MG
 Bitmapped join

RIDCY RIDG RID

1 1 1
1 0 0 RIDs of Exam table
AND =
0 0 0 for tuples to be read
1 1 1
… … …

bitmap for Course-Exam bitmap for Student-Exam

predicates and join predicates and join

69
DB
MG
 Group by

Sort based
Sort on the group by attributes
Next compute aggregate functions on groups
Hash based
Hash function on the group by attributes
Next sort each bucket and compute aggregate
functions
Materialized views may be exploited to improve
the performance of aggregation operations

70
DB
MG
 Database Management Systems

Execution plan selection

DB
MG
71
 Cost based optimization

Inputs
Data profiles
Internal representation of the query tree
Output
“Optimal” query execution plan
Set of dependencies
It evaluates the cost of different alternatives for
reading each table
executing each relational operator
It exploits approximate cost formulas for access
operations
72
DB
MG
 General approach to optimization

The search for the optimal plan is based on the

following dimensions
The way data is read from disk
e.g., full scan, index
The execution order among operators
e.g., join order between two join operations
The technique by means of which each operator is
implemented
e.g., the join method
When to perform sort (if sort is needed)

73
DB
MG
 General approach to optimization

The optimizer builds a tree of alternatives in

which
each internal node makes a decision on a variable
each leaf represents a complete query execution
plan

74
DB
MG
 Example

Given 3 tables
R, S, T
Compute the join
R S T
Execution alternatives
4 join techniques to evaluate (for both joins)
3 join orders
In total, at most
4 * 4 * 3 = 48 different alternatives

75
DB
MG
 Example

R 1
S 2
T

R 1
S 2
T S 1
T 2
R R 1
T 2
S

NESTED LOOP NESTED LOOP MERGE SCAN HASH JOIN

1 1 1 1

R INNER S INNER

NESTED LOOP NESTED LOOP

2 2

T INNER T OUTER

76
DB
MG LEAF NODE
 Best execution plan selection

The optimizer selects the leaf with the lowest

cost
General formula
CTotal = CI/O x nI/O + Ccpu x ncpu
nI/O is the number of I/O operations
ncpu is the number of CPU operations
The selection is based on operation research
optimization techniques
e.g., branch and bound

77
DB
MG
 Best execution plan selection

The final execution plan is an approximation of

the best solution
The optimizer looks for a solution which is of the
same order of magnitude of the “best” solution
For compile and go
it stops when the time spent in searching is
comparable to the time required to execute the
current best plan

78
DB
MG