子查询展开是指优化器将子查询转换为等价的表连接，从而提高查询效率。如果子查询前的where条件为：
  ● =、<、>、<=、>=、<>
  ● EXISTS、IN、ANY
  ● NOT EXISTS、NOT IN、ALL
则目标SQL就有可能会做子查询展开。

来看一个简单的例子：
SQL> select employee_id,last_name from employees where employee_id in (select employee_id from job_history where department_id=80);

Execution Plan
----------------------------------------------------------
Plan hash value: 2277246041

------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                     |     2 |    40 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                  |                     |     2 |    40 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS                 |                     |     2 |    40 |     4  (25)| 00:00:01 |
|   3 |    SORT UNIQUE                 |                     |     2 |    16 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| JOB_HISTORY         |     2 |    16 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | JHIST_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN           | EMP_EMP_ID_PK       |     1 |       |     0   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID  | EMPLOYEES           |     1 |    12 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

这里的条件是IN（等价于EXISTS、=ANY），优化器是将子查询中的job_history拿出来和外部的employees表做了嵌套循环连接。可以使用NO_UNNEST Hint不让优化器做子查询展开：

SQL> select employee_id,last_name from employees where employee_id in (select /*+ no_unnest */employee_id from job_history where department_id=80);

Execution Plan
----------------------------------------------------------
Plan hash value: 3468528558

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |     1 |    12 |   109   (0)| 00:00:01 |
|*  1 |  FILTER                      |                   |       |       |            |          |
|   2 |   VIEW                       | index$_join$_001  |   107 |  1284 |     2   (0)| 00:00:01 |
|*  3 |    HASH JOIN                 |                   |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN     | EMP_EMP_ID_PK     |   107 |  1284 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN     | EMP_NAME_IX       |   107 |  1284 |     1   (0)| 00:00:01 |
|*  6 |   TABLE ACCESS BY INDEX ROWID| JOB_HISTORY       |     1 |     8 |     2   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | JHIST_EMPLOYEE_IX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "JOB_HISTORY" "JOB_HISTORY" WHERE
              "EMPLOYEE_ID"=:B1 AND "DEPARTMENT_ID"=80))

这个时候，子查询会在最后最后一步执行，且走filter执行计划，相当于使用子查询对主查询的结果逐一过滤，这个效率是比较低的。

如果条件是NOT IN（等价于 NOT EXISTS、<>ALL），则会转换成对应的反连接。

SQL> select employee_id,last_name from employees where employee_id not in (select employee_id from job_history where department_id=80);

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2462043498

----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                     |   105 |  2100 |     4   (0)| 00:00:01 |
|*  1 |  HASH JOIN ANTI              |                     |   105 |  2100 |     4   (0)| 00:00:01 |
|   2 |   VIEW                       | index$_join$_001    |   107 |  1284 |     2   (0)| 00:00:01 |
|*  3 |    HASH JOIN                 |                     |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN     | EMP_EMP_ID_PK       |   107 |  1284 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN     | EMP_NAME_IX         |   107 |  1284 |     1   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID| JOB_HISTORY         |     2 |    16 |     2   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN          | JHIST_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------