4
History of Histogram Equi-width histogram Compressed histogram Learn more on Histogram

5
precomputed on underlying data Stored in main memory, less overhead What about the maintenance ??  Database is modified  Query is changed(?)  Outdated histogram  Does periodic updates solve the problem? Recomputing from the scratch Poor estimation during the in-between period What’s the solution ? Issues on Histogram Maintenance

8
During insertions  Reservoir sampling technique  Obtain sample of data from a single scan without a priori knowledge of no of tuples.  Length of random skip chosen such that each tuple is likely to be in the reservoir. 1 2 n First n n+1 Skip random no of record and replace How to maintain a backing sample? MaintainBackingSample

9
During modification  Modify if tuple present in sample During Deletion  Remove from the sample  If sample size decrease below lower bound L, then recompute from disk. How to maintain a backing sample?

11
Compressed (V,F) histogram  N highest frequencies stored in singleton buckets  For other values, use equi-depth histogram Both histograms needs to store for each bucket  The largest value in the bucket B.maxval  The Count B.count Approximate histograms are calculated from the random sample of the Relation How to maintain these histograms? Different Classes of Histograms

12
Fast Incremental maintenance of approximate equi-depth histograms During Insertion  Maintain a threshold (T) upper bound  If no of tuples < T, insertion will increment the bucket count.  Else recompute the histogram Split and merge algorithm  Reduce the no. of recomputations from the sample  When bucket count reaches T, instead of recomputing split the bucket in half.  But maintain the number of bucket as fixed by merging two buckets whose total count

13
Split n merge algorithm Insert threshold

14
To handle modify and delete Deletion can lower the bucket count Maintain a T l as lower threshold Merge if below threshold Split bucket with largest count Delete threshold

15
Fast Incremental maintenance of approximate compressed histograms Values with high frequencies can span more than one bucket – replace by single bucket with single count –singleton buckets Construct compressed histogram on the sample and scale it by N/k factor. During insertions  If the count doesn’t exceed threshold, add to the bucket, else update bucket boundaries

16
Challenges to maintain compressed histograms New values may lead to data skew, which may lead to new singleton buckets Values may not belong to singleton buckets if tuples increase in equi-depth buckets Number of equi-depth buckets needs adjustment No. of tuples in equi-depth buckets needs adjustment

17
Solutions to the challenges Large number of same value will cause an equi-depth bucket to split but the adjacent boundaries will have same value, hint create singleton bucket for that value allow singleton buckets with small counts to be merged back into equi-depth buckets. Split and merge technique to control imbalance between equi-depth buckets and their tuples without recomputation

18
To handle deletion and modification Deletion can decrease number of tuples in a bucket relative to another bucket, making a singleton bucket can drop a bucket count to the lower threshold TL. What to do?  Merge the pair with smallest combined count and split the bucket with largest count  Else recompute from backing sample