What is cardinality estimation?
In the world of the Oracle Query Optimizer, Cardinality Estimation is the single most important calculation. It is the "guess" the database makes about how many rows will be returned at each step of an execution plan.
If the cardinality is correct, the query is fast. If the cardinality is wrong, the database might choose a bicycle to move a mountain of data.
In database terms, Cardinality simply means the number of rows.
Table Cardinality: Total rows in the table.
Filter Cardinality: The number of rows expected to remain after a WHERE clause is applied.
Join Cardinality: The number of rows expected to result from combining two tables.
The Optimizer uses this number to decide which Join Method (Nested Loops vs. Hash Join) and which Access Path (Index vs. Full Table Scan) to use.
To get the Cardinality, Oracle first calculates Selectivity—a value between 0 and 1 representing the fraction of rows that will satisfy a filter.
The basic formula is:
You have an EMPLOYEES table with 10,000 rows and 50 distinct Departments.
If you query WHERE department_id = 10, the Optimizer assumes an even distribution.
Selectivity = $1 / 50 = 0.02$
Estimated Cardinality = $10,000 \times 0.02 = \mathbf{200}$ rows.
The Optimizer isn't a psychic; it's a mathematician. It relies on Statistics to make these guesses:
Distinct Values: How many unique entries are in a column?
Histograms: For skewed data (e.g., a "Status" column where 99% of rows are 'CLOSED'), histograms tell Oracle that the data isn't evenly distributed.
Null Counts: How many rows have no value in this column?
Cardinality errors are cumulative. If the Optimizer miscalculates the first step of a 5-table join, that error ripples through the entire plan.
Underestimation: Oracle thinks it's getting 1 row but gets 1 million. It picks a Nested Loop, and the query runs for hours.
Overestimation: Oracle thinks it's getting 1 million rows but gets 1. It prepares a massive Hash Join and allocates huge amounts of memory (PGA) it doesn't actually need.
The math often breaks down in these scenarios:
Correlated Columns: Searching for WHERE city = 'San Francisco' AND zip_code = 94105. Oracle might treat these as independent, even though they are perfectly correlated.
Complex Functions: WHERE UPPER(last_name) = 'SMITH'. Without a function-based index, Oracle has to "guess" the selectivity (usually defaulting to 1%).
Stale Stats: The statistics say the table is empty because it was analyzed before the daily bulk load.
When looking at an Execution Plan, compare the "E-Rows" (Estimated) column to the "A-Rows" (Actual) column.
| Operation | E-Rows | A-Rows |
| TABLE ACCESS FULL | 1 | 5,000,000 |
If these numbers are wildly different, you have a cardinality estimation problem.
Gather Fresh Stats: Use DBMS_STATS.GATHER_TABLE_STATS.
Extended Statistics: Tell Oracle that two columns are related (e.g., City and Zip Code).
Dynamic Sampling: Force Oracle to do a "quick count" of the data at runtime to get a better estimate.
Cardinality is the heartbeat of the Optimizer. Every performance tuning exercise is essentially an investigation into why the Optimizer's "guess" didn't match reality.