Column Histograms and Data Skew in Oracle 26ai
A histogram is a type of column statistic describing a value distribution in more detail. It is not an index, and the column need not be indexed. The historical filename is retained, but the lesson's subject is column histograms and their role in estimating rows.
When uniform assumptions miss the workload
Suppose most rows have status ACTIVE while a small number have status REVIEW. Knowing only how many distinct status values exist does not describe the frequency of either value. A suitable histogram may improve estimates for relevant predicates. It does not force an index scan or eliminate the cost of reading the matching rows.
Evaluate whether skew matters to queries, rather than collecting histograms on every column. A column with few distinct values can still benefit from distribution information. Conversely, many distinct values do not automatically justify a histogram. The workload and estimates supply the reason to investigate.
Inspect the current metadata
SELECT column_name, num_distinct, num_nulls,
histogram, num_buckets, sample_size, last_analyzed
FROM user_tab_col_statistics
WHERE table_name = 'EMPLOYEE'
ORDER BY column_name;
Assume EMPLOYEE belongs to the current schema. HISTOGRAM identifies the stored histogram type or NONE. NUM_NULLS estimates rows with null values, not the number of nullable columns. NUM_BUCKETS and SAMPLE_SIZE describe statistics collection and representation, not application result counts.
USER_TAB_HISTOGRAMS exposes endpoint information when closer inspection is justified. Endpoint values and numbers need interpretation according to the histogram type; they are not simply bars in a universal chart.
Collection choices
The preceding lesson uses DBMS_STATS with FOR ALL COLUMNS SIZE AUTO. That policy lets Oracle use column usage and data distribution in deciding where histograms are appropriate. Do not expect a histogram on every column after the call. Review the resulting metadata and estimates instead of assuming a bucket count proves success.
Oracle's documented types include frequency, top-frequency, and hybrid histograms, with height-balanced histograms described as legacy. The selection depends on collection settings and data characteristics. This lesson does not prescribe a fixed number of buckets for every column.
Histograms and binds require context
A histogram does not guarantee a separate optimal plan for each bind value. Cursor reuse, bind peeking, and adaptive cursor sharing can affect value-sensitive queries. Test both frequent and infrequent values and inspect the relevant child cursors when those mechanisms are involved.
Single-column distribution information also does not fully describe correlations across columns. Where combined predicate estimates are the problem, investigate extended statistics and query semantics rather than repeatedly increasing histogram detail.
Do not confuse a histogram with index design
A bitmap index and a B-tree index are access structures; a histogram supplies estimation information. A small number of distinct values is not sufficient reason to choose a bitmap index. Modification patterns, concurrency, query requirements, and storage must inform index design.
After a justified statistics change, compare estimates, measured work, and correct results. Record the cases tested and keep a recovery path. Continue to the module review to connect statistics with the full evaluation cycle.