影响子查询展开的情况

1.限制子查询展开情况(rownum或者Row_Number() Over 函数):
SQL>select ename, deptno
  from emp
 where deptno in (select 
                   deptno
                    from dept
                   where dname = 'SALES' );  2    3    4    5    6  

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation		     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT	     |	       |     5 |   165 |     6	(17)| 00:00:01 |
|   1 |  MERGE JOIN		     |	       |     5 |   165 |     6	(17)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     2	 (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN	     | PK_DEPT |     4 |       |     1	 (0)| 00:00:01 |
|*  4 |   SORT JOIN		     |	       |    14 |   280 |     4	(25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL	     | EMP     |    14 |   280 |     3	 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - filter("DNAME"='SALES')
   4 - access("DEPTNO"="DEPTNO")
       filter("DEPTNO"="DEPTNO")

SQL> select ename, deptno
  from emp
 where deptno in (select 
                   deptno
                    from dept
                   where dname = 'SALES' and rownum<2);  2    3    4    5    6  

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1827978183

-----------------------------------------------------------------------------
| Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	    |	  5 |	100 |	  6   (0)| 00:00:01 |
|*  1 |  FILTER 	     |	    |	    |	    |		 |	    |
|   2 |   TABLE ACCESS FULL  | EMP  |	 15 |	300 |	  3   (0)| 00:00:01 |
|*  3 |   FILTER	     |	    |	    |	    |		 |	    |
|*  4 |    COUNT STOPKEY     |	    |	    |	    |		 |	    |
|*  5 |     TABLE ACCESS FULL| DEPT |	  1 |	 22 |	  3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter( EXISTS (<not feasible>)
   3 - filter("DEPTNO"=:B1)
   4 - filter(ROWNUM<2)
   5 - filter("DNAME"='SALES')


可以看到ROWNUM会影响子查询展开

2. union unionall 会限制 子查询 展开
SQL> select ename, deptno
  from emp
 where deptno in (select 
                   deptno
                    from dept
                   where dname = 'SALES' union select deptno from dept where  dname ='OPERATIONS' );  2    3    4    5    6  

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2859252660

-----------------------------------------------------------------------------
| Id  | Operation	     | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |	    |	 10 |	200 |	 11  (19)| 00:00:01 |
|*  1 |  FILTER 	     |	    |	    |	    |		 |	    |
|   2 |   TABLE ACCESS FULL  | EMP  |	 15 |	300 |	  3   (0)| 00:00:01 |
|   3 |   SORT UNIQUE	     |	    |	  2 |	 44 |	  8  (63)| 00:00:01 |
|   4 |    UNION-ALL	     |	    |	    |	    |		 |	    |
|*  5 |     TABLE ACCESS FULL| DEPT |	  1 |	 22 |	  3   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS FULL| DEPT |	  1 |	 22 |	  3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

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

   1 - filter( EXISTS ( (SELECT "DEPTNO" FROM "DEPT" "DEPT" WHERE
	      "DEPTNO"=:B1 AND "DNAME"='SALES')UNION (SELECT "DEPTNO" FROM "DEPT"
	      "DEPT" WHERE "DEPTNO"=:B2 AND "DNAME"='OPERATIONS')))
   5 - filter("DEPTNO"=:B1 AND "DNAME"='SALES')
   6 - filter("DEPTNO"=:B1 AND "DNAME"='OPERATIONS')

3.子查询外面使用的是exists,CBO里面有个限制:当子查询里面有 start with....connect by , CBO不会对子查询进行转换
如果子查询外面是In就可以展开
SQL> select *
  from departments a
 where exists
       (SELECT 1
          FROM employees b
         START WITH employee_id = 101
        CONNECT BY PRIOR employee_id = manager_id
        and a.department_id=b.department_id);
  2    3    4    5    6    7    8  
27 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3077864849

------------------------------------------------------------------------------------------------------
| Id  | Operation			    | Name	     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		    |		     |	   1 |	  21 |	  44   (0)| 00:00:01 |
|*  1 |  FILTER 			    |		     |	     |	     |		  |	     |
|   2 |   TABLE ACCESS FULL		    | DEPARTMENTS    |	  27 |	 567 |	   3   (0)| 00:00:01 |
|*  3 |   CONNECT BY WITH FILTERING (UNIQUE)|		     |	     |	     |		  |	     |
|   4 |    TABLE ACCESS BY INDEX ROWID	    | EMPLOYEES      |	   1 |	  11 |	   1   (0)| 00:00:01 |
|*  5 |     INDEX UNIQUE SCAN		    | EMP_EMP_ID_PK  |	   1 |	     |	   0   (0)| 00:00:01 |
|   6 |    NESTED LOOPS 		    |		     |	   1 |	  24 |	   2   (0)| 00:00:01 |
|   7 |     CONNECT BY PUMP		    |		     |	     |	     |		  |	     |
|*  8 |     TABLE ACCESS BY INDEX ROWID     | EMPLOYEES      |	   1 |	  11 |	   1   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN		    | EMP_MANAGER_IX |	   6 |	     |	   0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "EMPLOYEES" "B" START WITH "EMPLOYEE_ID"=101 CONNECT BY
	      "MANAGER_ID"=PRIOR "EMPLOYEE_ID" AND "B"."DEPARTMENT_ID"=:B1))
   3 - access("MANAGER_ID"=PRIOR "EMPLOYEE_ID")
       filter("B"."DEPARTMENT_ID"=:B1)
   5 - access("EMPLOYEE_ID"=101)
   8 - filter("B"."DEPARTMENT_ID"=:B1)
   9 - access("connect$_by$_pump$_003"."PRIOR employee_id "="MANAGER_ID")

4.如果子查询外面是In就可以展开
SQL> select *
  from departments a
 where a.department_id in 
       (SELECT b.department_id
          FROM employees b
         START WITH employee_id = 101
        CONNECT BY PRIOR employee_id = manager_id
        )
  2    3    4    5    6    7    8    9  ;

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3045523330

---------------------------------------------------------------------------------------------------
| Id  | Operation			 | Name 	  | Rows  | Bytes | Cost (%CPU)| Time	  |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT		 |		  |	7 |   238 |	6  (17)| 00:00:01 |
|   1 |  MERGE JOIN SEMI		 |		  |	7 |   238 |	6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID	 | DEPARTMENTS	  |    27 |   567 |	2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN		 | DEPT_ID_PK	  |    27 |	  |	1   (0)| 00:00:01 |
|*  4 |   SORT UNIQUE			 |		  |	7 |    91 |	4  (25)| 00:00:01 |
|   5 |    VIEW 			 | VW_NSO_1	  |	7 |    91 |	5  (40)| 00:00:01 |
|*  6 |     CONNECT BY WITH FILTERING	 |		  |	  |	  |	       |	  |
|   7 |      TABLE ACCESS BY INDEX ROWID | EMPLOYEES	  |	1 |    11 |	1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN 	 | EMP_EMP_ID_PK  |	1 |	  |	0   (0)| 00:00:01 |
|   9 |      NESTED LOOPS		 |		  |	6 |   144 |	2   (0)| 00:00:01 |
|  10 |       CONNECT BY PUMP		 |		  |	  |	  |	       |	  |
|  11 |       TABLE ACCESS BY INDEX ROWID| EMPLOYEES	  |	6 |    66 |	1   (0)| 00:00:01 |
|* 12 |        INDEX RANGE SCAN 	 | EMP_MANAGER_IX |	6 |	  |	0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

   4 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID")
       filter("A"."DEPARTMENT_ID"="DEPARTMENT_ID")
   6 - access("MANAGER_ID"=PRIOR "EMPLOYEE_ID")
   8 - access("EMPLOYEE_ID"=101)
  12 - access("connect$_by$_pump$_003"."PRIOR employee_id "="MANAGER_ID")

posted @ 2014-01-09 11:14  czcb  阅读(275)  评论(0编辑  收藏  举报