最佳化update-虛擬表,最佳化update-虛擬
改前:A1update gls_voucher t set t.promptval = (select sum(m.originalval) from gls_vchitem m where m.vid = t.vid and m.dir = 1);B1update gls_voucher t set t.promptexplain = (select max(n.explain) from gls_vchitem n where n.vid = t.vid and n.viid = (select min(m.viid) from gls_vchitem m where m.vid = n.vid and m.explain is not null));
改後:A1'update (select n.yr, n.promptval, a.originalval newVal from gls_voucher n inner join (select m.vid, sum(m.originalval) originalval from gls_vchitem m where m.dir = 1 group by m.vid) a on n.vid = a.vid) t set t.promptval = t.newVal;B1'update (select n.yr, n.promptexplain, q.explain newExplain from gls_voucher n inner join (select p.vid, max(p.explain) explain from gls_vchitem p inner join (select m.vid, min(m.viid) viid from gls_vchitem m where m.explain is not null group by m.vid) a on p.viid = a.viid group by p.vid) q on n.vid = q.vid) t set t.promptexplain = t.newExplain;
--下邊這個語句會報錯 ORA-01779: 無法修改與非鍵值儲存表對應的列說明: gls_voucher憑證(vid主鍵) 與 gls_vchitem憑證項(viid主鍵) 通過 vid 關聯目的: 更新憑證promptexplain=該憑證下憑證項中的第一條explain不為空白的explain值
原因: 通過第二個inner join關聯後,oracle不能確定gls_voucher n和gls_vchitem p是一對一的查詢,改成上面B1'就行了update (select n.yr, n.promptexplain, p.explain newExplain from gls_voucher n inner join (select m.vid, min(m.viid) viid from gls_vchitem m where m.explain is not null group by m.vid) a on n.vid = a.vid inner join gls_vchitem p on a.viid = p.viid) t set t.promptexplain = t.newExplain