ITPub博客

首页 > 数据库 > Oracle > 将游标修改为建表来提高效率

将游标修改为建表来提高效率

原创 Oracle 作者:lhrbest 时间:2014-08-01 22:46:00 0 删除 编辑

记录日期: 2014-07-30 14:25:27

 

    题记:

最近一个同事说一个更新语句很慢求助与我,我看了下,这类语句的优化具有典型的代表性,于是记录下来和大家共享下。

 

原sql语句:

 

DECLARE

 

V_PARTYNO VARCHAR2(20);

V_APP_MP VARCHAR2(20);

V_INTIME DATE;

V_CNT INT;

 

BEGIN

FOR CUR IN (SELECT APPLICATION_NO FROM FRAUD_SCORE) LOOP

SELECT MAX(PARTY_NO),

MAX(MOBILE_PHONE_NO),

MAX(IMPORT_CPPCDM_TIME)

INTO V_PARTYNO,

V_APP_MP,

V_INTIME

FROM RISKREPT.RKO_CDM_PROCESS

WHERE APPLICATION_NO = CUR.APPLICATION_NO;

 

SELECT COUNT(1)

INTO V_CNT

FROM RISKREPT.RKO_CDM_PROCESS

WHERE PARTY_NO = V_PARTYNO

AND MONTHS_BETWEEN(V_INTIME,

IMPORT_CPPCDM_TIME) <= 12;

 

UPDATE FRAUD_SCORE

SET APP_LOANCARD_NUM = V_CNT

WHERE APPLICATION_NO = CUR.APPLICATION_NO;

COMMIT;

END LOOP;

INSERT INTO FRAUD_SCORE_LOG

(VAR_NAME,

FINISH_TIME,

SEQ_NO)

VALUES

('APP_LOANCARD_NUM',

SYSDATE,

'8');

END P_APP_LOANCARD_NUM;

 

 

 

看下数据量:

 

分析:表FRAUD_SCORE 大约有5147929 行数据,采用这种方式大约需要24小时,所以优化。。。。。。

这个块中表RISKREPT.RKO_CDM_PROCESS有多次扫描,该表也是400W的数据量,所以从这里下手优化,一般来说,建表的方式比游标要快,所以,提供三种优化方式:

 

 

 

---------------优化后的三种办法:

 

-------------------------------- 第一种办法 (推荐)----- 尽量将语句简单化

--------- 如果是存过中就用truncate + append + nologging 完成

------------ 我开了并行,大约10分钟,不开并行的话2个小时肯定跑完了

----------这种方式表建立完成后最好把表的并行模式修改为1

 

Create Table MHQ_TMP_A nologging parallel 20 As

SELECT A.ROWID ROWIDS,

B.APPLICATION_NO,

MAX(PARTY_NO) PARTY_NO,

MAX(MOBILE_PHONE_NO) MOBILE_PHONE_NO,

MAX(IMPORT_CPPCDM_TIME) IMPORT_CPPCDM_TIME

FROM RISKPUBFLN.FRAUD_SCORE A,

RISKREPT.RKO_CDM_PROCESS B

WHERE B.APPLICATION_NO = A.APPLICATION_NO

GROUP BY A.ROWID,

B.APPLICATION_NO ;

 

 

Create Table MHQ_TMP_B nologging parallel 20 As

SELECT A.ROWIDS,

COUNT(1) COUNTS

FROM MHQ_TMP_A A,

RISKREPT.RKO_CDM_PROCESS B

WHERE A.PARTY_NO = B.PARTY_NO

AND MONTHS_BETWEEN(A.IMPORT_CPPCDM_TIME,

B.IMPORT_CPPCDM_TIME) <= 12

GROUP BY A.ROWIDS;

 

---select count(1) from MHQ_TMP_B;

 

MERGE INTO RISKPUBFLN.FRAUD_SCORE A

USING MHQ_TMP_B B

ON (B.ROWIDS=A.ROWID)

WHEN MATCHED THEN

UPDATE SET A.APP_LOANCARD_NUM = B.COUNTS;

COMMIT;

 

 

 

-------------------------------- 第二种办法 (语句复杂,解析慢点,不太推荐)

 

MERGE INTO RISKPUBFLN.FRAUD_SCORE T

USING (SELECT BB.APPLICATION_NO,

COUNT(1) COUNTS

FROM (SELECT B.APPLICATION_NO,

MAX(PARTY_NO) PARTY_NO,

MAX(IMPORT_CPPCDM_TIME) IMPORT_CPPCDM_TIME

FROM RISKPUBFLN.FRAUD_SCORE A,

RISKREPT.RKO_CDM_PROCESS B

WHERE B.APPLICATION_NO = A.APPLICATION_NO

GROUP BY B.APPLICATION_NO) AA,

RISKREPT.RKO_CDM_PROCESS BB

WHERE BB.PARTY_NO = AA.PARTY_NO

AND MONTHS_BETWEEN(AA.IMPORT_CPPCDM_TIME,

BB.IMPORT_CPPCDM_TIME) <= 12

GROUP BY BB.APPLICATION_NO) TT

ON (T.APPLICATION_NO = TT.APPLICATION_NO)

WHEN MATCHED THEN

UPDATE SET T.APP_LOANCARD_NUM = TT. COUNTS

;

COMMIT;

 

 

 

 

-------------------------------- 第三 种办法 (游标for循环)

---------------- 尽量减少loop中的循环处理逻辑,也就是减少大表扫描次数

 

DECLARE

 

V_PARTYNO VARCHAR2(20);

V_APP_MP VARCHAR2(20);

V_INTIME DATE;

V_CNT INT;

 

V_COUNTS NUMBER;

 

BEGIN

FOR CUR IN ( SELECT A.ROWID ROWIDS,

B.APPLICATION_NO,

MAX(PARTY_NO) V_PARTYNO,

MAX(MOBILE_PHONE_NO) V_APP_MP,

MAX(IMPORT_CPPCDM_TIME) V_INTIME

FROM RISKPUBFLN.FRAUD_SCORE A,

RISKREPT.RKO_CDM_PROCESS B

WHERE B.APPLICATION_NO = A.APPLICATION_NO

group by A.ROWID ,B.APPLICATION_NO

order by a.rowid

 

) LOOP

 

SELECT COUNT(1)

INTO V_CNT

FROM RISKREPT.RKO_CDM_PROCESS

WHERE PARTY_NO = CUR.V_PARTYNO

AND MONTHS_BETWEEN(CUR.V_INTIME,

IMPORT_CPPCDM_TIME) <= 12;

 

UPDATE RISKPUBFLN.FRAUD_SCORE T

SET APP_LOANCARD_NUM = V_CNT

WHERE T.ROWID = CUR.ROWIDS;

 

V_NUM := V_NUM + 1;

IF MOD(V_NUM,

20000) = 0 THEN

COMMIT;

END IF;

 

END LOOP;

 

COMMIT;

INSERT INTO RISKPUBFLN.FRAUD_SCORE_LOG

(VAR_NAME,

FINISH_TIME,

SEQ_NO)

VALUES

('APP_LOANCARD_NUM',

SYSDATE,

'8');

END P_APP_LOANCARD_NUM;

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1244050/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1367
  • 访问量
    8348666