/* Formatted on 2011/03/09 17:52 (Formatter Plus v4.8.8) */
UPDATE /*+index(d)*/ecard_hrms_emp_total d
SET (d.empsex, d.empname, d.empbirthday, d.nationid, d.empidno,
d.empstatusid, d.emptypeid, d.empcardtid, d.grdid, d.empmemo, d.dptno,
d.dptname, d.totalno, d.totalname, d.createuser, d.createdate,
d.maintuser, d.maintdate, d.SIGN, d.remark, d.dptid,
d.card_group_date) =
(SELECT s.empsex, s.empname, s.empbirthday, s.nationid, s.empidno,
s.empstatusid, s.emptypeid, s.empcardtid, s.grdid,
s.empmemo, s.dptno, s.dptname, s.totalno, s.totalname,
s.createuser, s.createdate, s.maintuser, s.maintdate,
s.SIGN, s.remark, s.dptid, s.card_group_date
FROM ecard_hrms_emp_total_movin_tmp s
WHERE d.empno = s.empno)
WHERE d.empno IN (SELECT empno
FROM ecard_hrms_emp_total_movin_tmp);
ecard_hrms_emp_total_movin_tmp有將近3.5萬筆數據
ecard_hrms_emp_total有108萬
先用游標讀出ecard_hrms_emp_total_movin_tmp表的工號信息,再逐個工號update ecard_hrms_emp_total 表相關欄位
新SQL邏輯讀是原來的1/20,明天再找開發協商改存儲過程
|