oracle SQL调整一例

以下语句从jcss_ocainfo表中，取出ticketseq不存在于jcss_ticketinfo中的记录。并且，每个重复的ticketseq只能取其中的一条记录。

select 。。。

from jcss_ocainfo t
where t.rowid in
(select min(t2.rowid) from jcss_ocainfo t2 group by ticketseq)
and t.rowid not in (select tt.rowid
from jcss_ocainfo tt, jcss_ticketinfo t2
where tt.ticketSeq = t2.ticketseq);

以下是上述语句的执行计划：

1 SELECT STATEMENT 88619 1 1512
2 NESTED LOOPS SEMI 88619 1 1512
3 MERGE JOIN ANTI 86865 1 1505
4 SORT JOIN 85741 193909 290475682
5 TABLE ACCESS FULL JCSS_OCAINFO 724 193909 290475682
6 SORT UNIQUE 1124 193909 1357363
7 VIEW VW_NSO_2 199 193909 1357363
8 MERGE JOIN 199 193909 7950269
9 INDEX FULL SCAN IDXAAAAA 26 193909 4653816
10 SORT JOIN 173 21570 366690
11 INDEX FAST FULL SCAN IDXAA 5 21570 366690
12 VIEW VW_NSO_1 1754 1939 13573
13 SORT GROUP BY 1754 193909 4653816
14 TABLE ACCESS FULL JCSS_OCAINFO 724 193909 4653816

这个执行过程是一个花时间的。

把not in 替换成not exists ，

select 。。。 from jcss_ocainfo t
where t.rowid in
(select min(t2.rowid) from jcss_ocainfo t2 group by ticketseq)
and not exists (select 1
from jcss_ocainfo ttt, jcss_ticketinfo t3
where ttt.rowid = t.rowid
and t.ticketSeq = t3.ticketseq)

执行的资源小了些。

1 SELECT STATEMENT 7856 97 145985
2 FILTER
3 MERGE JOIN SEMI 7662 97 145985
4 SORT JOIN 4983 9695 14523110
5 TABLE ACCESS FULL JCSS_OCAINFO 724 9695 14523110
6 SORT UNIQUE 2680 193909 1357363
7 VIEW VW_NSO_1 1754 193909 1357363
8 SORT GROUP BY 1754 193909 4653816
9 TABLE ACCESS FULL JCSS_OCAINFO 724 193909 4653816
10 NESTED LOOPS 2 1 24
11 TABLE ACCESS BY USER ROWID JCSS_OCAINFO 1 1 7

但总是感觉，描述的数据比较大。检查表jcss_ocainfo的统计信息，发布未有。于是，添加表的统计信息

analyze table ....

再查看执行计划，整个计划的资源占用就降起来了。

1 SELECT STATEMENT 1765 9 3825
2 FILTER
3 MERGE JOIN SEMI 1747 9 3825
4 SORT JOIN 848 948 396264
5 TABLE ACCESS FULL JCSS_OCAINFO 724 948 396264
6 SORT UNIQUE 900 4338 30366
7 VIEW VW_NSO_1 866 4338 30366
8 SORT GROUP BY 866 4338 117126
9 TABLE ACCESS FULL JCSS_OCAINFO 724 18954 511758
10 NESTED LOOPS 2 1 24
11 TABLE ACCESS BY USER ROWID JCSS_OCAINFO 1 1 7
12 INDEX RANGE SCAN IDXAA 1 1 17

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/197458/viewspace-1011175/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/197458/viewspace-1011175/