R - Datenbanksysteme Tübingen | Home

Kapitel 8
Schätzung von Anfragekosten
• Einführung
• Berechnung von
Operatorkardinalitäten
• Histogramme
2
Architektur und Implementierung von Datenbanksystemen | WS 2009/10
Melanie Herschel | Universität Tübingen
Überblick
Architektur eines DBMS
Figure inspired by Ramakrishnan/Gehrke: “Database Management Systems”, McGraw-Hill 2003.
Web Forms
Applications
SQL Interface
SQL Commands
Executor
Operator Evaluator
Transaction
Manager
Lock Manager
Parser
!
Optimizer
File and Access Methods
Buffer Manager
Disk Space Manager
!
data, files, indices, ...
!
!
!
Recovery
Manager
DBMS
Database
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
3
Anfragebearbeitung
Grundproblem
• Anfragen sind deklarativ (SQL, relationale Algebra)
• Anfragen müssen in ausführbare (prozedurale) Form transformiert werden.
Ziele
• Prozeduraler Query Execution Plan (QEP)
• Effizienz
• Schnelle Ausführung der Anfrage
• Wenig Ressourcenverbrauch (CPU, I/O, RAM, Bandbreite)
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
4
Anfragebearbeitung
SQL Anfrage
Parsing
• Parsen der Anfrage (Syntax)
• Überprüfung der Elemente (Semantik)
Parsing
• Parserbaum
Wahl des logischen Anfrageplans
• Baum mit logischen Operatoren
• Potentiell exponentiell viele
• Wahl des optimalen Plans (siehe Kapitel 9)
• Logische Optimierung
Wahl des logischen
Anfrageplans
• Regelbasierte Optimierung
• Kostenbasierte Optimierung
Wahl des physischen Anfrageplans
• Ausführbar
• Programm mit physischen Operatoren (siehe Kapitel 6 und 7)
Wahl des physischen
Anfrageplans
• Wahl des optimalen Plans
• physische Optimierung
Anfrageplan ausführen
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
5
Kostenmodell
DB-Kardinalitäten
Algebraischer
Ausdruck
Attributverteilungen
Kostenmodell
Index-Informationen
Ausführungskosten
Ballungsinformationen
(Clustering)
Folie nach Prof. Alfons Kemper, TU München
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
6
Kapitel 8
Schätzung von Anfragekosten
• Einführung
• Berechnung von
Operatorkardinalitäten
• Histogramme
7
Architektur und Implementierung von Datenbanksystemen | WS 2009/10
Melanie Herschel | Universität Tübingen
Schätzung der Kardinalität
• Die Kardinalität einer Anfrage entspricht der Größe der Ausgabe der Anfrage.
• Die Kardinalität eines Operators entspricht der Größe der Ausgabe des
Operators.
• Die Kardinalität wird typischerweise in Anzahl Seiten bzw. Anzahl Tupel
angegeben.
• Die Selektivität einer Anfrage bzw. eines Operators ist
sel = Anzahl Ausgabetupel / Anzahl Eingabetupel
• Die exakte Bestimmung von Kardinalitäten bedarf der Ausführung der Anfrage
bzw. des Operators
" Schätzung der Kardinalität zur Anfrageoptimierung
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
8
Schätzung der Kardinalität
Zwei Varianten der Kardinalitätenschätzung:
1. Datenbankprofile
• Speichere Statistiken für Basisrelationen im database catalog, insb. Anzahl und
Größe von Tupeln und Werteverteilungen von Attributen.
• Berechne diese Statistiken für Zwischenergebnisse einer Anfrage (Kardinalität der
Operatoren der Anfrage) anhand eines statistischen Modells während der
Anfrageoptimierung.
• Typischerweise basieren statistische Modelle auf vereinfachenden Annahmen wie die
Datenunabhängigkeit und Gleichverteilung der Daten.
• Diese gelten oft nicht in realen Daten, deswegen sind die Schätzungen oft falsch.
Um ein genaueres Ergebnis zu erzielen, werden Histogramme verwendet.
2. Sampling
• Sammeln relevanter Statistiken während der Anfrageausführung auf einem Sample
der Eingabedaten.
• Extrapolieren der Ergebnisse auf Gesamteingabe.
• Es ist wichtig, die richtige Balance zwischen der Stichprobengröße und der Qualität
der Schätzung zu finden.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
9
Datenbankprofil
•Im Datenbankkatalog werden Profile gespeichert und bei SQL DML
Befehlen (Datenbank-Updates) aktualisiert.
•Daten, die in einem Datenbankprofil typischerweise gespeichert sind:
|R|
Anzahl Tupel in Relation R
NR
Anzahl Seiten auf Platte die Tupel von R enthalten
s(R)
durschnittliche Tupellänge
V(A, R)
...
Anzahl verschiedener, sog. Distinct-Werte in Attribut A
...
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
10
Datenbankprofil
Excerpt of IBM DB2 catalog information for a TPC-H database
1
2
3
4
5
6
7
8
9
10
11
12
13
14
db2 => SELECT TABNAME, CARD, NPAGES
db2 (cont.) => FROM SYSCAT.TABLES
db2 (cont.) => WHERE TABSCHEMA = ’TPCH’;
TABNAME
CARD
NPAGES
-------------- -------------------- -------------------ORDERS
1500000
44331
CUSTOMER
150000
6747
NATION
25
2
REGION
5
1
PART
200000
7578
SUPPLIER
10000
406
PARTSUPP
800000
31679
LINEITEM
6001215
207888
8 record(s) selected.
CARD = |R|, NPAGES = NR
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
11
Annahmen des Statistischen Modells
Gleichverteilung und Unabhängigkeit (einfach, doch selten realistisch)
• Alle Werte eines Attributs erscheinen mit der gleichen Wahrscheinlichkeit
(Gleichverteilung, engl. uniformity). Werte verschiedener Attribute sind unabhängig
voneinander (Unabhängigkeit, engl. independence).
• Gegenbeispiele:
• Nachname Müller erscheint mit höherer Wahrscheinlichkeit als Nachname Blabla.
• Attribut PLZ ist nicht unabhängig von Attribut Stadt.
Worst Case (unrealistisch)
• Es liegen keine Informationen über den Inhalt von Relationen vor.
• In diesem Fall wird z.B. im Fall einer Selektion nach Prädikat P angenommen, dass
alle Tupel P entsprechen.
Allwissendes Orakel (unrealistisch)
• Es liegen exakte Informationen über Werteverteilungen vor.
• Benötigt einen sehr großen Katalog oder genaues Wissen über eingehende
Anfragen.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
12
Schätzung der Kardinalität Relationaler Operatoren
Selektion Cardinality
(mit Gleichheitsbedingung)
Estimation for σ (Equality Predicate)
Query: Q ≡ σA=c (R)
Selectivity sel(A = c)
1/V(A, R)
!
Uniformity
Cardinality |Q|
sel(A = c) · |R|
Record size s(Q)
s(R)
Value Distribution V(A , Q)
!
1,
c(V(A, R), |Q|),
!
Cardinality Estimation
Torsten Grust
Cardinality Estimation
Database Profiles
Assumptions
for A! = A,
otherwise.
with (# of distinct colors obtained by drawing r balls from a bag
of balls of m colors):


for r < m/2,
r,
c(m, r) = (r + m)/3, for m/2 ! r < 2m,


m,
for r " 2m
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
Histograms
Equi-Width
Equi-Depth
Statistical Views
13
9.9
Selectivity Estimation for σ (Other Predicates)
Torsten Grust
• Equality between attributes (Q ≡ σA=B (R)):
Approximate selectivity by
Selectivity
Estimation forder
σ (Other
Predicates) Relationaler Operatoren
Schätzung
Kardinalität
Selectivity Estimation for σ (Other Predicates)
Selektion (andere sel(A
Prädikate)
= B) (Q =
1/ (R)):
max(V(A, R), V(B, R)) .
• Equality
between attributes
≡ σA=B
Cardinality Estimation
Torsten Grust
Equality between attributes (Q ≡ σA=B (R)):
Selectivity
Estimation
for σby
(Other• Predicates)
Approximate
selectivity
Cardin
T
Cardinality Estimation
Approximate
selectivity with
by
Selectivity
Estimation
for σvalue
(Other
(Assumes
that each
of Predicates)
the attribute
fewer Torsten Grust Database Profiles
Torsten Grust
• Gleichheit
Equalitydistinct
between
attributes
(Q ≡ σA=B (R)):
zwischen
Attributen
Assumptions
values
has
a
corresponding
!
sel(A
=
B)
=
1/
max(V(A,
R),
V(B,
R))
.
sel(A
=
B)
=
1/
max(V(A,
R),
V(B,
R))
.
•
Equalityselectivity
betweenbyattributes (Q ≡ σA=B (R)):
Approximate
• Nimmt
an,
dass
jeder
Wert
des Attributs
mit weniger
Distinct-Werten
einem Estimating Operator Cardi
Selectivity
Estimation
for σ (Other
Predicates)
match
in
the
other
attribute.)
Independence
Cardinality Estimation Cardinality
Cardin
Approximate
selectivity
by
Wert
des
anderen
Attributs
entspricht.
(Assumes that
each value of the attribute
with fewer Selection σ
(Assumes
each
the
attribute
with
Datab
Database
Equality
between
attributes
(Q ≡ σA=B
(R)):Profiles
• that
sel(A
=selections
B)value
= of 1/
R),
V(B,fewer
R))
.
Range
(Q•max(V(A,
=
σ
(R)):
A>c
Projection π
distinct values has a corresponding
!
Assumptions
distinct values
has
a
corresponding
!
Approximate
selectivity
by
Set Operations ∪, \, ×
sel(A
=
B)
=
1/
max(V(A,
R),
V(B,
R))
.
In the database profile,match
maintain
the minimum
Estima
Estimation
in the other
attribute.) and Cardinality
Independence
Estimating
Operator Join "
!
Cardin
(Assumes
that
each
value
of
the
attribute
with
fewer
match
in the
other
attribute.)
Independence
Database
Profiles
Cardinality
maximum value of attribute
A in =
relation
R,A>c
Low(A,
R) and
Cardinality Estimation
•
Range
selections
(Q
=
σ
(R)):
sel(A
B)
=
1/
max(V(A,
R),
V(B,
R))
.Histograms
Selection
σ
distinct
values
has
a
corresponding
!
(Assumes
that
each
value
of
the
attribute
with
fewer
Range
selections
(Q
=
σ
(R)):
Bereichsanfrage
Database
Profiles
A>c
High(A, R).
Projection π
In the database profile, maintain the Estimating
minimum
and Equi-Width
Operator
Cardi
"
!
match
in
the
other
attribute.)
Independence
Equi-Depth
Assumptions
!
distinct
values
has
a
corresponding
Set
Operations
∪,
\,
×
Cardinality
!
(Assumes
that
each
value
of
the
attribute
with
fewer
In the•database
profile,
maintain
the
minimum
and
maximum
value
of
attribute
A
in
relation
R,
Low(A,
R)
and
Im Datenbankprofil wird der minimale Wert Low(A, R) und maximale
Datab
Histog
Join "
!σ Wert
Estimating
Operator
Statistical
Views
Approximate
selectivity
by
Uniformity
•
Range
selections
(Q
= σA>c
match
in von
the
other
attribute.)
Independence
values
has a corresponding
High(A,
R).
!
π
maximum
value
of attribute
relation
Low(A,
R) and
Cardinality
High(A,
R)
Attribut
AA(R)):
inindistinct
Relation
R R,
gespeichert.
Histograms
!
Estim
∪, \,Selection
×
σ
in
the other and
attribute.)
Independence
In the
database
profile, maintain
the
minimum
Equi-Width
Cardi
• R).
High(A,
Range
selections
(Q = σmatch
(R)):
Statist
"
!
A>c
Approximate
selectivity by
Uniformity
Projection π
sel(A
>
c)
=
Equi-Depth
! Histograms Set Operations ∪, \, ×
maximum
of attribute
in relation
R, Low(A,
and
Range
selections
(Q =R)σA>c
(R)):
In thevalue
database
profile,•Amaintain
the
minimum
and
Statistical ViewsJoin "
!
Approximate
Uniformity
High(A, R). selectivity by
In sel(A
the database
maintain the minimum
and
> c) = profile,
"
!
maximum
A in relation R, Low(A,
R) and
 value of attribute
!
Histograms
maximum
value of attribute A in relation R, Low(A, R) and
High(A,
R) −
c 
Histo
selectivity
Statistical Views Equi-Width
Approximate
by
Uniformity
High(A,
R).
,
Low(A,
R)
!
c
!
High(A,
R)
sel(A > c) =
High(A, R).
Equi-Depth
High(A, R) − Low(A,R) High(A, R) − c
, Low(A,!
R) ! c ! High(A, R)!
Statistical Views
High(A,selectivity
R) − Low(A,
R)
Approximate
Statis
Approximate
byUniformity
Uniformity
sel(A
> c) 
= 0, selectivity by
otherwise


0,
otherwise
High(A, R) − c

, Low(A,
R)c)!=c ! High(A, R)
9.10
 sel(A > c) =
sel(A >
High(A,High(A,
R) − Low(A,
R) − c R)
14

,von Datenbanksystemen
Low(A,
R) !
c2009/10
! High(A,
R) | Universität Tübingen

Architektur
und
Implementierung
|
WS
|
Melanie
Herschel
0, High(A,
otherwise
 R) − Low(A, R)
High(A, R) − c


High(A, R) − c
Cardinality
Estimation
Cardinality
Estimation
Assum
•
Selecti
Assumptions
Project
Set Op
Join
Selection
Projection
Set Operations
Assum
Equi-W
Equi-D
Join
Select
Projec
Equi-Width
Set Op
Equi-Depth
Join
Equi-W
Equi-D
Cardinality Esti
Cardinality Estimation for π
Torsten Gr
Schätzung der Kardinalität Relationaler Operatoren
• For Q ≡ πL (R), estimating the number of result rows is
Projektion
difficult (L = "A1 , A2 , . . . , An #: list of projection attributes):
Q ≡ πL (R)
Cardinality |Q|
Record size s(Q)
Val. Dist. V(Ai , Q)
Cardinality Estim

V(A, R),



|R|,

|R|,


%
'
&

min |R|, Ai ∈L V(Ai , R) ,
if L = "A#
if keys of R ∈ L
no dup. elim.
otherwise
!
Independence
(
Ai ∈L
Database Profile
Assumptions
Estimating Ope
Cardinality
Selection σ
Projection π
Set Operations ∪,
Join "
!
Histograms
Equi-Width
Equi-Depth
Statistical Views
s(Ai )
V(Ai , R) for Ai ∈ L
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
15
Schätzung der Kardinalität Relationaler Operatoren
Mengenoperatoren
Cardinality Estimation
Cardinality Estimation for ∪, \, ×
Torsten Grust
Q≡R∪S
|Q|
s(Q)
V(A, Q)
!
=
!
|R| + |S|
s(R) = s(S)
schemas of R,S identical
V(A, R) + V(A, S)
Cardinality Estimation
Database Profiles
Assumptions
Q≡R\S
max(0, |R| −| S|) ! |Q|
s(Q)
V(A, Q)
!
=
!
|R|
s(R) = s(S)
V(A, R)
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
Histograms
Equi-Width
Equi-Depth
Statistical Views
Q≡R×S
|Q|
s(Q)
=
=
V(A, Q)
=
|R| · |S|
s(R)
! + s(S)
V(A, R), for A ∈ R
V(A, S), for A ∈ S
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
9.12
16
•
A special, yet very common case: foreign-key relationship
between input relations R and S:
Cardinality Estimation
key relationship (SQL)
SchätzungEstablish
dera foreign
Kardinalität
Relationaler Operatoren
Database Profiles
Assumptions
Join
•
•
Estimating Operator
CREATE TABLE R (A INTEGER NOT NULL,
Cardinality
2
...
Selection σ
3
PRIMARY KEY (A));
Projection π
Set Operations ∪, \, ×
4 CREATE TABLE S (...,
Join "
!
Im Allgemeinen
Fall
ist
es
sehr
schwierig,
die
Kardinalität
eines
Joins
abzuschätzen.
5
A INTEGER NOT NULL,
Histograms
6
...
Equi-Width
Ein Spezialfall,7 der häufig auftritt
istKEY
der(A)Join
zwischen
und
FOREIGN
REFERENCES
R); einem Primärschlüssel
Equi-Depth
1
einem entsprechenden Fremdschlüssel.
Statistical Views
Q≡R!
"R.A=S.A S
The foreign key constraint guarantees πA (S) ⊆ πA (R). Thus:
Cardinality Estimation for !
"
|Q| = |S| .
Cardinality Estimation
Torsten Grust
• Ist ein Attribut nicht Unique, aber das andere Attribut dennoch eine Untermenge:
Q≡R!
"R.A=S.B S
9.13
|Q| =

|R| · |S|


 V(A, R) , πB (S) ⊆ πA (R)


 |R| · |S| ,
V(B, S)
πA (R) ⊆ πB (S)
s(Q) = s(R) + s(S)
Cardinality Estimation
Database Profiles
Assumptions
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
Histograms
Equi-Width
Equi-Depth
%
V(A! , R), if A! attribute in R
!
V(A , Q) !
V(A! , S), otherwise
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
Statistical Views
17
Kapitel 8
Schätzung von Anfragekosten
• Einführung
• Berechnung von
Operatorkardinalitäten
• Histogramme
18
Architektur und Implementierung von Datenbanksystemen | WS 2009/10
Melanie Herschel | Universität Tübingen
Histogramme
• In realistischen Datenbankinstanzen sind die Werte der
aktiven Domäne eines Attributs (Werte, die tatsächlich
gespeichert sind) nicht gleichmäßig verteilt.
• Um die tatsächliche Verteilung von Attributwerten besser
zu reflektieren, wird diese durch Histogramme
approximiert.
• Die aktive Domäne von Attribut A wird in adjazente
Intervalle geteilt, indem Grenzwerte (boundary
values) bi gewählt werden.
• Für jedes Intervall zwischen zwei Genzwerten werden
Statistiken gesammelt, z.B.,
• Anzahl Tupel mit bi-1 < r.A <= bi oder
• Anzahl Distinct-Werte von A im Intervall (bi-1, bi].
• Die Intervalle eines Histogramms werden auch
Buckets genannt.
Weitherführende Literatur
Yannis Ioannidis: The History of Histograms (Abridged), Proceedings
of the Conference on Very Large Data Bases (VLDB), 2003, Berlin
Histogram maintained for a
column in a TPC-H database
SELECT
FROM
WHERE
AND
AND
SEQNO
----1
2
3
4
5
6
7
8
9
10
11
12
13
SEQNO, COLVALUE, VALCOUNT
SYSCAT.COLDIST
TABNAME = ’LINEITEM’
COLNAME = ’L_EXTENDEDPRICE’
TYPE = ’Q’;
COLVALUE
VALCOUNT
----------------- -------+0000000000996.01
3001
+0000000004513.26
315064
+0000000007367.60
633128
+0000000011861.82
948192
+0000000015921.28 1263256
+0000000019922.76 1578320
+0000000024103.20 1896384
+0000000027733.58 2211448
+0000000031961.80 2526512
+0000000035584.72 2841576
+0000000039772.92 3159640
+0000000043395.75 3474704
+0000000047013.98 3789768
.
.
.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
19
Histogramme
Zwei Typen von Histogrammen sind weit verbreitet:
1. Equi-Width Histogramme
• Alle Buckets haben die gleiche Breite, d.h., Grenzwerte werden wie folgt gewählt
bi = bi-1 + w, für eine Konstante w
2. Equi-Depth Histogramme
• Alle Buckets enthalten die selbe Anzahl Tupel, d.h., ihre Breite ist variabel.
Die Anzahl Buckets ist die Stellschraube, die den Tradeoff zwischen der Qualität der
Kardinalitätsschätzung (histogram resolution) und die Größe (und daher den
Speicherbedarf) eines Histogramms definiert.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
20
Cardinality Estimation
Equi-Width Histograms
Equi-Width Histogramme
Torsten Grust
Beispiel
Example (Actual value distribution)
In Spalte A vom SQL Typ INTEGER (Domäne {..., -2, -1, 0, 1, 2, 3, ...}) beobachten wir
Column A of SQL type INTEGER (domain {. . . , -2, -1, 0, 1, 2, . . . }).
die folgende reale Verteilung von Werten in einer Relation R.
Actual non-uniform distribution in relation R:
8
7
6
5
4
3
2
2
1
2
3
0
4
5
Assumptions
Histograms
3
2
1
1
3
Database Profiles
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
9
8
Cardinality Estimation
Equi-Width
Equi-Depth
Statistical Views
6
7
8
9
10
11
12
13
14
15
16
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
21
9.18
Equi-Width Histograms
Equi-Width
Histogramme
Torsten Grust
Cardinality Estimation
• DivideHistograms
Equi-Width
active domain of attribute A into B buckets of equal
Torsten Grust
width.
The bucket
width
Wir teilen die
aktive active
Domäne
von Attribut
Aw
in will
B Buckets
w. Die Bucket• Divide
domain
of attribute
A be
into B gleicher
bucketsBreite
of equal
Breite w wirdwidth.
berechnet
durch width
The bucket
w willR)be− Low(A, R) + 1
High(A,
w=
High(A, R) − Low(A,
B R) + 1
w=
Cardinality Estimation
B
Example
histogram (B = 4))
Beispiel
(B = (Equi-width
4)
Cardinality Estimation
Cardinality Estimation
Database Profiles
Example (Equi-width histogram (B = 4))
Database Profiles
Assumptions
Assumptions
27
27
19
19
8
6
5 5
2
1
1
2
2
9
1
1
2
2
3
3
1
0
4
05
4
8
7
7
4
3
3
3
Equi-Width
Equi-Width
Equi-Depth
Equi-Depth
Statistical Views
Statistical Views
5
6
4
2
88
Histograms Histograms
13
13
9
Estimating Operator
Estimating Operator
Cardinality
Cardinality
Selection σ
Selection σ
Projection π
Projection π
Set Operations ∪, \, ×
Set Operations ∪, \, ×
Join "
!
Join "
!
5
3
3
2
3
2
1
5
6
7
6
8
7
9
8
10
9
11
10
12
11
13
12
14
13
15
16
14
15
16
Zusätzlich zu den Grenzwerten speichern wir die Summe der Wertehäufigkeiten.
•
•
Maintain sum of value frequencies in each bucket (in
Architektur und Implementierung
von Datenbanksystemen
Herschel
| Universität(in
Tübingen
Maintain
of boundaries
value
frequencies
each
bucket
addition
tosum
bucket
bi ).| WS 2009/10in| Melanie
22
9.19
Equi-Width Histograms
• Divide active domain of attribute A into B buckets of equal
width. The bucket width w will be
High(A, R) − Low(A, R) + 1
Selektion mit Gleichheitsbedingung
w=
B
Cardinality Estimation
Torsten Grust
Equi-Width Histogramme
Cardinality Estimation
Beispiel (Q
! !A=5(R)
)
Example
(Equi-width
histogram (B = 4))
Database Profiles
Assumptions
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
27
19
Histograms
13
Equi-Width
9
8
Equi-Depth
8
7
Statistical Views
6
5
5
4
3
2
3
2
1
1
3
2
1
2
3
0
4
5
6
7
8
9
10
11
12
13
14
15
16
Wert 5 ist in Bucket [5, 8] (mit 19 Tupeln)
Unter der Annahme
einersum
Gleichverteilung
von Werten innerhalb
eines Buckets
Maintain
of value frequencies
in each bucket
(in haben wir
= 19 / B = 19
addition to bucket|Q|boundaries
b )./ 4 ! 5
•
i
9.19
Tatsächlicher ist |Q| = 1. Welchen Schätzwert erhalten wir ohne Histogramm?
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
23
Equi-Width Histograms
• Divide active domain of attribute A into B buckets of equal
width. The bucket width w will be
High(A, R) − Low(A, R) + 1
Selektion mit Bereichsprädikat
w=
B
Cardinality Estimation
Torsten Grust
Equi-Width Histogramme
Cardinality Estimation
Beispiel (Q
! !A>7 AND
A <= 16 (R) )
Example
(Equi-width
histogram (B = 4))
Database Profiles
Assumptions
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
27
19
Histograms
13
Equi-Width
9
8
Equi-Depth
8
7
Statistical Views
6
5
5
4
3
2
3
2
1
1
3
2
1
2
3
0
4
5
6
7
8
9
10
11
12
13
14
15
16
Anfrageintervall schließt Buckets [9, 12] und [13, 16] ein.
AnfrageintervallMaintain
umfasst einen
Teilvalue
von Bucket
[5, 8]
sum of
frequencies
in each bucket (in
|Q|
=
27
+
13
+
19
/
4
!
45
addition to bucket boundaries b ).
•
i
9.19
Tatsächlicher ist |Q| = 48. Welchen Schätzwert erhalten wir ohne Histogramm?
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
24
Equi-Width Histogramme
Initiallisierung und Aktualisierung
Initialisierung eines Equi-Width Histogramm für ein Attribut A der Relation R mit B Buckets
1. Berechne Grenzwerte bi zwischen High(A,R) und Low(A, R).
2. Scanne R sequentiell.
3. Während des Scans werden B Tupel-Häufigkeits-Counter (einen für jeden Bucket)
verwaltet. Ein Counter wird inkrementiert, wenn ein Tupel des entsprechenden Buckets
gelesen wird.
• Wenn R (z.B. aufgrund seiner Größe) nicht gescannt werden kann, scannen wir eine
Stichprobe RSample aus R und skalieren die Counter durch |R| / |RSample|
Aktualisierung
• Werden Tupel gelöscht, werden einfach die entsprechenden Counter dekrementiert.
• Werden Tupel hinzugefügt, und fallen diese in einen existierenden Bucket (Wert
zwischen High(A, R) und Low(A, R) ) so inkrementieren wir die entsprechenden
Counter. Ansonsten ist prinzipiell eine Neubestimmung der Bucketgrenzen nötig.
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
25
Cardinality Estima
Equi-Depth Histograms
Torsten Grust
• Divide active
domain of attribute A into B buckets of
Equi-Depth
Histogramme
number of tuples in each bucket, depth d
of each
bucket
will
be
• Divide
active
domain
of attribute
A into B buckets of
Equi-Depth
Histograms
roughly
the same
Cardinality Estimation
Torsten Grust
Die aktive Domäne
eines
Attributs
A wird
in B Buckets
unterteilt,
wobei
roughly the
same
number
of tuples
in each bucket,
depth
d jeder Bucket
|R|Tupel in eine Bucket, die Tiefe d, wird
(ungefähr) die
selbebucket
Anzahlwill
Tupel
of each
be enthält. Die Anzahl
d=
.
berechnet als
B
|R|
Cardinality Estimation
d=
.
B
Database Profiles
Example (Equi-depth histogram (B = 4, d = 16))
Assumptions
Beispiel
B = 4, d = histogram
16
Examplemit
(Equi-depth
(B = 4, d = 16))
16
16
16
16
16
16
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
16
16
8
8
9
87
8
4
2
1
1
•
•
6
2
2
12
3
1
2
3
4
0
4
2
3
3
3
5
6
0
4
17
8
5
6
9
10
7
11
8
Equi-Width
Equi-Depth
12
13
9
10
Statistical Views
5
3
1
14
11
15
Estimating Operat
Cardinality
Selection σ
Projection π
Set Operations ∪, \,
Join "
!
Equi-Depth
7
5
Assumptions
Equi-Width
Statistical Views
6
Database Profiles
Histograms
Histograms
9
Cardinality Estima
2
3
3
2
16
12
13
14
15
16
Maintain depth (and bucket boundaries bi ).
Maintain depth (and bucket boundaries bi ).
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
9.23
26
Equi-Depth Histogramme
Equi-Width vs. Equi-Depth Histograms
Example (Histogram on customer age attribute (B = 8, |R| = 5,600))
• Intuitiv werden Werte mit hoher Häufigkeit als wichtiger gewertet als Werte mit
geringer Häufigkeit.
1300
• Die Auflösung des Histograms passt sich somit Daten 1100
an, die starke
1000
Unterschiede in den Häufigkeiten aufweisen.
800
Cardinality Estimation
Width vs. Equi-Depth
Histograms
• Equi-Depth
Histogramme “investieren” Bytes in dichte Datenregionen.
600
Torsten Grust
500
Beispielon
Histogramme
fürattribute
Kundenalter
8, |R|
5600)
ple (Histogram
customer age
(B =(B8,= |R|
==
5,600))
200
100
1300
0-10
1100
10-20
20-30
30-40
40-50
50-60
60-70
70-80
1000
800
700
700
600
200
100
10-20
20-30
30-40
40-50
50-60
60-70
0-20
70-80
Equi-Width Histogramm
700
700
700
700
700
700
700
700
700
Assumptions
500
0-10
Cardinality Estimation
700
700
700 700
Database Profiles
•
21-29
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
30-36 37-41 44-48 49-52 55-59
Join "
!
60-80
Equi-Depth
Histogramm
Histograms
700
Equi-Width
Equi-depth histogram “invests” bytes in the densely
Statistical
Views
age
region
between
30 and 59.27
Architektur und Implementierung von populated
Datenbanksystemen |customer
WS 2009/10 | Melanie
Herschel
| Universität
Tübingen
Equi-Depth
Equi-Width Histogramme
Selektion mit Gleichheitsbedingung
Equi-Depth Histograms: Equality Selections
Cardinality Estimation
Torsten Grust
Example (Q ≡ σA=5 (R))
Beispiel (Q ! !A=5(R) )
16
16
16
16
Cardinality Estimation
Database Profiles
9
8
Assumptions
8
7
6
5
4
3
2
3
2
2
1
1
3
1
2
3
0
4
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
Histograms
5
6
7
8
9
10
11
12
13
14
15
16
Equi-Width
Equi-Depth
Wert 5 ist in Bucket [1, 7] (mit d = 16 Tupeln)
Value 5 einer
is inGleichverteilung
first bucket [1,
7]Werten
(withinnerhalb
d = 16 eines
tuples)
Unter der Annahme
von
Buckets haben wir
|Q| = d / 7 = 16 / 7 ! 2
•
•
Statistical Views
Assume uniform distribution within the bucket:
|Q| = d/7 = 16/7 ≈ 2 .
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
(Actual: |Q| = 1)
28
Equi-Depth Histogramme
Selektion
mit Bereichsprädikat
Equi-Depth
Histograms: Equality Selections
Cardinality Estimation
Torsten Grust
Example (Q ≡ σA=5 (R))
Beispiel (Q ! !A>5 AND A <= 16 (R) )
16
16
16
16
Cardinality Estimation
Database Profiles
9
8
Assumptions
8
7
6
5
4
3
2
3
2
1
1
3
2
1
2
3
0
4
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
Histograms
5
6
7
8
9
10
11
12
13
14
15
16
Equi-Width
Equi-Depth
Anfrageintervall schliesst Buckets [8, 9], [10, 11] und [12, 16] ein.
Valueumfasst
5 is ineinen
firstTeil
bucket
[1, 7][1,(with
d = 16 tuples)
Anfrageintervall
von Bucket
7]
= 16 + 16 + 16 within
+ 2 / 7 ! the
53 bucket:
Assume uniform|Q|distribution
•
•
Statistical Views
(Der exakte Wert beträgt |Q| = 59).
|Q| = d/7 = 16/7 ≈ 2 .
(Actual:
|Q| = 1)
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
29
Equi-Depth Histograms:
Construction
Equi-Depth
Histogramme
Cardinality Estimation
Torsten Grust
Initiallisierung
•
To construct an equi-depth histogram for relation R,
Initialisierung eines Equi-Depth Histogramms für ein Attribut A der Relation R mit B
attribute A:
Buckets
|R|/B.
1 Compute
1. Berechne Tiefe
d = |R| / B.depth d =
2 Sort R by sort criterion A.
2. Sortiere R nach
A.
3 b = Low(A, R), then determine the bi by dividing the
3. Sei b0 = Low(A,0R). Bestimme Grenzwerte bi indem das sortierte
Feld A in Buckets der
sorted
R
into
chunks
of
size
d.
Größe d geteilt werden.
Example
=4,4,|R||R|
=
Beispiel
mit(B
B=
= 64
1
d = 64/4 = 16.
2
Sorted R.A:
64)
Database Profiles
Assumptions
Estimating Operator
Cardinality
Selection σ
Projection π
Set Operations ∪, \, ×
Join "
!
Histograms
Equi-Width
Equi-Depth
!1,2,2,3,3,5,6,6,6,6,6,6,7,7,7,7,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,10,10,. . . "
3
Cardinality Estimation
Statistical Views
Boundaries of d-sized chunks in sorted R:
!1,2,2,3,3,5,6,6,6,6,6,6,7,7,7,7,8,8,8,8,8,8,8,8,9,9,9,9,9,9,9,9,10,10,. . . "
{z
} |
{z
}
|
b1 =7
b2 =9
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
30
Zusammenfassung
Anfragebearbeitung
• Um einen optimalen Anfrageplan unter allen möglichen zu finden, bewertet der Optimierer
Pläne anhand eines komplexen Kostenmodells.
• Ein wichtiger Bestandteil dieses Kostenmodells sind die I/O Kosten, die durch die Menge
an Daten (Seiten oder Tupel), die den Plan “durchlaufen”, approximiert wird.
Kardinalität einer Anfrabe bzw. eines Operators
• Die Kardinalität entspricht der Größe der Ausgabe einer Anfrage bzw. eines Operators.
• Zur Schätzung der Kardinalität sammelt ein DBMS Statistiken über die Basisrelationen.
• Annahmen wie die Gleichverteilung und Unabhängigkeit von Daten machen eine
Schätzung der Kardinalität möglich, sind jedoch oft fern der Realität.
Histogramme
• Um potentiell eine bessere Schätzung zu erzielen, werden Histogramme
für gewisse Daten angelegt.
• Zwei Histogramm-Typen: Equi-Depth- und Equi-Width-Histogramme
Architektur und Implementierung von Datenbanksystemen | WS 2009/10 | Melanie Herschel | Universität Tübingen
31