In a perfect world, data would be perfectly balanced. If you had a column for "Country," you’d have exactly the same number of customers in Iceland as you do in India.

In the real world, data is skewed. You might have 1 million customers in India and only 5 in Iceland. Without Histograms, the Oracle Optimizer is "colorblind" to this difference. It assumes an even distribution, which leads to disastrous execution plans.

1. The Core Concept: Beyond the Average

By default, Oracle calculates selectivity using a simple formula: $1 / \text{Number of Distinct Values}$.

• The Scenario: You have a STATUS column with 2 values: OPEN (50 rows) and CLOSED (999,950 rows).

• The "Blind" Guess: Without a histogram, Oracle assumes a 50/50 split. It thinks OPEN has 500,000 rows.

• The Result: Oracle sees 500,000 rows and chooses a Full Table Scan to find those 50 OPEN records. That is incredibly inefficient.

A Histogram tells Oracle: "Wait! This data is lopsided. Use an index for 'OPEN' but a full scan for 'CLOSED'."

2. The Two Main Types of Histograms

Oracle 12c and 19c primarily use two types of histograms to map data distribution.

A. Frequency Histograms

• How they work: Oracle counts every single occurrence of every value. Each unique value gets its own "bucket."

• Best for: Columns with a small number of unique values (low cardinality), like GENDER, STATUS, or REGION.

• Limit: Only available if the number of distinct values is less than or equal to the number of buckets (default 254).

B. Height-Balanced (or Hybrid) Histograms

• How they work: When there are too many unique values to count individually (like LAST_NAME), Oracle groups the data into buckets of equal height (e.g., each bucket represents 5% of the data).

• Hybrid (Modern): Introduced in 12c, Hybrid Histograms combine the best of both. they track the "End Point Number" and how many times that specific value repeats at the edge of a bucket, providing much better accuracy for "popular" values.

3. When Does Oracle Create a Histogram?

Oracle doesn't create histograms for every column because they take up space and make the "Hard Parse" phase slightly longer. It typically creates them only when:

1. The data is skewed (unevenly distributed).

2. The column is used in a WHERE clause (Oracle tracks "column usage" automatically).

4. How to Check for Histograms

You can see if a column has a histogram by looking at the USER_TAB_COL_STATISTICS view.

SELECT column_name, histogram, num_buckets
FROM user_tab_col_statistics
WHERE table_name = 'ORDERS';

• NONE: No histogram. Oracle assumes even distribution.

• FREQUENCY: Exact counts for small sets of values.

• HYBRID / HEIGHT BALANCED: Estimated distribution for large sets of values.

5. The "Bind Variable" Conflict

Histograms and Bind Variables have a complicated relationship.

• If you use a literal (WHERE status = 'OPEN'), Oracle uses the histogram perfectly.

• If you use a bind (WHERE status = :1), Oracle has to Peek at the variable (as we discussed earlier) to decide which part of the histogram to look at. If it doesn't peek, the histogram is essentially useless.

6. Summary: The Histogram Payoff

Pro-Tip: Forcing a Histogram

If Oracle isn't creating a histogram where you know one is needed, you can force its hand during a stats gather:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (
    ownname => 'SALES',
    tabname => 'CUSTOMERS',
    method_opt => 'FOR COLUMNS SIZE 254 country_code'
  );
END;

The SIZE parameter tells Oracle how many "buckets" to use. Using 254 (the max) provides the highest resolution.