- Statistics:
- Modern DBMS often maintain a number of statistical information to help the Query Optimizer decide on the optimal query plan
- Types of
statistical information maintained:
- T(R)
(# tuples) for each relation
- V(R,a) for
each attribute
- Histogram of some important attribute values
- T(R)
(# tuples) for each relation
- Equi-width:
- Each bucket of the
histogram has the
same width (= interval length)
- Each bucket of the
histogram has the
same width (= interval length)
- Equi-height:
- Each bucket of the
histogram contains the
same height (= # data points)
- Each bucket of the
histogram contains the
same height (= # data points)
- Most frequent values:
- The most frequent values
are tabulated
An entry for the other values is listed
Example:
- The most frequent values
are tabulated
- Hybrid:
most-frequent values +
equi-width
- You can either
include or
exclude the
most frequent items
in the equi-width histogram:
- You can either
include or
exclude the
most frequent items
in the equi-width histogram:
- Fact:
- Histograms can
greatly help
improve the
estimation of the
size of the
result of a
join
- However:
- How to use the histogram information depends on the type of the histogram....
- Histograms can
greatly help
improve the
estimation of the
size of the
result of a
join
- Problem description:
R = R(a,b) S = S(b,c) Statistics on the relations: R S ======================================= T(R) = 1000 T(S) = 500 V(R,b) = 14 V(S.b) = 13
Estimate the size of R(a,b) ⋈ S(b,c) - Histogram information on
the input relations:
R.b: S.b b freq(b) b freq(b) ------------- ------------- 0 150 0 100 1 200 1 80 5 100 2 70 others 550 others 250 ^ ^ | | 14-3=11 values 13-3=10 values
- Estimate the result of
R(a,b) ⋈ S)b,c):
- Case 1: b = 0
There are 150 tuples in R with R.b = 0 There are 100 tuples in S with S.b = 0 ===> R(a,0) ⋈ S(0,c) ==> 15,000 tuples
- Case 2: b = 1
There are 200 tuples in R with R.b = 1 There are 80 tuples in S with S.b = 1 ===> R(a,1) ⋈ S(1,c) ==> 16,000 tuples
- Case 3: b = 5
There are 100 tuples in R with R.b = 1 There are 250 tuples in S with 10 other values ==> assume that b=5 is one of the other values ==> Then: avg # tuples in S with "S.b = 5" = 25 ===> R(a,5) ⋈ S(5,c) ==> 2,500 tuples
- Case 4: b = 2
There are 70 tuples in S with S.b = 2 There are 550 tuples in R with 11 other values ==> assume that b=2 is one of the other values ==> Then: avg # tuples in R with "R.b = 2" = 50 ===> R(a,2) ⋈ S(2,c) ==> 3,500 tuples
- Case 5: b = "other"
There are 550 tuples in R with 11 other values R.b = { 0, 1, 5, x, x, x, .... (11 other values) } // V(R,b) = 14 ==> Then: avg 50 tuples in R per value, y, y, y, .... (9 other values !!) } Each of these 9 other values will join in R⋈S !!! Therefore: ===> R(a,other) ⋈ S(other,c) ==> 9 × 50 × 25 tuples
There are 250 tuples in S with 10 other values S.b = { 0, 1, 2, y, y, y, .... (10 other values) } // V(S,b) = 13 ==> Then: avg 25 tuples in S per value
We continue to make the containment of value set assumption: V(S,b) ≤ V(R,b) ===> all values of S.b are in R.b Since: we have assumed that 5 ∈ R.b, we have: S.b = { 0, 1, 2, 5
- Therefore:
T(R⋈S) ~= 15,000 + 16,000 + 2,500 + 3,500 + 9×50×25 ~= 48,250 tuples
- Case 1: b = 0
- Compare: estimate
without using
histogram information
Statistics on the relations: R S ======================================= T(R) = 1000 T(S) = 500 V(R,b) = 14 V(S.b) = 13
T(R) × T(S) T(R⋈S) ~= ------------------- max(V(R,b), V(S,b)) 1000 × 500 ~= ------------- max(14, 13) = 500,000/14 ~= 35,714 48,250/35,714 = 1.35 (35% difference)
- Fact:
- Equi-width histograms are ideally (= simple) suited for estimating the result of joins
- Problem description:
JanTemp = JanTemp(day, temp) JulTemp = JulTemp(day, temp) Statistics on the relations: JanTemp JulTemp ================================================= T(JanTemp) = 245 T(JulTemp) = 245 V(JanTemp,Temp) = 100 V(JulTemp.Temp) = 100
Estimate the size of: SELECT JanTemp.day, JulTemp.day FROM JanTemp, JulTemp WHERE JanTemp.Temp = JulTemp.Temp (Days in Jan and Jul that has same temperature) - Histogram information on
the input relations:
Range JanTemp JulTemp ---------+---------+--------- 0 - 9 | 40 | 0 10 - 19 | 60 | 0 20 - 29 | 80 | 0 30 - 39 | 50 | 0 40 - 49 | 10 | 5 50 - 59 | 5 | 20 60 - 69 | 0 | 50 70 - 79 | 0 | 100 80 - 89 | 0 | 60 90 - 99 | 0 | 10
- Estimate the result of
R(a,b) ⋈ S(b,c):
- Case 1:
40 ≤ Temp ≤ 49
JanTemp: 10 tuples has values in 40, 41, 42, ...., 49 (10 values) Avg: 1 tuple with Temp = 40 1 tuple with Temp = 41 ... 1 tuple with Temp = 49 JulTemp: 5 tuples has values in 40, 41, 42, ...., 49 (10 values) Avg: 0.5 tuple with Temp = 40 0.5 tuple with Temp = 41 ... 0.5 tuple with Temp = 49 JanTemp ⋈ JulTemp: Temp = 40 ==> 1 × 0.5 = 0.5 tuple (average !) Temp = 41 ==> 1 × 0.5 = 0.5 tuple ... Temp = 49 ==> 1 × 0.5 = 0.5 tuple ------------- Total = 10 × 0.5 = 5 tuples
- Case 2:
50 ≤ Temp ≤ 59
JanTemp: 5 tuples has values in 50, 51, 52, ...., 59 (10 values) Avg: 0.5 tuple with Temp = 50 0.5 tuple with Temp = 51 ... 0.5 tuple with Temp = 59 JulTemp: 20 tuples has values in 50, 51, 52, ...., 59 (20 values) Avg: 2 tuple with Temp = 50 2 tuple with Temp = 51 ... 2 tuple with Temp = 59 JanTemp ⋈ JulTemp: Temp = 50 ==> 2 × 0.5 = 1.0 tuple (average !) Temp = 51 ==> 2 × 0.5 = 1.0 tuple ... Temp = 59 ==> 2 × 0.5 = 1.0 tuple ------------- Total = 10 × 1.0 = 10 tuples
- Case 3: other
Temp
- There are no common values in the other ranges
- Therefore:
T(JanTemp ⋈ JulTemp) ~= 5 + 10 = 15 tuples
- Case 1:
40 ≤ Temp ≤ 49
- Compare: estimate
without using
histogram information
Statistics on the relations: JanTemp JulTemp ================================================= T(JanTemp) = 245 T(JulTemp) = 245 V(JanTemp,Temp) = 100 V(JulTemp.Temp) = 100
T(R) × T(S) T(R⋈S) ~= ------------------- max(V(R,b), V(S,b)) 245 × 245 ~= ------------- max(100, 100) = 60025/100 ~= 600 600/15 = 40 (4000% error !!!)