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@]