Oracle Database Administration: The Essential Refe

ANALYZE TABLE . . . STATISTICS FOR COLUMNS

Sometimes the distribution of data in a column is not uniform. For example, a column may have three distinct values, A, B, and C, with relative distributions of 1%, 49%, and 50%, respectively. In this case, using an index to find all rows with a value of A would make sense, but it would not work for the values B and C. The results of the ANALYZE INDEX command would indicate that there are three distinct values, but would give no indication of the relative distribution of the data.

This is where histograms come into play. By grouping the values into buckets, Oracle can determine the distribution of data. With a histogram in place, Oracle would know that if it were looking for rows with a value of A (in the example above), an index would work just fine, but if it were looking for rows with either a B or C value, a full table scan would be more efficient.

Oracle uses a height-balanced histogram. That is, once it determines the number of buckets to use, it divides the total number of rows by the number of buckets. Each bucket then represents that many rows in sorted order. Oracle keeps track of the distribution of keys by storing the highest key value for each bucket. This is different from a width-balanced histogram, where the same number of distinct keys are stored in each bucket with a separate indication of the number of rows represented by each bucket.

The operation of the ANALYZE TABLE command changed slightly in Oracle7 Release 7.2. Since ...