Correlated Subqueries

correlated subquery is a subquery (a query nested inside another query) that uses values from the outer query in its WHERE clause. The sub-query is evaluated once for each row processed by the outer query.

execution step as follows:

1 The nested query executes after the outer query returns the row

2 the inner query  is evaluated for the  value in the passed-in

3 Each row returned by the outer query is evaluated for the results returned by the inner query

classified index ：

1 select:

SQL> select ename,sal,deptno from emp outer
  2  where sal>(select avg(sal) from emp where deptno=outer.deptno);

ENAME             SAL     DEPTNO
---------- ---------- ----------
ALLEN            1600         30
JONES            2975         20
BLAKE            2850         30
SCOTT            3000         20
KING             5000         10
FORD             3000         20

已选择6行。

explain difference:

SQL>  select ename,sal,deptno from emp outer
  2   where sal>(select avg(sal) from emp where deptno=outer.deptno);

执行计划
----------------------------------------------------------
Plan hash value: 1245077725

--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    39 |     8  (25)| 00:00:01 |
|*  1 |  HASH JOIN           |         |     1 |    39 |     8  (25)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |     3 |    78 |     4  (25)| 00:00:01 |
|   3 |    HASH GROUP BY     |         |     3 |    21 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP     |    14 |    98 |     3   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

   1 - access("DEPTNO"="OUTER"."DEPTNO")
       filter("SAL">"VW_COL_1")

----------no nested:

SQL>  select ename,sal,deptno from emp outer
  2   where sal>(select avg(sal) from emp where deptno=outer.deptno);

执行计划
----------------------------------------------------------
Plan hash value: 2649664444

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |    13 |    12   (0)| 00:00:01 |
|*  1 |  FILTER             |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   182 |     3   (0)| 00:00:01 |
|   3 |   SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  4 |    TABLE ACCESS FULL| EMP  |     5 |    35 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

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

   1 - filter("SAL"> (SELECT AVG("SAL") FROM "EMP" "EMP" WHERE
              "DEPTNO"=:B1))
   4 - filter("DEPTNO"=:B1)

 

2 update:

 

SQL> update emp e set dept_name=(select dname from dept d where d.deptno=e.deptno);

已更新14行。

执行计划
----------------------------------------------------------
Plan hash value: 684645255

--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
me     |

--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |         |    14 |   210 |     3   (0)| 00
:00:01 |

|   1 |  UPDATE                      | EMP     |       |       |            |
       |

|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   210 |     3   (0)| 00
:00:01 |

|   3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00
:00:01 |

|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00
:00:01 |

--------------------------------------------------------------------------------

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

   4 - access("D"."DEPTNO"=:B1)

 

3 delete:

SQL> delete from emp e where empno=(select empno from emp_test where empno=e.empno);

已删除14行。

4 exists:

SQL> select ename,sal,deptno from emp e where exists
  2  (select 1 from dept where deptno=e.deptno and loc='NEW YORK');

执行计划
----------------------------------------------------------
Plan hash value: 90266402

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   120 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |         |     5 |   120 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - filter("LOC"='NEW YORK')
   4 - access("DEPTNO"="E"."DEPTNO")

 

SQL> conn  / as sysdba;
已连接。
SQL> alter system set "_unnest_subquery"=false;

系统已更改。

SQL> conn scott/kk
已连接。
SQL> set autot traceonly exp
SQL> select ename,sal,deptno from emp e where exists
  2  (select 1 from dept where deptno=e.deptno and loc='NEW YORK');

执行计划
----------------------------------------------------------
Plan hash value: 90266402

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   120 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |         |     5 |   120 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - filter("LOC"='NEW YORK')
   4 - access("DEPTNO"="E"."DEPTNO")

5 not exists:

SQL>  select ename,sal,deptno from emp e where not exists
  2   (select 1 from dept where deptno=e.deptno and loc='NEW YORK')
  3  ;

执行计划
----------------------------------------------------------
Plan hash value: 2649839948

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     9 |   216 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS ANTI           |         |     9 |   216 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   3 - filter("LOC"='NEW YORK')
   4 - access("DEPTNO"="E"."DEPTNO")

----------no nested:

SQL>  select ename,sal,deptno from emp e where not exists
  2   (select 1 from dept where deptno=e.deptno and loc='NEW YORK')
  3  ;

执行计划
----------------------------------------------------------
Plan hash value: 2809975276

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     9 |   117 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | EMP     |    14 |   182 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "DEPT" "DEPT" WHERE
              "DEPTNO"=:B1 AND "LOC"='NEW YORK'))
   3 - filter("LOC"='NEW YORK')
   4 - access("DEPTNO"=:B1)

 

---end----

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

转载于:http://blog.itpub.net/13750068/viewspace-718460/