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

--------------------------------------------------------------------------------
| 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博客

Correlated Subqueries

最新文章