Unnesting of Nested Subqueries ( 10gr2 )

本文的目的是要研究一下nested subquery。所谓nested subquery，就是指出现在where语句中用括号括起来的子查询。通常oracle对这些子查询的处理情况是把它们当做一个独立的对象，嵌套在父查询中执行。比如说下面这个例子

SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select /*+ no_unnest  */ 1 from dept where deptno=emp.deptno and deptno=20);

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|*  1 |  FILTER             |      |      1 |        |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL | EMP  |      1 |     14 |     14 |00:00:00.01 |       4 |
|*  3 |   FILTER            |      |      3 |        |      1 |00:00:00.01 |       3 |
|*  4 |    TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------

仔细看一下执行计划，如果你了解FILTER操作，就会明白，子查询嵌套在父查询中执行了。这里的执行过程大概就是父查询对EMP表返回的每一条记录，都要拿给子查询去执行。跟NESTED LOOP差不多。但是随着CBO的升级，很多时候oracle可以把这种嵌套关系解开，让子查询中的表可以和父查询一起join，当然CBO这样做的前提是它认为这样做开销更小。但是CBO并没有这么智能，有时候它可能错误的把嵌套解开了，或者错误的使用了嵌套执行，那么我们的任务就是纠正它的错误，下面要了解的内容就是什么情况下CBO无法解开嵌套执行以及我们怎么样利用hint 让cbo选择嵌套或者解开嵌套。下面是本文主要要讲的内容。

1. 官方文档上关于 nested subquery的信息
2. 什么样的subquery无法unnest
3. 如何使用un_nest 和 unnest hint
4. 扩展一下，如何使用push_subq

1 官方文档上关于 nested subquery的信息

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.(这不一定按照之前的实验，有时候sub query 即使是 correlated subquery 也可以采用join)

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:

You can unnest an uncorrelated NOT IN subquery by specifying the HASH_AJ or MERGE_AJ hint in the subquery.（这里的两个AJ到底是什么意思）
You can unnest other subqueries by specifying the UNNEST hint in the subquery.

See Also:
Oracle Database Performance Tuning Guide for information on hints

2 什么样的subquery无法unnest

总结一下上面官方文档的内容，要注意这里的subquery是指在where从句中的，不是from中的。这些subquery通常会被嵌套调用，但是CBO在计算了开销之后可以根据情况解开。或者你可以通过HINT让cbo去解开嵌套。当然有些情况CBO无法解开嵌套。这些情况列举如下：

2.1 hierarchical subqueries
2.2 subqueries that contain a ROWNUM pseudocolumn
2.3 one of the set operators
2.4 a nested aggregate function
2.5 correlated reference to a query block that is not the immediate outer query block of the subquery

我们实验一下看看哪些情况无法解开。

2.1 hierarchical subqueries

ok，这个例子不太好想，也不常见，再议

2.2 subqueries that contain a `ROWNUM` pseudocolumn

我们使用两个查询来对比。第一个是普通的nested subquery。它要查出deptno为20的ename。第二个查询简单做了些修改，在subquery中加了个rownum>0.这是一个不影响逻辑的更改。按理说执行计划应该不会变。

SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20);

ENAME
------------------------------
FORD
ADAMS
SCOTT
JONES
SMITH

SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5v00mt710844w, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from
dept where deptno=emp.deptno and deptno=20)

Plan hash value: 2319207582

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|*  1 |  HASH JOIN SEMI     |         |      1 |      5 |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL | EMP     |      1 |     14 |     14 |00:00:00.01 |       3 |
|   3 |   VIEW              | VW_SQ_1 |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  4 |    TABLE ACCESS FULL| DEPT    |      1 |      1 |      1 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------

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

   1 - access("DEPTNO"="EMP"."DEPTNO")
   4 - filter("DEPTNO"=20)


22 rows selected.

SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20  and  rownum>0);

ENAME
------------------------------
SMITH
JONES
SCOTT
ADAMS
FORD

SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f5r68h4r1man7, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from
dept where deptno=emp.deptno and deptno=20  and  rownum>0)

Plan hash value: 351916621

----------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|*  1 |  FILTER               |      |      1 |        |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL   | EMP  |      1 |     14 |     14 |00:00:00.01 |       4 |
|   3 |   COUNT               |      |      3 |        |      1 |00:00:00.01 |       3 |
|*  4 |    FILTER             |      |      3 |        |      1 |00:00:00.01 |       3 |
|*  5 |     FILTER            |      |      3 |        |      1 |00:00:00.01 |       3 |
|*  6 |      TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   4 - filter(ROWNUM>0)
   5 - filter(:B1=20)
   6 - filter("DEPTNO"=:B1)


26 rows selected.

但是我们看到，第二个执行计划变成了嵌套执行的模式。因为subquery中使用了rownum，所以无法unnest。为什么加了rownum就无法unnest了呢？很好理解。因为subquery中有了rownum的限制就无法把subquery和上层查询 join了。

2.3 one of the set operators。

SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20);

ENAME
------------------------------
FORD
ADAMS
SCOTT
JONES
SMITH

SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5v00mt710844w, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from
dept where deptno=emp.deptno and deptno=20)

Plan hash value: 2319207582

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|*  1 |  HASH JOIN SEMI     |         |      1 |      5 |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL | EMP     |      1 |     14 |     14 |00:00:00.01 |       3 |
|   3 |   VIEW              | VW_SQ_1 |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  4 |    TABLE ACCESS FULL| DEPT    |      1 |      1 |      1 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------

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

   1 - access("DEPTNO"="EMP"."DEPTNO")
   4 - filter("DEPTNO"=20)


22 rows selected.

SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20 union select 1 from dept where deptno=emp.deptno and deptno=20);

ENAME
------------------------------
SMITH
JONES
SCOTT
ADAMS
FORD

SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  63yydqmyfp006, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from
dept where deptno=emp.deptno and deptno=20 union select 1 from dept where
deptno=emp.deptno and deptno=20)

Plan hash value: 850121941

----------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|*  1 |  FILTER               |      |      1 |        |      5 |00:00:00.01 |      10 |
|   2 |   TABLE ACCESS FULL   | EMP  |      1 |     14 |     14 |00:00:00.01 |       4 |
|   3 |   SORT UNIQUE         |      |      3 |      2 |      1 |00:00:00.01 |       6 |
|   4 |    UNION-ALL          |      |      3 |        |      2 |00:00:00.01 |       6 |
|*  5 |     FILTER            |      |      3 |        |      1 |00:00:00.01 |       3 |
|*  6 |      TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  7 |     FILTER            |      |      3 |        |      1 |00:00:00.01 |       3 |
|*  8 |      TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   5 - filter(:B1=20)
   6 - filter("DEPTNO"=:B1)
   7 - filter(:B1=20)
   8 - filter("DEPTNO"=:B1)

只是加了一段不影响逻辑的 Union，就让子查询无法unnest

2.4 a nested aggregate function

如下面的例子所示，子查询改写成具有aggregate function的形式， subquery就会嵌套执行

SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20);

ENAME
------------------------------
FORD
ADAMS
SCOTT
JONES
SMITH

SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5v00mt710844w, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from
dept where deptno=emp.deptno and deptno=20)

Plan hash value: 2319207582

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|*  1 |  HASH JOIN SEMI     |         |      1 |      5 |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL | EMP     |      1 |     14 |     14 |00:00:00.01 |       3 |
|   3 |   VIEW              | VW_SQ_1 |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  4 |    TABLE ACCESS FULL| DEPT    |      1 |      1 |      1 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------

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

   1 - access("DEPTNO"="EMP"."DEPTNO")
   4 - filter("DEPTNO"=20)


22 rows selected.

SQL> select /*+ gather_plan_statistics */ ename from emp where (select count(*) from dept where deptno=emp.deptno and deptno=20) >0;

ENAME
------------------------------
SMITH
JONES
SCOTT
ADAMS
FORD

SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f8j8ssu947nfc, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where (select count(*)
from dept where deptno=emp.deptno and deptno=20) >0

Plan hash value: 868944553

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|*  1 |  FILTER              |      |      1 |        |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL  | EMP  |      1 |     14 |     14 |00:00:00.01 |       4 |
|   3 |   SORT AGGREGATE     |      |      3 |      1 |      3 |00:00:00.01 |       3 |
|*  4 |    FILTER            |      |      3 |        |      1 |00:00:00.01 |       3 |
|*  5 |     TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------

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

   1 - filter(>0)
   4 - filter(:B1=20)
   5 - filter("DEPTNO"=:B1)


24 rows selected.

SQL>

2.5 correlated reference to a query block that is not the immediate outer query block of the subquery

SQL> select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from dept where deptno=emp.deptno and deptno=20);

ENAME
------------------------------
FORD
ADAMS
SCOTT
JONES
SMITH

SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5v00mt710844w, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp where exists (select 1 from
dept where deptno=emp.deptno and deptno=20)

Plan hash value: 2319207582

-----------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|*  1 |  HASH JOIN SEMI     |         |      1 |      5 |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL | EMP     |      1 |     14 |     14 |00:00:00.01 |       3 |
|   3 |   VIEW              | VW_SQ_1 |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  4 |    TABLE ACCESS FULL| DEPT    |      1 |      1 |      1 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------

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

   1 - access("DEPTNO"="EMP"."DEPTNO")
   4 - filter("DEPTNO"=20)


22 rows selected.

SQL> select /*+ gather_plan_statistics */ ename from emp e1 where exists
  2  (select 1 from dual where exists ( select 1 from dept where deptno=e1.deptno and deptno=20));

ENAME
------------------------------
SMITH
JONES
SCOTT
ADAMS
FORD

SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  5sg5s2u5wfbbh, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ ename from emp e1 where exists (select 1
from dual where exists ( select 1 from dept where deptno=e1.deptno and
deptno=20))

Plan hash value: 1158821859

---------------------------------------------------------------------------------------
| Id  | Operation            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------
|*  1 |  FILTER              |      |      1 |        |      5 |00:00:00.01 |       7 |
|   2 |   TABLE ACCESS FULL  | EMP  |      1 |     14 |     14 |00:00:00.01 |       4 |
|*  3 |   FILTER             |      |      3 |        |      1 |00:00:00.01 |       3 |
|   4 |    FAST DUAL         |      |      1 |      1 |      1 |00:00:00.01 |       0 |
|*  5 |    FILTER            |      |      3 |        |      1 |00:00:00.01 |       3 |
|*  6 |     TABLE ACCESS FULL| DEPT |      1 |      1 |      1 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   3 - filter( IS NOT NULL)
   5 - filter(:B1=20)
   6 - filter("DEPTNO"=:B1)


27 rows selected.

SQL>

这里第一个查询像以前一样，把子查询unnest用join的方式来连接两个表。第二个查询把第一个的子查询外面又加了一层子查询，这样第三层子查询就会引用第一层子查询的表。这种情况下subquery无法unnest。

3 如何使用un_nest 和 unnest hint

这篇转载的文章讲的非常好并且提到了filter操作。

http://www.cnblogs.com/kramer/archive/2013/04/12/3017013.html

4 扩展一下，如何使用push_subq

push_subq是让子查询先进行join。先看一下这个例子

SQL> create table kramer1 as select * from dba_objects;

Table created.

SQL> create table kramer2 as select * from dba_objects;

Table created.

SQL> create table kramer3 as select * from dba_objects;

Table created.

SQL> create table kramer4 as select * from dba_objects;

Table created.

SQL> create index kramer3_ind1 on kramer3(object_id);

Index created.




SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS','KRAMER1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS','KRAMER2');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.GATHER_TABLE_STATS('SYS','KRAMER3',CASCADE=>TRUE);

PL/SQL procedure successfully completed.

SQL>  exec dbms_stats.GATHER_TABLE_STATS('SYS','KRAMER4');

PL/SQL procedure successfully completed.

SQL> SELECT /*+ GATHER_PLAN_STATISTICS */ KRAMER1.OBJECT_ID FROM KRAMER1,KRAMER2,KRAMER4 WHERE
  2  (KRAMER1.OBJECT_ID+KRAMER2.OBJECT_ID)>50
  3  AND
  4  KRAMER1.OBJECT_TYPE=KRAMER4.OBJECT_TYPE
  5  AND
  6  8 IN (SELECT KRAMER3.OBJECT_ID FROM KRAMER3 WHERE KRAMER1.OBJECT_ID=KRAMER3.OBJECT_ID);
^CSELECT /*+ GATHER_PLAN_STATISTICS */ KRAMER1.OBJECT_ID FROM KRAMER1,KRAMER2,KRAMER4 WHERE
                                                            *
ERROR at line 1:
ORA-01013: user requested cancel of current operation



SQL> select * from TABLE(dbms_xplan.display_cursor(null,null,'iostats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
SQL_ID  fbykd58kquryg, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */ KRAMER1.OBJECT_ID FROM KRAMER1,KRAMER2,KRAMER4
WHERE (KRAMER1.OBJECT_ID+KRAMER2.OBJECT_ID)>50 AND
KRAMER1.OBJECT_TYPE=KRAMER4.OBJECT_TYPE AND 8 IN (SELECT KRAMER3.OBJECT_ID FROM
KRAMER3 WHERE KRAMER1.OBJECT_ID=KRAMER3.OBJECT_ID)

Plan hash value: 2572408404

-----------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|*  1 |  FILTER              |              |      1 |        |      0 |00:00:00.01 |       0 |
|*  2 |   HASH JOIN          |              |      1 |    303G|     13M|00:00:00.07 |     839 |
|   3 |    TABLE ACCESS FULL | KRAMER4      |      1 |  56711 |  56711 |00:00:00.01 |     786 |
|   4 |    NESTED LOOPS      |              |      1 |    160M|   3785 |00:00:00.02 |      53 |
|   5 |     TABLE ACCESS FULL| KRAMER1      |      1 |  56708 |      1 |00:00:00.01 |       4 |
|*  6 |     TABLE ACCESS FULL| KRAMER2      |      1 |   2835 |   3785 |00:00:00.01 |      49 |
|*  7 |   FILTER             |              |      1 |        |      0 |00:00:00.01 |       0 |
|*  8 |    INDEX RANGE SCAN  | KRAMER3_IND1 |      0 |      1 |      0 |00:00:00.01 |       0 |
-----------------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   2 - access("KRAMER1"."OBJECT_TYPE"="KRAMER4"."OBJECT_TYPE")
   6 - filter("KRAMER1"."OBJECT_ID"+"KRAMER2"."OBJECT_ID">50)
   7 - filter(8=:B1)
   8 - access("KRAMER3"."OBJECT_ID"=8)


31 rows selected.

SQL>

上面这个查询非常耗时，因为它是先把KRAMER1 和 KRAMER2 join，然后结果集和 KRAMER4 join。产生了一个非常大的结果集后再通过FILTER嵌套执行KRAMER3的subquery。这样会非常慢。所以我们通过ctrl+c先取消执行。如果你仔细看的话就会发现，KRAMER1和KRAMER3如果先进行join那么只会返回一行数据，这样就会非常的快，所以我们通过hint push_subq 来让子查询先join。

SQL> explain plan for
  2  SELECT /*+ GATHER_PLAN_STATISTICS push_subq(@tmp) */ KRAMER1.OBJECT_ID FROM KRAMER1,KRAMER2,KRAMER4 WHERE
  3  (KRAMER1.OBJECT_ID+KRAMER2.OBJECT_ID)>50
  4  AND
  5  KRAMER1.OBJECT_TYPE=KRAMER4.OBJECT_TYPE
  6  AND
  7  8 IN (SELECT/*+QB_Name(tmp)*/ KRAMER3.OBJECT_ID FROM KRAMER3 WHERE KRAMER1.OBJECT_ID=KRAMER3.OBJECT_ID);

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1216539656

---------------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |    15G|   396G|       |  1034K (53)| 03:26:57 |
|*  1 |  HASH JOIN           |              |    15G|   396G|  1168K|  1034K (53)| 03:26:57 |
|   2 |   TABLE ACCESS FULL  | KRAMER4      | 56711 |   498K|       |   180   (4)| 00:00:03 |
|   3 |   NESTED LOOPS       |              |  8039K|   145M|       |   501K  (4)| 01:40:18 |
|*  4 |    TABLE ACCESS FULL | KRAMER1      |  2835 | 39690 |       |   179   (4)| 00:00:03 |
|*  5 |     FILTER           |              |       |       |       |            |          |
|*  6 |      INDEX RANGE SCAN| KRAMER3_IND1 |     1 |     5 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS FULL | KRAMER2      |  2835 | 14175 |       |   177   (4)| 00:00:03 |
---------------------------------------------------------------------------------------------

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

   1 - access("KRAMER1"."OBJECT_TYPE"="KRAMER4"."OBJECT_TYPE")
   4 - filter( EXISTS (SELECT /*+ PUSH_SUBQ QB_NAME ("TMP") */ 0 FROM "KRAMER3"
              "KRAMER3" WHERE 8=:B1 AND "KRAMER3"."OBJECT_ID"=8))
   5 - filter(8=:B1)
   6 - access("KRAMER3"."OBJECT_ID"=8)
   7 - filter("KRAMER1"."OBJECT_ID"+"KRAMER2"."OBJECT_ID">50)

24 rows selected.

可以看到子查询先join，事实上这样执行也确实非常快，一秒内就可以返回结果。

posted on 2013-04-12 16:47 kramer 阅读(434) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

kramer

Unnesting of Nested Subqueries ( 10gr2 )

1 官方文档上关于 nested subquery的信息

2 什么样的subquery无法unnest

2.1 hierarchical subqueries

2.2 subqueries that contain a `ROWNUM` pseudocolumn

2.3 one of the set operators。

2.4 a nested aggregate function

2.5 correlated reference to a query block that is not the immediate outer query block of the subquery

3 如何使用un_nest 和 unnest hint

4 扩展一下，如何使用push_subq

导航

公告

kramer

Unnesting of Nested Subqueries ( 10gr2 )

1 官方文档上关于 nested subquery的信息

2 什么样的subquery无法unnest

2.1 hierarchical subqueries

2.2 subqueries that contain a ROWNUM pseudocolumn

2.3 one of the set operators。

2.4 a nested aggregate function

2.5 correlated reference to a query block that is not the immediate outer query block of the subquery

3 如何使用un_nest 和 unnest hint

4 扩展一下，如何使用push_subq

导航

公告

2.2 subqueries that contain a `ROWNUM` pseudocolumn