• 影响子查询展开的情况


    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")

  • 相关阅读:
    移动端Web前端注解
    Atom 必备插件
    Gulp 前端自动化构建
    RESTful API 设计
    Cookie 的 增删查
    三步实现 AngularJS URL 去 # 号
    AngularJS $http 之 POST 传参
    Angular.JS中使用$watch监听模型变化
    AngularJS $apply() 解析
    [个人笔记]IDEA+MAVEN+testNG(reportNG)
  • 原文地址:https://www.cnblogs.com/hzcya1995/p/13352452.html
Copyright © 2020-2023  润新知