Objectives:	An appreciation of the complexity and difficulty of the query optimization problem. Techniques for "optimizing the optimizer." Optimizer improvements in Oracle 11g.
Abstract:	The query optimizer assumes that the columns of a table are independent. For example, the optimizer cannot predict that the query SELECT * FROM CARS WHERE MAKE = 'TOYOTA' AND MODEL = 'ACCORD' will not produce any rows of data. Also, the optimizer does not know how many rows will be created when two tables are joined. The search for the best execution strategy is heavily influenced by the estimates of the cardinalities of SQL operations such as Selection and Join and is therefore prone to error. The query optimizer also has to contend with SQL redundancy, computational complexity, and bind variables. We analyze these four problems and discuss the available workarounds. We conclude with a discussion of improvements in Oracle 11g including the "learning optimizer," multi-column statistics, and statistics on expressions.
Audiernce Focus:	Developers
Expertise:	Intermediate
Track:	Development
Date:	Tuesday, April 15, 2008
Time:	3:30 PM - 4:30 PM