Cardinality in Oracle Statistics
Cardinality in Oracle Statistics: A Practical DBA Perspective In Oracle databases, cardinality is one of the most critical concepts that directly influences query performance. Simply put, cardinality refers to the number of rows returned by a particular operation in an execution plan. The Oracle optimizer relies heavily on cardinality estimates to decide how a query should be executed. After years of working as a DBA, I can confidently say that most performance issues are not due to missing indexes or bad SQL alone—they often stem from incorrect cardinality estimates. What Exactly is Cardinality? Cardinality is the optimizer’s estimate of how many rows will be processed at each step of a query. For example: SELECT * FROM employees WHERE department_id = 10; If the optimizer estimates: 10 rows → low cardinality 10,000 rows → high cardinality This estimate determines whether Oracle will use: Index scan (for low cardinality result) Full table scan (for h...