為何我都oracle資料庫做update或insert時，更新或插入幾百幾千很快，十萬條要半個小時左右，近百萬以上就5個小時以上，要是幾百萬條以上，就遙遙無期，有一次800多萬條想一次性做插入操作，結果50多個小時還沒結束，最后只好終止。
以前好像做更新或插入幾千萬條也很快。
現在用都插入陳述句是：
insert /*+append+*/ into mytest.xl1 (dh,sj,vi) select /*+ no_merge(a2) use_hash(a2) */ dh,sj,sum(jj) over(order by dh,sj rows between var50 PRECEDING and current row) as vi from mytest.xl a2 where a2.sj<20170000 and a2.sj>20160700;
update a1 set var51=(select vi from ( select /*+ no_merge(a2) use_hash(a2) */ dh,sj,sum(jj) over(order by dh,sj rows between var50 PRECEDING and current row) as vi
from a3 ) a2 where a2.dh=a1.dh and a1.sj=a2.sj );
uj5u.com熱心網友回復：
我的服務器是ibm 3850 x5，記憶體32g，cpu 16核，硬碟raid6,2T,用戶表空間96G，臨時表空間64Guj5u.com熱心網友回復：
alter table xl nologging;alter session set skip_unusable_indexes=true;
insert /*+append+*/ into mytest.xl1 (dh,sj,vi) select /*+ no_merge(a2) use_hash(a2) */ dh,sj,sum(jj) over(order by dh,sj rows between var50 PRECEDING and current row) as vi from mytest.xl a2 where a2.sj<20170000 and a2.sj>20160700;
用上述方式，20萬多條，用時34分鐘左右
uj5u.com熱心網友回復：
merge into a1
using ( select /*+ no_merge(a2) use_hash(a2) */ dh,sj,sum(jj)
over(order by dh,sj rows between var50 PRECEDING and current row) as vi from a1 where sj<20170000 and sj>20160700) a2
on ( a1.dh=a2.dh and a1.sj=a2.sj )
when matched then
update set a1.var51 = a2.vi;
commit;
2016-2017之間有70萬多條，更新了1個小時多還沒完成
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/249959.html
標籤：高級技術
下一篇：安裝Oracle011g(64位)提示[INS-20802] Oracle Net Configuration Assistant 失敗