我用下这个程序,运行速度很慢,
DECLARE
TYPE curtype IS REF CURSOR;
c_no1 curtype;
v_campaign_id VARCHAR2 (15);
v_pub_company_no CHAR (8);
v_assign_to_tm_userid NUMBER (4);
BEGIN
DBMS_OUTPUT.put_line ( ' start at '|| TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
OPEN c_no1 FOR
SELECT campaign_id, pub_company_no, assign_user_id
FROM assign_campaign_temp_new
WHERE guserid = g_userid AND assign_user_id <> 0;
LOOP
FETCH c_no1 INTO v_campaign_id, v_pub_company_no,
v_assign_to_tm_userid;
EXIT WHEN c_no1%NOTFOUND; UPDATE campaign_calllist
SET last_update_id = g_userid,
last_update_dte = SYSDATE,
assign_to_tm_userid = ''
WHERE lang_id = 0
AND company_no = 51000120
AND event_id = 202
AND campaign_id = v_campaign_id
AND pub_company_no = v_pub_company_no
AND assign_to_tm_userid IS NULL; END LOOP;
CLOSE c_no1;
DBMS_OUTPUT.put_line ( 'end at '|| TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;其中表campaign_calllist有上100万条记录.
campaign_calllist中有一个INDEX
CREATE INDEX PK_CAMPAIGN_CALLLIST ON CAMPAIGN_CALLLIST
(LANG_ID, EVENT_ID, COMPANY_NO, CAMPAIGN_ID, PUB_COMPANY_NO)现在表assign_campaign_temp_new中如果有500条记录,要5分钟才能完成. 有没有好的办法?
DECLARE
TYPE curtype IS REF CURSOR;
c_no1 curtype;
v_campaign_id VARCHAR2 (15);
v_pub_company_no CHAR (8);
v_assign_to_tm_userid NUMBER (4);
BEGIN
DBMS_OUTPUT.put_line ( ' start at '|| TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
OPEN c_no1 FOR
SELECT campaign_id, pub_company_no, assign_user_id
FROM assign_campaign_temp_new
WHERE guserid = g_userid AND assign_user_id <> 0;
LOOP
FETCH c_no1 INTO v_campaign_id, v_pub_company_no,
v_assign_to_tm_userid;
EXIT WHEN c_no1%NOTFOUND; UPDATE campaign_calllist
SET last_update_id = g_userid,
last_update_dte = SYSDATE,
assign_to_tm_userid = ''
WHERE lang_id = 0
AND company_no = 51000120
AND event_id = 202
AND campaign_id = v_campaign_id
AND pub_company_no = v_pub_company_no
AND assign_to_tm_userid IS NULL; END LOOP;
CLOSE c_no1;
DBMS_OUTPUT.put_line ( 'end at '|| TO_CHAR (SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;其中表campaign_calllist有上100万条记录.
campaign_calllist中有一个INDEX
CREATE INDEX PK_CAMPAIGN_CALLLIST ON CAMPAIGN_CALLLIST
(LANG_ID, EVENT_ID, COMPANY_NO, CAMPAIGN_ID, PUB_COMPANY_NO)现在表assign_campaign_temp_new中如果有500条记录,要5分钟才能完成. 有没有好的办法?
1.LANG_ID...PUB_COMPANY_NO分别建立索引试试。
2.在前台程序更新:比如 循环记录集更新
select *
from campaign_calllist cc,assign_campaign_temp_new actn
where lang_id = 0
AND cc.company_no = 51000120
AND cc.event_id = 202
AND cc.campaign_id = actn.campaign_id
AND cc.pub_company_no = actn.pub_company_no
AND assign_to_tm_userid IS NULL
AND actn.guserid=67不知道有什么好的办法来实现批量更新?
UPDATE campaign_calllist a
SET a.last_update_id = 67,
a.last_update_dte = SYSDATE,
a.assign_to_tm_userid =''
WHERE a.lang_id = 0
AND a.company_no = 51000120
AND a.event_id = 202
AND EXISTS (
SELECT *
FROM assign_campaign_temp_new b
WHERE b.guserid = 67
AND b.campaign_id = a.campaign_id
AND b.pub_company_no = a.pub_company_no
AND b.assign_user_id <> 0)
AND a.assign_to_tm_userid IS NULL 速度也同样很慢
....
from campaign_calllist a,assign_campaign_temp_new b
where a.last_update_id = b.b.guserid
...)
set old_last_update_id = new_last_update_id
....这时对assign_campaign_temp_new 有点要求,得有主键约束
试试这种速度
UPDATE campaign_calllist
SET last_update_id = g_userid,
last_update_dte = SYSDATE,
assign_to_tm_userid = ''
WHERE lang_id = 0
AND company_no = 51000120
AND event_id = 202
AND campaign_id = 'v_campaign_id'
AND pub_company_no = 'v_pub_company_no'
AND assign_to_tm_userid IS NULL;
的速度如何?观察执行上面SQL的执行计划是怎样的,是否用到了那个索引。