update cfg_custrisk r set r.needrecognise='1', r.recognisetype=vc_libtype
where exists(select 1 from ta_ack_acct t
where t.businesscode='103'
and t.transactioncfmdate>to_char((sysdate-to_number(arrchar(1))),'yyyymmdd')
and r.tano = t.tano and r.taaccountid = t.taaccountid
group by t.tano, t.taaccountid
having count(1) > to_number(arrchar(2)));
其中表cfg_custrisk 和表ta_ack_acct 数据量都上万级求高手们赐招!!!
where exists(select 1 from ta_ack_acct t
where t.businesscode='103'
and t.transactioncfmdate>to_char((sysdate-to_number(arrchar(1))),'yyyymmdd')
and r.tano = t.tano and r.taaccountid = t.taaccountid
group by t.tano, t.taaccountid
having count(1) > to_number(arrchar(2)));
其中表cfg_custrisk 和表ta_ack_acct 数据量都上万级求高手们赐招!!!
t.transactioncfmdate 数据库是什么类型 避免数据库隐私转换2 table nologging
第二点啥意思
--使用GROUP BY 应该不会影响速度,才万级数据。我用我自己的表做的测试,一千四百万行数据查询时间大约是50秒。
UPDATE
(SELECT r.needrecognise,
r.recognisetype,
t.tano,
t.taaccountid,
count(1)
FROM cfg_custrisk r,
ta_ack_acct t
WHERE r.tano = t.tano
AND r.taaccountid = t.taaccountid
AND t.businesscode ='103'
AND t.transactioncfmdate > TO_CHAR((sysdate-to_number(arrchar(1))),'yyyymmdd')
GROUP BY r.needrecognise,
r.recognisetype,
t.tano,
t.taaccountid
HAVING COUNT(1) > to_number(arrchar(2) ) a
SET a.needrecognise='1',
a.recognisetype =vc_libtype;
搞完后
alter table tablename logging;