查询转换--子查询解嵌套

ora10g

HR@PROD>select * from employees where department_id in (select department_id from departments);

Execution Plan
----------------------------------------------------------
Plan hash value: 169719308

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |   106 |  7632 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS      |            |   106 |  7632 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  7276 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")

SQL> select * from employees where department_id in (select /*+ NO_UNNEST */ department_id from departments);

Execution Plan
----------------------------------------------------------
Plan hash value: 2979032901

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    10 |   690 |     3   (0)| 00:00:01 |
|*  1 |  FILTER            |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  7383 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPARTMENTS"
              "DEPARTMENTS" WHERE "DEPARTMENT_ID"=:B1))
   3 - access("DEPARTMENT_ID"=:B1)

ora11g

SQL> select * from employees where department_id in (select department_id from departments);

Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |   106 |  7314 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |   106 |  7314 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - filter("DEPARTMENT_ID" IS NOT NULL)

HR@PROD>select * from employees where department_id in (select /*+ NO_UNNEST */ department_id from departments);

Execution Plan
----------------------------------------------------------
Plan hash value: 2979032901

---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |    10 |   680 |     3   (0)| 00:00:01 |
|*  1 |  FILTER            |            |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  7276 |     3   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPARTMENTS"
              "DEPARTMENTS" WHERE "DEPARTMENT_ID"=:B1))
   3 - access("DEPARTMENT_ID"=:B1)