相老师您好，以下有两个sql，一个sql使用的是exists子句，另外一个sql以in代替exists子句，结果子查询由no_unnest状态转变为unnest状态且效率提高很多，我的疑问是对于复杂子查询（比如含有group by,connect by子句）来说，无论是in or exists默认情况下应该是不能嵌套化的才对啊，还是我对于嵌套与非嵌套的判断有误，希望相老师能帮我分析分析这两个sql在改写前后性能提高的真正原因，谢谢！

改写前的sql:
SQL> select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc
  2        from bsd_ticket a
  3       where a.inc_file_seq = 1
  4         and exists (select null
  5                from bsd_ticket t
  6               where t.inc_file_seq <> 1
  7                 and a.tdnr = t.tdnr
  8                 and a.tacn = t.tacn
  9                 and t.del_flag = 'N'
10                 and t.doctype_code <> '30'
11             group by t.tdnr, t.tacn
12             having count(*) > 1)
13        and not exists (select null
14               from bsd_trans_error e
15              where e.trans_id = a.trans_id
16                and e.err_code = '239')
17        and a.doctype_code = '10';

未选定行

已用时间:  00: 00: 08.64
----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                              |       |       | 11388 (100)|          |
|*  1 |  FILTER                       |                              |       |       |            |          |
|   2 |   NESTED LOOPS ANTI           |                              |     1 |    60 |     6   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID| BSD_TICKET                   |    14 |   700 |     5   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDX_BSD_TICKET_INC_FILESEQ   |    19 |       |     3   (0)| 00:00:
|*  5 |    TABLE ACCESS BY INDEX ROWID| BSD_TRANS_ERROR              |    16 |   160 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | IDX_BSD_TRANS_ERROR_TRANS_ID |     1 |       |     0   (0)|          |
|*  7 |   FILTER                      |                              |       |       |            |          |
|   8 |    SORT GROUP BY NOSORT       |                              |     1 |    26 | 11382   (1)| 00:02:17 |
|*  9 |     TABLE ACCESS FULL         | BSD_TICKET                   |     1 |    26 | 11382   (1)| 00:02:17 |
----------------------------------------------------------------------------------------------------

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

   1 - filter( IS NOT NULL)
   3 - filter("A"."DOCTYPE_CODE"='10')
   4 - access("A"."INC_FILE_SEQ"=1)
   5 - filter("E"."ERR_CODE"='239')
   6 - access("E"."TRANS_ID"="A"."TRANS_ID")
   7 - filter(COUNT(*)>1)
   9 - filter(("T"."TDNR"=:B1 AND "T"."TACN"=:B2 AND "T"."DOCTYPE_CODE"<>'30' AND "T"."DEL_FLAG"='N'
              AND "T"."INC_FILE_SEQ"<>1))

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
改写后的sql:
SQL> select a.trans_id, a.trnn, a.tdnr, a.agent_code, a.tacn, a.trnc
  2    from bsd_ticket a
  3   where a.inc_file_seq = 1
  4     and (tdnr, tacn) in (select tdnr, tacn
  5            from bsd_ticket t
  6           where t.inc_file_seq <> 1
  7             and t.del_flag = 'N'
  8             and t.doctype_code <> '30'
  9           group by tdnr, tacn
10          having count(*) > 1)
11     and not exists (select null
12            from bsd_trans_error e
13           where e.trans_id = a.trans_id
14             and e.err_code = '239')
15     and a.doctype_code = '10';

未选定行

已用时间:  00: 00: 01.23
----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                              |       |       | 11398 (100)|          |
|*  1 |  FILTER                         |                              |       |       |            |          |
|   2 |   HASH GROUP BY                 |                              |     2 |   196 | 11398   (1)| 00:02:17 |
|*  3 |    HASH JOIN                    |                              |    29 |  2842 | 11397   (1)| 00:02:17 |
|   4 |     NESTED LOOPS ANTI           |                              |    29 |  2088 |     8   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| BSD_TICKET                   |    42 |  2604 |     7   (0)| 00:00:01
|*  6 |       INDEX RANGE SCAN          | IDX_BSD_TICKET_INC_FILESEQ   |    42 |       |     3   (0)| 00:00:0
|*  7 |      TABLE ACCESS BY INDEX ROWID| BSD_TRANS_ERROR              |     5 |    50 |     1   (0)| 00:00
|*  8 |       INDEX RANGE SCAN          | IDX_BSD_TRANS_ERROR_TRANS_ID |     1 |       |     0   (0)|         
|*  9 |     TABLE ACCESS FULL           | BSD_TICKET                   |   477K|    11M| 11385   (1)| 00:02:17 |
----------------------------------------------------------------------------------------------------

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

   1 - filter(COUNT(*)>1)
   3 - access("TDNR"="TDNR" AND "TACN"="TACN")
   5 - filter("A"."DOCTYPE_CODE"='10')
   6 - access("A"."INC_FILE_SEQ"=1)
   7 - filter("E"."ERR_CODE"='239')
   8 - access("E"."TRANS_ID"="A"."TRANS_ID")
   9 - filter(("T"."DOCTYPE_CODE"<>'30' AND "T"."DEL_FLAG"='N' AND "T"."INC_FILE_SEQ"<>1))

not exists 换成 not in呢

第二个SQL很好的实现了将子查询进行了合并处理，转换成了多表扫描。
第一个SQL没有实现SQL子查询的合并，存在潜在的性能问题。

上面的执行计划我在上课都分析过，回去好好看看笔记吧。

1、要看两个执行计划的优劣，需要通过执行统计信息来查看，因为时间很多时候不准确。
2、子查询最好写成多表连接的方式，这样效果更好一些。
3、你仔细看一下两个执行计划的优劣，就明白了。