subquery unnesting、Subquery unnesting and View Merge

本文内容系转载，如有侵犯你的知识产权，烦请及时告知本人，本人将即刻停止侵权行为。

参考网址：http://space.itpub.net/27064837/viewspace-746501

　　　　　http://space.itpub.net/471666/viewspace-694608

                  http://jonathanlewis.wordpress.com/2008/02/17/pushing-predicates-2/

                  http://www.oraclefans.cn/forum/showtopic.jsp?rootid=6963

　　　 　  http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/queries008.htm

1、Unnesting of Nested Subqueries

Unnesting of Nested Subqueries

Subqueries are nested when they appear in the WHERE clause of the parent statement. When Oracle Database evaluates a statement with a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins.

Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or a correlated reference to a query block that is not the immediate outer query block of the subquery.

Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:

• Uncorrelated IN subqueries

• IN and EXISTS correlated subqueries, as long as they do not contain aggregate functions or a GROUP BY clause

You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:
      1、 You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery.

      2、You can unnest other subqueries by specifying the UNNEST hint in the subquery

对嵌套子查询进行解嵌套：

     当子查询出现在主查询的where条件的时候，它们是嵌套的，当Oracle对子查询进行验证的时候，需要多次的验证子查询，并且要为其选择一个高效的访问路径和连接方式。

   Subquery unnesting 将子查询解套并且合并在包含他的主查询的语句中，并且允许优化器选择访问路径和连接方式的时候，将他们放在一块考虑，优化器可以将大部分的嵌套查询进行转换，除了一些特殊的，这些特殊情况包括：
层次查询、包含伪列rownum的子查询、聚合函数、不是主查询的直接子查询语句。

   可以使用Hint来指导优化器来优化如下的子查询：

    1、对于not in可以在子查询中使用HASH_AJ或MERGE_AJ的hint

    2、在子查询中直接使用UNNEST