大家好,我这里有一个业务.自己写的procedure效率很低下。希望大家能提供一个思路。
表A1(当事人缴费记录表)
表B1(当事人缴费记录明细表)表C(个人账户表)
表C1(个人账户年度表)
表C2(个人账户收入事件表)
表C3(个人账户收入事件明细表)
B1外键关联A1
C1外键关联C
C2外键关联C
C3外键关联C2每次银行回盘10W-50W数据
根据回盘内容(txt文件在procedure中读取):
1.更新A1,B1的到账年月、标志
2.新增C2,C3
3.更新C,C1金额我现在的处理方式:
1.把回盘对应的A1,B1数据写入临时表,创建C1,C2,C3零时表,写入C1临时表数据
2.更新A1,B1临时表数据,写入C1,C2,C3零时表数据
3.更新C表金额
4.整体更新A1,B1,C1,C2,C3临时表数据至原表(这一步还未完成,不清楚怎么把临时表数据更新至原表)目前测试数据13W,更新100条记录需要耗时22秒.先感谢大家帮助一个帖子最多只能给100分,结贴的时候会另开贴加分.
表A1(当事人缴费记录表)
表B1(当事人缴费记录明细表)表C(个人账户表)
表C1(个人账户年度表)
表C2(个人账户收入事件表)
表C3(个人账户收入事件明细表)
B1外键关联A1
C1外键关联C
C2外键关联C
C3外键关联C2每次银行回盘10W-50W数据
根据回盘内容(txt文件在procedure中读取):
1.更新A1,B1的到账年月、标志
2.新增C2,C3
3.更新C,C1金额我现在的处理方式:
1.把回盘对应的A1,B1数据写入临时表,创建C1,C2,C3零时表,写入C1临时表数据
2.更新A1,B1临时表数据,写入C1,C2,C3零时表数据
3.更新C表金额
4.整体更新A1,B1,C1,C2,C3临时表数据至原表(这一步还未完成,不清楚怎么把临时表数据更新至原表)目前测试数据13W,更新100条记录需要耗时22秒.先感谢大家帮助一个帖子最多只能给100分,结贴的时候会另开贴加分.
解决方案 »
- oracle 10g如何查找临时表空间的使用率?
- oracle 11g 问题,待高手帮忙!!!
- 求一条sql语句
- 范围内统计问题,答案正确即结贴,在线等
- oracle中alert.log文件在哪啊
- OACLE中什么是方差
- 为什么无法执行DBMS_LOGMNR_D???
- 初级ORACLE,送分问题啊~
- ora-01033 ORACLE initialization or shutdown in process
- 在删除和创建11g dbconsole 提示oracle.sysman.emcp.exception.EMConfigException: 数据库实例不可用
- 求一个汇总问题
- orcale 存储过程
测试1000条数据,耗时:156秒
查询出你要的数据耗费多少时间?
先确认是查询耗费的效率低,还是更新的效率低
做更新的话,如果表的索引很多的话会严重影响效率,
txt文件在procedure中读取
你这里是逐条更新还是批量更新?
试试把txt 内容先读到一张临时表,然后一句这个表来更新其他。这样也便于调试,看看是txt读取慢,还是更新慢。抛砖引玉,一起学习~
I_FILENAME IN VARCHAR2,
I_USERID IN VARCHAR2,
RESULTNUMBER OUT NUMBER,
RESULTMESSAGE OUT VARCHAR2) IS
TXT_FILE UTL_FILE.FILE_TYPE;
TXT_LINE VARCHAR2(50);
TXT_AAZ083 NUMBER;
TXT_AAE078 VARCHAR2(1);
TXT_AAE079 NUMBER;
AC50_ID NUMBER;
AC52_ID NUMBER;
AE02_ID NUMBER;
AC01_ID NUMBER;
CURRENTYEAR NUMBER;
T_GRJF NUMBER;
T_CZBT NUMBER;
T_AAE002 NUMBER;
BEGIN
RESULTNUMBER := 0;
CURRENTYEAR := TO_NUMBER(TO_CHAR(SYSDATE, 'yyyy'));
BEGIN
IF I_FILENAME IS NULL OR LENGTH(I_FILENAME) = 0 THEN
BEGIN
RAISE_APPLICATION_ERROR('-20002', '缺少文件名称!');
END;
END IF;
IF I_USERID IS NULL OR LENGTH(I_USERID) = 0 THEN
BEGIN
RAISE_APPLICATION_ERROR('-20002', '缺少操作用户ID!');
END;
END IF;
TXT_FILE := UTL_FILE.FOPEN('JFDZSJFH_DIRECTORY', I_FILENAME, 'R');
SELECT SEQ_AE02_AAZ002.NEXTVAL INTO AE02_ID FROM DUAL;
INSERT INTO AE02 (AAZ002, AAE011) VALUES (AE02_ID, I_USERID);
BEGIN
TXT_AAZ083 := 0;
WHILE TXT_AAZ083 IS NOT NULL LOOP
UTL_FILE.GET_LINE(TXT_FILE, TXT_LINE);
TXT_AAZ083 := GET_STRARRAYSTROFINDEX(TXT_LINE, '|', 0);
TXT_AAE078 := GET_STRARRAYSTROFINDEX(TXT_LINE, '|', 2);
TXT_AAE079 := GET_STRARRAYSTROFINDEX(TXT_LINE, '|', 1);
SELECT AAZ010 INTO AC01_ID FROM AE23 WHERE AAZ083 = TXT_AAZ083;
SELECT AAZ116
INTO AC50_ID
FROM AC50
WHERE AAC001 = AC01_ID
AND AAE100 = '1';
UPDATE AC43
SET AAE079 = TXT_AAE079, AAE078 = TXT_AAE078
WHERE AAZ083 = TXT_AAZ083;
UPDATE AE23
SET AAE079 = TXT_AAE079, AAE078 = TXT_AAE078
WHERE AAZ083 = TXT_AAZ083;
SELECT SEQ_AC52_AAZ119.NEXTVAL INTO AC52_ID FROM DUAL;
INSERT INTO AC52
(AAE207, AAZ002, AAZ116, AAZ119, AAA097)
VALUES
(TXT_AAE079, AE02_ID, AC50_ID, AC52_ID, '101');
IF T_AAE002 IS NULL THEN
SELECT AC43.AAE002
INTO T_AAE002
FROM AC43
WHERE AC43.AAZ083 = TXT_AAZ083
AND ROWNUM <= 1;
END IF;
SELECT AC43.AAE023
INTO T_GRJF
FROM AC43
WHERE AC43.AAZ083 = TXT_AAZ083
AND AC43.AAE341 = '11';
SELECT AC43.AAE023
INTO T_CZBT
FROM AC43
WHERE AC43.AAZ083 = TXT_AAZ083
AND AC43.AAE341 != '11';
INSERT INTO AC53
(AAA027, AAE023, AAE228, AAZ116, AAZ118, AAZ119, AAC001, AAE341)
VALUES
('000001',
T_GRJF,
TXT_AAE079 || '01',
AC50_ID,
SEQ_AC53_AAZ118.NEXTVAL,
AC52_ID,
AC01_ID,
'11');
INSERT INTO AC53
(AAA027, AAE023, AAE228, AAZ116, AAZ118, AAZ119, AAC001, AAE341)
VALUES
('000001',
T_CZBT,
TXT_AAE079 || '01',
AC50_ID,
SEQ_AC53_AAZ118.NEXTVAL,
AC52_ID,
AC01_ID,
'11');
UPDATE AC51
SET AC51.AAE264 = AC51.AAE264 + T_GRJF,
AC51.AAE266 = AC51.AAE266 + T_CZBT,
AC51.AAE335 = AC51.AAE335 + T_CZBT,
AC51.AAE337 = AC51.AAE337 + T_CZBT
WHERE AC51.AAZ116 = AC50_ID
AND AC51.AAE001 = CURRENTYEAR;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(TXT_FILE);
IF SQLERRM != 'ORA-01403: 未找到数据' THEN
ROLLBACK;
RESULTNUMBER := 0;
RESULTMESSAGE := SQLERRM;
RETURN;
END IF;
END;
COMMIT;
RESULTNUMBER := 1;
RESULTMESSAGE := '处理成功';
EXCEPTION
WHEN OTHERS THEN
RESULTNUMBER := 0;
RESULTMESSAGE := SQLERRM;
ROLLBACK;
END;
END JFDZSJFH;