Selftuning Histograms Building Histograms Without Looking at Data

Self-tuning Histograms Building Histograms Without Looking at Data By Ashraf Aboulnage & Surajit Chaudhuri Represents By Miller Ofer 1

Traditional histograms • Histograms impose little cost at queries , especially in a large data base. • The cost of building histograms from database is significant high and prevent us from building useful histograms. • Data modification causes to lost of histogram accuracy. 2

Self-Tuning histogram : • • Similar in structure to traditional histogram. Builds without looking or sampling the data. Uses “free” information from the queries results. Can be refined in an on-line mode ( The overall cost of building self tuning histogram is very low). • Their accuracy depends on how often they are used, the more it is use , the more it is refined , the more accurate it becomes 3

The main steps in building the ST-histogram : 1. Initial the histogram. 2. Refining the bucket values (frequencies). 3. Restructuring – moving the buckets boundaries. 4

Initial The Histogram : • Data requirement : – – – • B Number of histogram buckets T Number of tuples Min/max min and max values of attribute. Assuming uniformity of the data distribution and initial each of the buckets as T/b tuples. 5

ST-histogram after initialization tuples frequency buckets 6

The Algorithm for refining the buckets frequencies. (second step ) begin 1. Finds set of k buckets that overlapping the selection range. 2. Let est be the estimated results size of the selection range using histogram h. 3. Let act be the actual result size. 4. Compute the estimation error by act-est , denote by esterr. • 7

last overlap bucket 5. for i = 1 to k do 6. 7. 8. endfor proportion end Update. Freq average assumption 8

Refinement example : act = 60 rangehigh = 25 Rangelow = 12. 5 high(b 1) = 15 low(b 1) = 5 35 0. 5 5/25 9

Restructuring Algorithm : • Motivation : – Frequencies are approximated by their average. Thus , high frequent value will be contain in high frequent buckets , but they may be grouped with low frequency values. – When the range of query adapts to the range of histogram bucket , no average assumption is needed. 10

Restructuring Algorithm : Merging step 1. 2. 3. For every consecutive runs of buckets , find the maximum differences in frequency between a bucket in the first run and the buckets in the second run. Find the minimum of all these maximum difference , denote by mindiff. if mindiff < m*T then Merge the two runs of buckets corresponding to mindiff into one run. Look for other runs to merge. goto line 1. endif 11

Restructuring Algorithm : Splitting step 4. k=s*b ; b – the rest of the buckets that haven’t been chosen. 5. Find the set 6. Compute the splitting extra bucket of each one by : 7. split(bi) = with k highest frequency. where totalfreq is the sum of all the bucket to be split and B is the number of extra bucks 8. Each buckets freq gets the old freq divided to split(bi)+1. 12

Restructuring example : m*T 3 S*B 2 merge 1 10 13 1 2 23 1 merge 2 split 17 14 13 11 25 70 10 30 3 4 5 6 7 8 9 10 23 23 24 17 38 25 2 3 4 5 6 7 10 8 15 15 9 10 13

Multi-dimensional ST-histogram • Initialization : – Assuming a complete uniformity and independence. – Using existing one-dimensional ST-histograms assuming independence of the attribute. 14

Multi-dimensional Refining the buckets frequencies : • The refining algorithm for multi-dimensional is identical to the algorithm for one-dimensional except the two following changes : 1. Finding the overlap selected range, now require examining a multi-dimensional structure. 2. The fraction of a bucket overlapping the selection range is now equal to the volume of the region divided by the volume of the region represented by the whole bucket. 15

Multi-dimensional Restructuring • Merge – find the max difference in freq between any two corresponding buckets of the same line, merge if the difference within m*t. ( m<1% ). • Split – the frequency of partition ji in dimension i is compute by : [1, 10] [11, 20] [1, 5] [6, 10] [11, 15] [16, 20] [21, 25] 10 5 8 20 7 14 9 7 19 11 Max S = 50 Max diff = 4 Max S = 60

Accuracy of ST-histogram Due to the same memory limit and the complex of MHIST-2, st-histogram have more buckets. 17

Adapting to Database Updates R 1 – relation before update with skew =1. R 2 - Update the relation by deleting 25% of it’s tuple and inserting an equal number of tuples. 18

Accuracy dependence of the frequent queries. 19

Conclusions : • Better than assuming uniformity and independence for all values of data skew (z). • For low data skew the st-histograms found to be sufficient accurate comparing to the traditional histograms. • Attractive for multi-dimensional histogram since the high cost of building them. • For high data skew, st-hist much less accurate than “Mhist -2”. • Combination between traditional hist and st-hist for all the range of data skew can yield the best of each concept. 20