alter table paysys_acnt_first_login_gatway nologging;（PS:其实这里的nologging是不起作用的）
alter session enable parallel dml;
update /*+ parallel(t1,4) */ paysys_acnt_first_login_gatway t1
set province_id =
(select province_id
from paysys_account_login_log t2
where t1.account_id = t2.account_id
and t1.gateway_id = t2.gateway_id
and t1.login_date = t2.login_date);
偶最开始的做法是用的上述办法，之所以没有使用下面的关联UPDATE,是因为分析了它与上述两个的执行计划，但从计划看上边无论从Cost,Cardinality还是Bytes都优于关联UPDATE
update (select /*+ bypass_ujvc */
t1.province_id old_id, t2.province_id new_id
from paysys_acnt_first_login_gatway t1,
paysys_account_login_log t2
where t1.account_id = t2.account_id
and t1.gateway_id = t2.gateway_id
and t1.login_date = t2.login_date) r
set r.old_id = r.new_id;
如下是今天测试使用的，速度太夸张了247s就搞定了
declare
maxrows number default 5000;
row_id_table dbms_sql.Urowid_Table;
p_id_table dbms_sql.Number_Table;
cursor acnt_first_cur is
select /*+ use_hash(t1,t2) parallel(t1,4) parallel(t2,4) */
t2.province_id, t1.rowid
from paysys_acnt_first_login_gatway t1, paysys_account_login_log t2
where t1.account_id = t2.account_id
and t1.gateway_id = t2.gateway_id
and t1.login_date = t2.login_date
order by t1.rowid;
begin
open acnt_first_cur;
loop
exit when acnt_first_cur%notfound;
fetch acnt_first_cur bulk collect
into p_id_table, row_id_table limit maxrows;
forall i in 1 .. row_id_table.count
update paysys_acnt_first_login_gatway
set province_id = p_id_table(i)
where rowid = row_id_table(i);
commit;
end loop;
end;
|