g_vouchernosuppl 表几千万条数据的问题

runcate gl_vouchermaxno;--删除最大号表数据(不写回滚段）
truncate gl_vouchernosuppl;--删除补号表数据(不写回滚段)
delete from gl_vouchermaxno;--删除最大号表数据
delete from gl_vouchernosuppl;--删除补号表数据
--注:如果确定要删除数据的话，删除大量数据用truncate速度很快，但有个缺点是不写回滚段，不能回滚，慎重选择。用delete的方式删除数据，写回滚段，大量数据删除的时候速度很慢。
/*以下语句根据凭证表数据重新插入凭证最大号*/
insert into gl_vouchermaxno
  (select 0,
          max(no),
          a.period,
          a.pk_glorgbook,
          max(a.pk_voucher),
          a.pk_vouchertype,
          max(ts),
          a.year
     from gl_voucher a
    where a.dr = 0
      and (a.year || a.period >
          (select s.settledyear || s.settledperiod
              from gl_syssettled s
             where s.pk_glorgbook = a.pk_glorgbook) or
          (not exists (select s.settledyear || s.settledperiod
                          from gl_syssettled s
                         where s.pk_glorgbook = a.pk_glorgbook
             and s.settledyear is not null
             and s.settledperiod is not null)))
    group by a.pk_glorgbook, a.year, a.period, a.pk_vouchertype);
/*创建一个序列，插补号表数据时用*/
create sequence sttt start with 100000000000000;
/*以下语句根据最大号表和凭证表数据查出空号，并将其插入到补号表*/
DECLARE
  v_orgbook VARCHAR2(20);
  v_year char(4);
  v_period char(2);
  v_vouchertype char(20);
  CURSOR v_cursor IS
        SELECT pk_glorgbook,year,period,pk_vouchertype FROM gl_vouchermaxno;
  v_row v_cursor%ROWTYPE;
  BEGIN
     OPEN v_cursor;
   Loop
   
    FETCH v_cursor INTO v_row;
    v_orgbook := v_row.pk_glorgbook;
    v_year := v_row.year;
    v_period :=v_row.period;
    v_vouchertype := v_row.pk_vouchertype;
    
    INSERT INTO gl_vouchernosuppl
     SELECT 2, b.NO,
          (SELECT pk_vouchermaxno
             FROM gl_vouchermaxno
            WHERE pk_glorgbook = v_orgbook
            AND YEAR = v_year
              AND period = v_period
              AND pk_vouchertype = v_vouchertype),
          substr(b.pk_glorgbook,16,20) || sttt.NEXTVAL, ts
     FROM (SELECT a.n AS NO, ts, voucher.pk_voucher,a.pk_glorgbook, nosuppl.pk_vouchermaxno
             FROM (SELECT ROWNUM AS n, ts AS ts, v_orgbook as pk_glorgbook
                     FROM gl_voucher
                    WHERE ROWNUM <=
                             (SELECT maxno
                                FROM gl_vouchermaxno
                               WHERE pk_glorgbook = v_orgbook
                                 AND YEAR = v_year
                                 AND period = v_period
                                 AND pk_vouchertype = v_vouchertype) and gl_voucher.dr=0) a
                  LEFT OUTER JOIN
                  (SELECT pk_voucher, NO
                     FROM gl_voucher
                    WHERE gl_voucher.pk_glorgbook = v_orgbook
                      AND gl_voucher.YEAR = v_year
                      AND gl_voucher.period = v_period
                      AND gl_voucher.pk_vouchertype = v_vouchertype
                      AND gl_voucher.dr = 0) voucher ON voucher.NO = a.n
                  LEFT OUTER JOIN
                  (SELECT NO, pk_vouchermaxno
                     FROM gl_vouchernosuppl
                    WHERE gl_vouchernosuppl.pk_vouchermaxno =
                             (SELECT pk_vouchermaxno
                                FROM gl_vouchermaxno
                               WHERE pk_glorgbook = v_orgbook
                                 AND YEAR = v_year
                                 AND period = v_period
                                 AND pk_vouchertype = v_vouchertype)) nosuppl
                  ON a.n = nosuppl.NO
                  ) b
    WHERE b.pk_voucher IS NULL AND pk_vouchermaxno IS NULL;
    EXIT WHEN v_cursor%NOTFOUND;
    
    end Loop;
    close v_cursor;
    end;
 /*删除序列*/
    drop sequence sttt