虚心请教相老师关于以下一个子查询嵌套的问题!!!
本帖最后由 wxjzqym 于 2012-9-18 16:56 编辑相老师您好，以下有两个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))
1、要看两个执行计划的优劣，需要通过执行统计信息来查看，因为时间很多时候不准确。
2、子查询最好写成多表连接的方式，这样效果更好一些。
3、你仔细看一下两个执行计划的优劣，就明白了。 上面的执行计划我在上课都分析过，回去好好看看笔记吧。 第二个SQL很好的实现了将子查询进行了合并处理，转换成了多表扫描。
第一个SQL没有实现SQL子查询的合并，存在潜在的性能问题。 not exists 换成 not in呢
页:
[1]