Make sure histograms exist on columns with uneven data distributions to ensure that the optimizer makes the best choice between indexes and table scans.
For range scans on data that is not uniformly distributed, the optimizers’ decisions will be improved by the presence of a histogram.
Histograms increase the accuracy of the optimizer’s cost calculations but increase the overhead of statistics collections. It’s usually worth creating histograms for columns where you believe the data will have an irregular distribution, and where the column is involved in WHERE or JOIN expressions.
CREATING HISTOGRAMS WITH METHOD_OPT
The METHOD_OPT option controls how column level statistics, in particular histograms, are created. The default value is ‘FOR ALL COLUMNS SIZE AUTO’,
which enables Oracle to choose the columns that will have a histogram collected and set the appropriate histogram bucket size.
For range scans on data that is not uniformly distributed, the optimizers’ decisions will be improved by the presence of a histogram.
Histograms increase the accuracy of the optimizer’s cost calculations but increase the overhead of statistics collections. It’s usually worth creating histograms for columns where you believe the data will have an irregular distribution, and where the column is involved in WHERE or JOIN expressions.
CREATING HISTOGRAMS WITH METHOD_OPT
The METHOD_OPT option controls how column level statistics, in particular histograms, are created. The default value is ‘FOR ALL COLUMNS SIZE AUTO’,
which enables Oracle to choose the columns that will have a histogram collected and set the appropriate histogram bucket size.
No comments:
Post a Comment