Oracle游标的性能优化

[liaoweke]

请问这个该怎么实现性能优化？

[beyon2008]

如果可以，改成关联批量update的方式，不要游标，游标是逐条处理，相比批量update效率低些

[beyon2008]

实在不行，把游标取得的数据塞到一个临时表里， 下面的update语句关联临时表

[liaoweke]

引用 3 楼 beyon2008 的回复:

实在不行，把游标取得的数据塞到一个临时表里， 下面的update语句关联临时表

怎么去弄 求SQL

[liaoweke]

跪求解答！！

[liaoweke]

引用 6 楼 xu176032 的回复:

游标优化，关键是优化游标的查询sql，你查询sql都没发，怎么优化啊。 另外你是根据rowid更新的，如果查询时候配合了order by应该效率很高的，关键还是看你的查询sql

INSERT INTO DATA_POLICYENDORKINDBASEINFO( POLICYNO, ENDORSEQNO, PLANCODE, RISKCODE, ITEMNO, ITEMKINDNO, KINDCODE, KINDNAME, ITEMDETAILCODE, ITEMDETAILLIST, MANUALPOLICYNO, -- ENDORNO, -- ENDORTYPE, -- ENDORTYPENAME, BUSINESSTYPE, CLASSCODE, INTERMEDIARYCODE, INTERMEDIARYCNNAME, AGREEMENTNO, SOLUTIONCODE, POLICYTYPE, APPLINAME, INSUREDNAME, STARTDATE, ENDDATE, UWYEAR, SUMGROSSPREMIUMA, SUMNETPREMIUMA, COMMISSIONA, SURCHARGEAMOUNTA, DISCOUNTRATE, COMMISIONRATE, CLIENTDISCOUNTA, UNDERWRITEENDDATE, UNDERWRITECODE, SALESMANCODE, OPERATORCODE, COMPANYCODE, ACCOUNTDATE, INPUTDATE, ALLOWRENEWIND, ISSUECOMPANY, ENDORTYPE, BUSINESSSOURCE, SUMINSUREDA, LIMITA, ORIGALCOMPANYCODE, UPDATEDATE, INSUREDCODE, PROMOTIONCODE, APPLICODE, OUTREINSCODE, OUTPOLICYNO, BROKERCODE, DEBITACCEPTER, COINSISSUEEXPENSE, DUESTARTDATE, TRCODE, TRNAME, SPECIALACCEPTANCE, CURRENCY, EXCHGRATE, OCCUPATION, OCCUPATIONNAME, TICCODE, TICCODENAME, --SUMGROSSPREMIUMANEW, --SUMNETPREMIUMANEW, --COMMISSIONANEW, --SURCHARGEAMOUNTANEW, --SUMINSUREDANEW, --LIMITANEW, -- KINDITEMRATE, KINDRISKRATE, Insuredsubjectmattercode, Insuredsubjectmatterdesc, Covercode, Covername, EmployeesClasscode, EmployeesOccupations, FACPLANCODE )SELECT A.POLICYNO , '000' ENDORSEQNO, ETL_FUNC_PACKAGE.GET_GUPOLICYCOPYRISK1(B.POLICYNO,B.ENDORSEQNO,B.RISKCODE,'PLANCODE') PLANCODE, B.RISKCODE, C.ITEMNO, C.ITEMKINDNO, C.KINDCODE, C.KINDNAME, C.ITEMDETAILCODE, C.ITEMDETAILLIST, A.MANUALPOLICYNO, -- ENDORNO, -- ENDORTYPE, -- ENDORTYPENAME, A.BUSINESSTYPE, ETL_FUNC_PACKAGE.GET_GUPOLICYCOPYRISK1(P_POLICYNO,'000',P_RISKCODE,'RISKCLASS'), A.INTERMEDIARYCODE, ETL_FUNC_PACKAGE.GET_AGENTNAME(INTERMEDIARYCODE) INTERMEDIARYCNNAME, A.AGREEMENTNO, A.SOLUTIONCODE, CASE WHEN A.RENEWIND = '0' AND A.ENDORSEQNO = '000' THEN 'N' --新单 WHEN A.RENEWIND = '1' AND A.ENDORSEQNO = '000' THEN 'R' --续保单 WHEN A.ENDORSEQNO != '000' THEN 'E' --批单 END POLICYTYPE, A.APPLINAME, A.INSUREDNAME, TRUNC(B.STARTDATE), TRUNC(B.ENDDATE), A.UWYEAR, NVL(C.GROSSPREMIUM,0), NVL(C.NETPREMIUM,0), 0 COMMISSIONA, 0 SURCHARGEAMOUNTA, 0 DISCOUNTRATE, 0 COMMISIONRATE, 0 CLIENTDISCOUNTA, A.UNDERWRITEENDDATE,A.UNDERWRITECODE, A.SALESMANCODE,A.OPERATORCODE, ETL_FUNC_PACKAGE.GET_COMPANYCODE(A.COMPANYCODE) COMPANYCODE, TRUNC(GREATEST(B.STARTDATE,A.UNDERWRITEENDDATE)) ACCOUNTDATE, TRUNC(A.INPUTDATE), A.ALLOWRENEWIND, ETL_FUNC_PACKAGE.GET_COMPANYCODE(A.ISSUECOMPANY) ISSUECOMPANY, '00' ENDORTYPE, A.BUSINESSSOURCE, NVL(C.SUMINSURED,0)*DECODE(C.CALCULATEIND,'1',1,0), -- 不计保额取保额为0 0 LIMITA, A.COMPANYCODE, SYSDATE, a.insuredcode, A.PROMOTIONCODE, A.APPLICODE, ETL_FUNC_PACKAGE.GET_POLICYCOPYREINSCEDEDINFO(P_POLICYNO, '000', 'OUTREINSCODE'), ETL_FUNC_PACKAGE.GET_POLICYCOPYREINSCEDEDINFO(P_POLICYNO, '000', 'OUTPOLICYNO'), ETL_FUNC_PACKAGE.GET_POLICYCOPYREINSCEDEDINFO(P_POLICYNO, '000', 'BROKERCODE'), ETL_FUNC_PACKAGE.GET_POLICYCOPYREINSCEDEDINFO(P_POLICYNO, '000', 'DEBITACCEPTER'), 0, -- A.COINSISSUEEXPENSE,-- TODO TRUNC(B.STARTDATE) DUESTARTDATE, A.TRCODE, ETL_FUNC_PACKAGE.GET_TRNAME(A.TRCODE), A.SPECIALACCEPTANCE, A.CURRENCY, V_EXCHRATE, GET_GUPOLICYCOPYEMPOLYINFO('OCCUPATION',P_POLICYNO,P_ENDORSEQNO,C.ITEMNO), GET_GUPOLICYCOPYEMPOLYINFO('OCCUPATIONNAME',P_POLICYNO,P_ENDORSEQNO,C.ITEMNO), GET_GUPOLICYCOPYEMPOLYINFO('TICCODE',P_POLICYNO,P_ENDORSEQNO,C.ITEMNO), GET_GUPOLICYCOPYEMPOLYINFO('TICCODENAME',P_POLICYNO,P_ENDORSEQNO,C.ITEMNO), --SUMGROSSPREMIUMANEW, --SUMNETPREMIUMANEW, --COMMISSIONANEW, --SURCHARGEAMOUNTANEW, --SUMINSUREDANEW, --LIMITANEW, -- 0 KINDITEMRATE, 0 KINDRISKRATE, GET_GUPOLICYCOPYITEMKINDINFO('INSUREDSUBJECTMATTERCODE',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) Insuredsubjectmattercode, GET_GUPOLICYCOPYITEMKINDINFO('INSUREDSUBJECTMATTERDESC',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) Insuredsubjectmatterdesc, GET_GUPOLICYCOPYITEMKINDINFO('COVERCODE',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) Covercode, GET_GUPOLICYCOPYITEMKINDINFO('COVERNAME',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) Covername, GET_GUPOLICYCOPYITEMKINDINFO('CLASSCODE',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) EmployeesClasscode, GET_GUPOLICYCOPYITEMKINDINFO('OCCUPATIONS',subStr(B.RISKCODE,0,2),P_POLICYNO,P_ENDORSEQNO,B.PLANCODE,B.RISKCODE,C.ITEMKINDNO) EmployeesOccupations, D.PLANCODE FROM GUPOLICYCOPYMAIN A,GUPOLICYCOPYRISK B,GUPOLICYCOPYITEMKIND C,GRFACPOLICY D WHERE A.POLICYNO = B.POLICYNO AND A.ENDORSEQNO = B.ENDORSEQNO AND A.POLICYNO = C.POLICYNO AND A.ENDORSEQNO = C.ENDORSEQNO AND A.POLICYNO = P_POLICYNO AND B.RISKCODE = C.RISKCODE AND A.ENDORSEQNO = P_ENDORSEQNO AND B.RISKCODE = P_RISKCODE AND C.POLICYNO = D.POLICYNO(+) AND C.ENDORSEQNO = D.ENDORSEQNO(+) AND C.ITEMNO=D.DANGERUNITNO(+); -- 设置险别保额/保费/佣金/征费 FOR REC_KIND IN CUR_KIND LOOP GET_GUPOLICYCOPYKINDINFO(P_POLICYNO,P_ENDORSEQNO,P_RISKCODE,REC_KIND.ITEMNO,REC_KIND.ITEMKINDNO,V_KINDINFO); UPDATE DATA_POLICYENDORKINDBASEINFO T SET T.SUMGROSSPREMIUMA = V_KINDINFO.GROSSPREMIUMKIND * V_EXCHRATE, T.SUMNETPREMIUMA = V_KINDINFO.NETPREMIUMKIND * V_EXCHRATE, T.SUMINSUREDA = T.SUMINSUREDA * V_EXCHRATE, T.COMMISSIONA = V_KINDINFO.COMMISSIONKIND * V_EXCHRATE, T.CLIENTDISCOUNTA = V_KINDINFO.DISCOUNTKIND * V_EXCHRATE, T.COMMISIONRATE = V_KINDINFO.COMMISSIONRATE , T.DISCOUNTRATE = V_KINDINFO.DISCOUNTRATE, T.SURCHARGEAMOUNTA = V_KINDINFO.SURCHARGEAMOUNTKIND* V_EXCHRATE , T.LIMITA = V_KINDINFO.LIMITKIND * V_EXCHRATE, T.KINDITEMRATE = V_KINDINFO.KINDITEMRATE, T.KINDRISKRATE = V_KINDINFO.KINDRISKRATE WHERE T.ROWID = REC_KIND.ROW_ID; V_TOTALKINDCOMMISION := V_TOTALKINDCOMMISION + V_KINDINFO.COMMISSIONKIND; V_TOTALKINDDISCOUNT := V_TOTALKINDDISCOUNT + V_KINDINFO.DISCOUNTKIND; V_TOTALKINDLIMIT := V_TOTALKINDLIMIT + V_KINDINFO.LIMITKIND; IF V_RISKCOMMISION = 0 OR V_RISKDISCOUNT = 0 OR V_RISKLIMIT = 0 THEN V_RISKCOMMISION := V_KINDINFO.COMMISSIONRISK; V_RISKDISCOUNT := V_KINDINFO.DISCOUNTRISK; V_RISKLIMIT := V_KINDINFO.LIMITRISK; END IF; END LOOP; 这是游标内容

[liaoweke]

请问 该怎么优化？

[beyon2008]

insert into tmp_table select * 然后 update DATA_POLICYEN set col= (select b.col from tmp_table b 就这个意思啊，建一张表tmp_table，把需要的临时数据集放进去，update DATA_POLICYEN的时候关联这张表，就不需要用游标了，明白了吗

[xu176032]

游标优化，关键是优化游标的查询sql，你查询sql都没发，怎么优化啊。 另外你是根据rowid更新的，如果查询时候配合了order by应该效率很高的，关键还是看你的查询sql

[liaoweke]