基于Oracle 的SQL优化

Lesson 5: Query Rewrite
• Describe the concept of subquery unnesting and identify its restrictions • Explain view merging • Describe how the optimizer evaluates IN-lists
A Comprehensive Example of IN-List
• XX9客户性能优化案例.doc － 顶尖高手也会犯错 a) in后面如果不是常量则无法应用IN-List Iterator和or expansion b) view中带union all则无法view merge c) 所以根本原因不在于view无法merge，而是在于in的filter执行 计划导致的谓词无法向上推入 d) 改掉sql中的in实现了谓词的推入
Subquery Unnesting: Examples
• Subquery_unnesting_example.txt
View Merging
• View merging rewrites queries containing views so that only the base tables remain. • View merging is only performed when a correct result is guaranteed. • View merging is performed prior to query optimization.
Subqueries That Can Be Unnested
Subqueries using the following conditional expressions can be unnested, if certain restrictions are met: • ANY (including IN) • ALL (including NOT IN) • EXISTS • NOT EXISTS • Single row (=, <, >, <=, >=)
View Merging: ExBaidu Nhomakorabeample
• View_merging_example_1.txt
View Merging Restrictions
For a view to be considered for merging, it must contain none of the following: • Set operators (UNION, UNION ALL, INTERSECT, MINUS) • A CONNECT BY clause • A ROWNUM pseudocolumn
Subquery Unnesting Notes
• the order of tables or join conditions does not specify the order of evaluation • the parentheses around a subquery do not imply that it should be evaluated first as a separate entity
Nonmergeable View Examples: ROWNUM Reference
Complex View Merging
• Enhancement that allows views containing GROUP BY or DISTINCT to be merged • Complex view merging can also be used to merge the queries produced by subquery unnesting.
IN-List and OR Evaluation
There are three methods available to the optimizer for handling IN-lists and OR expressions: • Use IN-list iterator functionality • Expand the list into a series of UNION ALL queries, one per expression • Apply the expressions as a filter • in_and_notin_example.txt
Subquery Unnesting Example
When Is Subquery Unnesting Performed?
• Subquery unnesting is performed in the query rewrite phase • Because subquery unnesting occurs prior to query optimization, it is optimizer independent. • In Oracle9i no unnesting operations are costed. • In Oracle 10g/above unnesting operations are costed
基于Oracle的SQL优化
崔华（dbsnake)
About Me
• 中航信工程师 • Oracle ACE • ACOUG成员
基于Oracle的SQL优化
• • • • • • • • 第一章 第二章 第三章 第四章 第五章 第六章 第七章 第八章 Oracle里的优化器 Oracle里的Cursor和绑定变量 Oracle里的查询转换 Oracle里的统计信息 Oracle里的执行计划 Oracle里的Hint Oracle里的并行 Oracle里SQL优化的方法论
IN-List Iterator
• The IN-list iterator function compares each row in the row source with each value from the IN-list. • The column compared with the IN-list must be indexed to use the IN-list Iterator function. • Typically, the IN-list iterator is more efficient than OR expansion. • There is no hint to force the IN-list iterator
IN-List Expansion
Converts IN-lists and OR expressions into a series of concatenated UNION ALL statements
IN-List and OR as a Filter
• In this case, the values in the IN-list are used to filter the result set after it has been retrieved. • In_list_and_or_example.txt
Thanks!
View Merging: Example
• View_merging_example_2.txt
Pushing Predicates
• The optimizer can transform a query block that accesses a nonmergeable view by pushing the query block's predicates inside the view's query or pulling the predicates from the view into the main query. • Pushing_predicates_example.txt • other_hints_example.txt
Subquery Unnesting
• The optimizer has two choices for subquery optimization: － Transform the subquery into an equivalent join and then optimize that join statement － Optimize the subquery as it is • If no equivalent join statement exists, then no transformation occurs. • Statements are equivalent if they return exactly the same rows under all circumstances.