求oracle插入初始数据pl/sql语句优化,该怎么处理

2012-02-07

求oracle插入初始数据pl/sql语句优化现有一张话务信息表，建表语句如下：create table VIP_RECKONING(N_IDNU

求oracle插入初始数据pl/sql语句优化
现有一张话务信息表，建表语句如下：
create table VIP_RECKONING
(
N_ID NUMBER(20) not null,
PHONE_NUM VARCHAR2(11),
D_ACCOUNTDATE DATE,
N_MONTHRENT NUMBER(20,2),
N_BASERATE NUMBER(20,2),
N_LONGRATE NUMBER(20,2),
N_NOTERATE NUMBER(20,2),
N_RAMBLERATE NUMBER(20,2),
N_INCREMENTRATE NUMBER(20,2),
N_OTHERRATE NUMBER(20,2),
N_PREKEEP NUMBER(20,2),
N_LIVE NUMBER(20,2)
);
comment on column VIP_RECKONING.N_ID
is '话务编号';
comment on column VIP_RECKONING.PHONE_NUM
is '手机号码';
comment on column VIP_RECKONING.D_ACCOUNTDATE
is '帐期';
comment on column VIP_RECKONING.N_MONTHRENT
is '月租费';
comment on column VIP_RECKONING.N_BASERATE
is '基本通话费';
comment on column VIP_RECKONING.N_LONGRATE
is '长途费';
comment on column VIP_RECKONING.N_NOTERATE
is '短信费';
comment on column VIP_RECKONING.N_RAMBLERATE
is '漫游费';
comment on column VIP_RECKONING.N_INCREMENTRATE
is '增值业务费';
comment on column VIP_RECKONING.N_OTHERRATE
is '其他费用';
comment on column VIP_RECKONING.N_PREKEEP
is '预存话费';
comment on column VIP_RECKONING.N_LIVE
is '余额';

初始化数据要求初始化模拟最近7个月的话务记录。客户手机号码(PHONE_NUM)从另外一张客户表(VIP_CUS_BASEINFO)取得，
客户表有模拟一万个客户.

该表让人纠结的就是一个话费余额字段，初始化时第一个月余额计算教简单，直接用
余额 = 预存话费 - 月租费 - 基本通话费 - 长途费 - 短信费 - 漫游费 - 增值业务费 - 其他费用。

非第一个月余额，还需要加入上个月余额。
余额 = 预存话费 - 月租费 - 基本通话费 - 长途费 - 短信费 - 漫游费 - 增值业务费 - 其他费用 + 上月余额

本人的插入初始化数据存储过程如下：
create or replace procedure thi_pro as
randomnum number;
randomnum2 number;
randomnum3 number;
randomnum4 number;
randomnum5 number;
randomnum6 number;
begin
for i in REVERSE 1..7 loop
randomnum:=round(dbms_random.value(0,100));--基本通话费
randomnum2:=round(dbms_random.value(0,100));--长途费
randomnum3:=round(dbms_random.value(0,100));--短信费
randomnum4:=round(dbms_random.value(0,100));--漫游费
randomnum5:=round(dbms_random.value(0,100));--增值业务费
randomnum6:=round(dbms_random.value(0,100));--其他费用

insert into VIP_RECKONING select
seq_VIP_RECKONING.nextval,
v_phone_num,
trunc(add_months(sysdate,-i),'MM'),
10,
randomnum,
randomnum2,
randomnum3,
randomnum4,
randomnum5,
randomnum6,
650,
0
from VIP_CUS_BASEINFO;

if i=7 then
update VIP_RECKONING v set v.n_live=800-10-randomnum-randomnum2-randomnum3-randomnum4-randomnum5-randomnum6;

else

update VIP_RECKONING v set v.n_live=(select 800-10-randomnum-randomnum2-randomnum3-randomnum4-randomnum5-randomnum6+
v1.n_live from VIP_RECKONING v1 where v1.phone_num=v.phone_num and
v1.d_accountdate=trunc(add_months(sysdate,-i-1),'MM'))
where v.d_accountdate=trunc(add_months(sysdate,-i),'MM');

end if;
end loop;
commit;

end;

该存储过程执行需要很久时间(将近16分钟)，应该如何改进，不知各位有何看法。

[解决办法]
我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html
[解决办法]
1.先插入数据，插入数据使用append hint和nologging;
2.在PHONE_NUM上建立索引

3.更新余额数据

SQL code

CREATE OR REPLACE PROCEDURE thi_pro AS  randomnum  NUMBER;  randomnum2 NUMBER;  randomnum3 NUMBER;  randomnum4 NUMBER;  randomnum5 NUMBER;  randomnum6 NUMBER;BEGIN  --插入数据  FOR i IN REVERSE 1 .. 7 LOOP    randomnum  := round(dbms_random.value(0, 100)); --基本通话费    randomnum2 := round(dbms_random.value(0, 100)); --长途费    randomnum3 := round(dbms_random.value(0, 100)); --短信费    randomnum4 := round(dbms_random.value(0, 100)); --漫游费    randomnum5 := round(dbms_random.value(0, 100)); --增值业务费    randomnum6 := round(dbms_random.value(0, 100)); --其他费用      INSERT /*+append*/    INTO VIP_RECKONING      SELECT seq_VIP_RECKONING.nextval,             v_phone_num,             trunc(add_months(SYSDATE, -i), 'MM'),             10,             randomnum,             randomnum2,             randomnum3,             randomnum4,             randomnum5,             randomnum6,             650,             0        FROM VIP_CUS_BASEINFO NOLOGGING;  END LOOP;  --建立索引  EXECUTE IMMEDIATE 'create index idx_PHONE_NUM on VIP_RECKONING(VIP_RECKONING)';  --更新  FOR i IN REVERSE 1 .. 7 LOOP    IF i = 7 THEN      UPDATE VIP_RECKONING v         SET v.n_live = 800 - 10 - N_BASERATE - N_LONGRATE - N_NOTERATE -                        N_RAMBLERATE - N_INCREMENTRATE - N_OTHERRATE;        ELSE          UPDATE VIP_RECKONING v         SET v.n_live =             (SELECT 800 - 10 - v.N_BASERATE - v.N_LONGRATE - v.N_NOTERATE -                     v.N_RAMBLERATE - v.N_INCREMENTRATE - v.N_OTHERRATE +                     v1.n_live                FROM VIP_RECKONING v1               WHERE v1.phone_num = v.phone_num                 AND v1.d_accountdate =                     trunc(add_months(SYSDATE, -i - 1), 'MM'))       WHERE v.d_accountdate = trunc(add_months(SYSDATE, -i), 'MM');        END IF;  END LOOP;  COMMIT;END;
[解决办法]
这句就是：INSERT /*+append*/
是一种优化指示，写法类似注释
告诉ORACLE在插入数据时使用新块

热点排行