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

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)