Histograms record the distribution of data when you analyze a table or index. With this information in hand, the cost-based optimizer can decide to use an index for conditions it knows will return a small number of rows and bypass the index when the condition will return many rows based on the limiting condition. The use of histograms is not limited to indexes. Any column of a table can have a histogram built on it.
When creating histograms, specify a size. This size relates to the number of buckets for the histogram. Each bucket will contain information about the value of the column(s) and the number of rows.
EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('scott','company', METHOD_OPT => 'FOR COLUMNS SIZE 10 company_code'); PL/SQL procedure successfully completed.
The preceding query creates a ten-bucket histogram on the COMPANY table, as shown in figure. The values for the COMPANY_CODE column are divided into the ten buckets as displayed in the figure. This example shows a large number (80 percent) of the COMPANY_CODE is equal to 1430. As is also shown in the figure, most of the width-balanced buckets contain only 3 rows; a single bucket contains 73 rows. In the height-balanced version of this distribution, each bucket has the same number of rows and most of the bucket endpoints are 1430, reflecting the skewed distribution of the data.