看了pub上一篇关于no_unnest的文章后学习了一番，照着做了一下测试，加深理解：

unnest我们称为对子查询展开，顾名思义，就是别让子查询孤单地嵌套(nest)在里面。
所以un_unnest双重否定代表肯定，即让子查询不展开，让它嵌套(nest)在里面。

SQL> create table t1 tablespace cbo as select * from all_objects;

Table created.

SQL> create table t2 tablespace cbo as select * from all_objects;

Table created.

SQL> exec dbms_stats.gather_table_stats(user,'t1');

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(user,'t2');

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> select t1.object_id from t1 where exists
  2  (select 1 from t2 where t1.object_id=t2.object_id*10);

1061 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 40198852

-------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |     1 |    18 |    81   (2)| 00:00:01 |
|*  1 |  HASH JOIN SEMI     |         |     1 |    18 |    81   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | T1      | 10943 | 54715 |    40   (0)| 00:00:01 |
|   3 |   VIEW              | VW_SQ_1 | 10944 |   138K|    40   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| T2      | 10944 | 54720 |    40   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   1 - access("T1"."OBJECT_ID"="VW_COL_1")

Statistics
----------------------------------------------------------
        281  recursive calls
          0  db block gets
        381  consistent gets
          0  physical reads
          0  redo size
      14997  bytes sent via SQL*Net to client
       1166  bytes received via SQL*Net from client
         72  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
       1061  rows processed

如果把unnest禁止，不让子查询自动展开，在子查询中加入提示：

SQL> select t1.object_id from t1 where exists
  2  (select /*+ no_unnest */ 1 from t2 where t1.object_id=t2.object_id*10);

1061 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 895956251

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 | 16491   (1)| 00:03:18 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10943 | 54715 |    40   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     2 |    10 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
              "T2"."OBJECT_ID"*10=:B1))
   3 - filter("T2"."OBJECT_ID"*10=:B1)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1404928  consistent gets
          0  physical reads
          0  redo size
      14997  bytes sent via SQL*Net to client
       1166  bytes received via SQL*Net from client
         72  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1061  rows processed

这一次，逻辑读居然搞到140万，显然，如果子查询不展开，采用了一个filter的操作，对于每一个t1里的object_id,子查询都要执行一次来检查是否有符合条件的值，也就是说类似于nest loop join，但其效率似乎比nest loop join要好一点

如果加提示，采用nest loop join的方式：
SQL> select /*+ use_nl(t1 t2) */ t1.object_id from t1,t2 where t1.object_id=t2.object_id*10;

1061 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1967407726

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 10944 |   106K|   429K  (1)| 01:25:57 |
|   1 |  NESTED LOOPS      |      | 10944 |   106K|   429K  (1)| 01:25:57 |
|   2 |   TABLE ACCESS FULL| T1   | 10943 | 54715 |    40   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |     5 |    39   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   3 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID"*10)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
    1543243  consistent gets
          0  physical reads
          0  redo size
      14997  bytes sent via SQL*Net to client
       1166  bytes received via SQL*Net from client
         72  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1061  rows processed

逻辑读达到了154万！！！