Slideshow:
Statistical information maintained by a DBMS
In the following 2 webpages, we study how to use histograms
Type of histograms: (1) equi-width
Comment: this type is quite common
Type of histograms: (2) equi-height
Type of histograms: (3) most-frequent values
Type of histograms: (*) hybrid
- Example
Why use histograms ?
Better estimate for size of result sets:
- 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
- Histograms on 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