when is histogram appropriate
Printable View
when is histogram appropriate
IMHO, histograms are most useful in the following 2 situations:
- Severely Skewed distributions
If you have a lookup code that can be 1 of 5 values, an even distribution would have 1/5th, or 20% of the records containing each value. Severely skewed statistics would be more like:
Value1 - 60%
Value2 - 35%
Value3 - 3%
Value2 - 1%
Value1 - 1%
In such circumstances, it is almost impossible for the optimizer to choose an execution plan that will be optimal for all values. A Histogram will give it the information it needs to make the proper choices for each different value.
- 'Extreme' Data Values
This is simply an extrapolation of the first case.
Say, for example, you have an indexed date field and 99.9% of the values are within the past ten years. Now what happens if there is a single date that has the year 1895? Such an entry will severely affect the optimizer's view of the data in that field. The ANALYZE process finds the lowest and highest values and stores these stats. The optimizer assumes that the distribution between these low and high values is 'flat'. That extreme date value will make the optimizer think that you have just as many 1899 entries as 1999 entries, which is certainly not the case. Again, the optimizer will make bad choices based on this incomplete data. In particular, it will affect ranged queries, which are quite common in date fields, hence my example. ie WHERE DateColumn BETWEEN '1/1/00' and '2/1/00'
In both of these cases, creating a histogram will provide the optimizer with enough information to make 'good' decisions when building the execution plans for statements using these fields.
Now, there are several problems with histograms.
- They *do not* work with binds. The values *must* be hard-coded in the statement. This means that they also will not work with CURSOR_SHARED=FORCE.
- Hard-coding these values will mean more statements in your shared pool and more parsing.
- Histograms must be re-built after every ANALYZE, which wipes them out.
- I hit a problem that I cannot find any corroboration on with exporting and re-importing stats with histograms via the DBMS_STATS package.
- Histograms go out the window with distributed queries.
However, given all that, they were still a life-saver for performance on my current project where many columns were already using hard-coded values, several columns have 'extreme' date values and *every* bloody column has severely-skewed distributions.
HTH,
- Chris