Jonathan Lewis “Basic Optimizer Selectivity” - September 10, 2013
This webinar is intended to help those who want a better understanding of how Oracle estimates the number of rows that will be returned by a query. The topic is a very large one, and in this seminar we limit our observations almost exclusively to single table accesses with simple statistics and various classes of predicates. We will, however, touch briefly on the impact of histograms, and on how we extend single table selectivity to join selectivity, but a more thorough treatment of those topics will be the subject of a future webinar.
Particular details we will examine are:
1 - What we mean by selectivity and cardinality and what statsOracle uses to calculate them
2 - Basic single column selectivity for equals, not equals, and the effects of functions on columns.
3 - Range-based predicates on single columns - known values, unknown values, and the special case for indexes.
4 - The arithmetic of character columns and the LIKE operator
5 - Null effects and querying out of range.
6 - Multiple predicates and comparing columns.
7 - The distinct operator and the basic join calculations and sanity checks.
The more you understand about the ways in which the optimizer calculates cardinality, the easier it is to spot where some unexpected combination of predicates and data patterns has produced an unsuitable estimate and pushed the optimizer into an unsuitable execution plan.
The easier you find it to recognize such anomalies, the less time you will spend tuning your SQL.