求oracle插入初始数据pl/sql语句优化

Question

现有一张话务信息表，建表语句如下：
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分钟)，应该如何改进，不知各位有何看法。

Answer 1

trunc(add_months(sysdate,-i-1),'MM');
800-10-randomnum-randomnum2-randomnum3-randomnum4-randomnum5-randomnum6;
这两个计算的值用变量存储一下，不要重复计算了多次，对效率会有提升。

Answer 2

1.先插入数据，插入数据使用append hint和nologging;
2.在PHONE_NUM上建立索引
3.更新余额数据
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;

Answer 3

不好意思，现在看明白2楼的意思了，每次循环都计算一次，确实有点消耗效率了。我更改一下。
3楼的大大，append hint能帮忙解释一下不，我对数据库还是一知半解，nologging应该是不记录日志吧，这个词在你的语句里面有看到，但是append hint好像没看到呢

Answer 4

这句就是：INSERT /*+append*/
是一种优化指示，写法类似注释
告诉ORACLE在插入数据时使用新块