ORACLE Opetimizer Transforming Queries：Subquery Unnesting

一，先看看官方文档的解释：Often the database can improve the performance of queries that contain subqueries by unnesting the subqueries and converting them into joins. Most subqueries are unnested by the query transformer. For those subqueries that are not unnested, separate subplans are generated. 大概意思就是说oracle可以对包含子查询的查询语句可以通过非嵌套子查询和把子查询转换成普通表连接方式来提高查询的效率[@more@]

并且大多数子查询可以通过查询转换操作以非嵌套的方式来处理。对于嵌套的子查询优化器将产生单独的子计划。（当子查询出现在where部分的时候就是嵌套的，unnest就是优化器把子查询部分合并到包含此子查询的查询语句中。然后来评估访问路径和连接的代价。）
二，不能非嵌套的情况:
1，子查询中包含Hierarchical Queries: Start with and Connect By Prior clauses
2，子查询中包含Rownum伪劣
3，子查询包含嵌套的聚会函数
三，可能嵌套的情况：
1，Uncorrelated IN subqueries
2，In和Exists相关子查询，但是不能包含聚合函数或者group by操作
四，实验
sql测试一：
SQL> select t1.*
2 from emp t1
3 where t1.emp_no in(
4 select t2.emp_no from emp t2 where emp_no between 10 and 20);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1773068452

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 847 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 11 | 847 | 3 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 12 | 864 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | E_PK | 12 | | 2 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | E_PK | 1 | 5 | 0 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T1"."EMP_NO">=10 AND "T1"."EMP_NO"<=20)
4 - access("T1"."EMP_NO"="T2"."EMP_NO")
filter("EMP_NO"<=20 AND "EMP_NO">=10)

SQL> select t1.*
2 from emp t1
3 where t1.emp_no in(
4 select/*+no_unnest*/ t2.emp_no from emp t2 where emp_no between 10 and 20);

11 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3960111469

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 10102 (1)| 00:01:42 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 20000 | 1406K| 61 (4)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX UNIQUE SCAN| E_PK | 1 | 5 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "EMP" "T2" WHERE
:B1<=20 AND :B2>=10 AND "T2"."EMP_NO"=:B3 AND "EMP_NO"<=20 AND
"EMP_NO">=10))
3 - filter(:B1<=20 AND :B2>=10)
4 - access("T2"."EMP_NO"=:B1)
filter("EMP_NO"<=20 AND "EMP_NO">=10)
sql测试二：
SQL> select t1.*
2 from emp t1
3 where t1.dept_no in(
4 select t2.dept_no from dept t2 where dept_no=2);

3334 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 111771750

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3334 | 276K| 62 (4)| 00:00:01 |
| 1 | NESTED LOOPS | | 3334 | 276K| 62 (4)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN| SYS_C005459 | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 3334 | 234K| 61 (4)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("DEPT_NO"=2)
3 - filter("T1"."DEPT_NO"=2)

SQL> select t1.*
2 from emp t1
3 where t1.dept_no in(
4 select/*+no_unnest*/ t2.dept_no from dept t2 where dept_no=2);

3334 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 576075546

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3333 | 234K| 67 (3)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 20000 | 1406K| 61 (4)| 00:00:01 |
|* 3 | FILTER | | | | | |
|* 4 | INDEX UNIQUE SCAN| SYS_C005459 | 1 | 13 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "T2" WHERE
:B1=2 AND "T2"."DEPT_NO"=:B2))
3 - filter(:B1=2)
4 - access("T2"."DEPT_NO"=:B1)
以上的sql语句中都包含in的子查询，其实用了hint(no_unnest),根据 1 - filter可以看到优化器把in转换成exsit来处理了。执行计划出现了filter并且不同的执行计划cost都不同。在收集了统计信息的情况下，不管何种方式，只要cost低，效率高就是比较理想的执行计划。
在看这个sql语句：

SQL> select
2 t1.*
3 from emp t1
4 where t1.sal > (
5 select/*+unnest*/
6 avg(t2.sal)
7 from emp t2
8 where t2.dept_no = t1.dept_no
9 );

9998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1245077725

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000 | 98000 | 127 (8)| 00:00:02 |
|* 1 | HASH JOIN | | 1000 | 98000 | 127 (8)| 00:00:02 |
| 2 | VIEW | VW_SQ_1 | 6 | 156 | 65 (10)| 00:00:01 |
| 3 | HASH GROUP BY | | 6 | 48 | 65 (10)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 20000 | 156K| 60 (2)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 20000 | 1406K| 61 (4)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("DEPT_NO"="T1"."DEPT_NO"
filter("T1"."SAL">"VW_COL_1")

SQL> select
2 t1.*
3 from emp t1
4 where t1.sal > (
5 select/*+no_unnest*/
6 avg(t2.sal)
7 from emp t2
8 where t2.dept_no = t1.dept_no
9 );

9998 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2649664444

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 167 | 12024 | 425 (3)| 00:00:05 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMP | 20000 | 1406K| 61 (4)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 8 | | |
|* 4 | TABLE ACCESS FULL| EMP | 3333 | 26664 | 61 (4)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T1"."SAL"> (SELECT /*+ NO_UNNEST */ AVG("T2"."SAL") FROM
"EMP" "T2" WHERE "T2"."DEPT_NO"=:B1))
4 - filter("T2"."DEPT_NO"=:B1)
第一个执行计划优化器把子查询当作view来处理，对另一个emp全表扫描，最后对两个row source进行hash join。第二个执行计划还是采用了filter，其实这里只对emp t2扫描了6次（emp里面的dept_no有六个不同的值，cost=425=61*7，七次扫描其中有一次扫描代价是emp t1）。我测试之前收集了表的统计信息，所以优化器的估算还是比较准确的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25586587/viewspace-1050400/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25586587/viewspace-1050400/