Jonathan Lewis "Basic Optimizer Selectivity #2"
Tuesday, September 10, 2013 from 12:00 PM to 1:30 PM (CDT)
San Francisco, California
London, United Kingdom
Basic Optimzer Selectivity
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 stats Oracle 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 recognise such anomalies, the less time you will spend tuning your SQL.
Speak-Tech is Minnesota-based and has organized webinars and seminars featuring the world’s leading Oracle experts for over 12 years.
Speak-Tech has organized over 100 training events for experts such as Jonathan Lewis, Tom Kyte, Cary Millsap, Tim Gorman, Gaja Krishna Vaidyanatha and David Kurtz, considered to be some of the world’s leading experts in Oracle Performance Tuning; Syed Jaffar Hussain, Oracle ACE Director who was awarded the Oracle Excellence Award for Technologist of the Year: DBA, 2011; Steven Feuerstein, considered to the world's leading expert in PL/SQL, Jim Czuprysnski and Matt Hart, all leading experts in their Oracle specialties.