78万数据搬家 搬到另一张表中 用时66秒

create or replace procedure test_forall(CURRENTPAGE number ) as
--CURRENTPAGE number :=2 ;
PAGESIZE number :=3000;
type indexType_dial_main_ra is table of his_product_deal_main_ra%rowtype index by pls_integer;
index_dial_main_ra indexType_dial_main_ra ;
cursor cursor_main_ra is SELECT
 ID                    ,
DEALTYPE              ,
PRODUCTID             ,
PRODUCTCODE           ,
DEALDATE              ,
FORMCREATOR           ,
REXMUSERID            ,
DEALPRICE             ,
DEALPRICECNY          ,
DEALPRICEUSD          ,
NO                    ,
STATE                 ,
BUYERID               ,
BUYERNAME             ,
BUYERAGENTID          ,
BUYERAGENTNAME        ,
BUYERDEALERID         ,
BUYERDEALERNAME       ,
BUYERMANAGEID         ,
BUYERMANAGENAME       ,
BUYERDEALCURRENCY     ,
BUYERBP               ,
BUYEREXGRATE          ,
BUYEROUTCURRENCY      ,
BUYERSPFLAG           ,
BUYERBRIDGE           ,
BUYERFEEPRICE         ,
BUYERFEEPRICECNY      ,
BUYERFEEPRICEUSD      ,
BUYERFEEPRICERM       ,
BUYERFEEPRICERMCNY    ,
BUYERFEEPRICERMUSD    ,
BUYERMKSURE           ,
BUYERREMFLAG          ,
BUYERSTLMID           ,
BUYERSTLMTNAME        ,
BUYERSTLMTBANK        ,
BUYERSTLMTNO         ,
SELLERID             ,
SELLERNAME           ,
SELLERAGENTID        ,
SELLERAGENTNAME      ,
SELLERDEALERID       ,
SELLERDEALERNAME     ,
SELLERMANAGEID       ,
SELLERMANAGENAME     ,
SELLERDEALCURRENCY   ,
SELLERBP             ,
SELLEREXGRATE        ,
SELLEROUTCURRENCY    ,
SELLERSPFLAG         ,
SELLERBRIDGE         ,
SELLERFEEPRICE       ,
SELLERFEEPRICECNY    ,
SELLERFEEPRICEUSD    ,
SELLERFEEPRICERM     ,
SELLERFEEPRICERMCNY  ,
SELLERFEEPRICERMUSD  ,
SELLERMKSURE         ,
SELLERREMFLAG        ,
SELLERSTLMID         ,
SELLERSTLMTNAME      ,
SELLERSTLMTBANK      ,
SELLERSTLMTNO        ,
CREATORID            ,
CREATETIME           ,
MODIFIERID           ,
MODIFYTIME           ,
CHECKTIME            ,
PROCESS              ,
MANO                 ,
DEALTIMES            ,
NODEALDATE

        FROM (SELECT ra.* ,
                     ROWNUM RN
                FROM his_product_deal_main_ra ra
               WHERE ROWNUM <= (CURRENTPAGE - 1) * PAGESIZE + PAGESIZE)
       WHERE RN > (CURRENTPAGE - 1) * PAGESIZE ;
begin

open cursor_main_ra ;
loop
fetch cursor_main_ra bulk collect into index_dial_main_ra ;
--insert into test_deal_main_ra
exit when cursor_main_ra%notfound ;
end loop ;
forall i in index_dial_main_ra.first .. index_dial_main_ra.count
insert into test_deal_main_ra values (
index_dial_main_ra(i).ID                    ,
index_dial_main_ra(i).DEALTYPE              ,
index_dial_main_ra(i).PRODUCTID             ,
index_dial_main_ra(i).PRODUCTCODE           ,
index_dial_main_ra(i).DEALDATE              ,
index_dial_main_ra(i).FORMCREATOR           ,
index_dial_main_ra(i).REXMUSERID            ,
index_dial_main_ra(i).DEALPRICE             ,
index_dial_main_ra(i).DEALPRICECNY          ,
index_dial_main_ra(i).DEALPRICEUSD          ,
index_dial_main_ra(i).NO                    ,
index_dial_main_ra(i).STATE                 ,
index_dial_main_ra(i).BUYERID               ,
index_dial_main_ra(i).BUYERNAME             ,
index_dial_main_ra(i).BUYERAGENTID          ,
index_dial_main_ra(i).BUYERAGENTNAME        ,
index_dial_main_ra(i).BUYERDEALERID         ,
index_dial_main_ra(i).BUYERDEALERNAME       ,
index_dial_main_ra(i).BUYERMANAGEID         ,
index_dial_main_ra(i).BUYERMANAGENAME       ,
index_dial_main_ra(i).BUYERDEALCURRENCY     ,
index_dial_main_ra(i).BUYERBP               ,
index_dial_main_ra(i).BUYEREXGRATE          ,
index_dial_main_ra(i).BUYEROUTCURRENCY      ,
index_dial_main_ra(i).BUYERSPFLAG           ,
index_dial_main_ra(i).BUYERBRIDGE           ,
index_dial_main_ra(i).BUYERFEEPRICE         ,
index_dial_main_ra(i).BUYERFEEPRICECNY      ,
index_dial_main_ra(i).BUYERFEEPRICEUSD      ,
index_dial_main_ra(i).BUYERFEEPRICERM       ,
index_dial_main_ra(i).BUYERFEEPRICERMCNY    ,
index_dial_main_ra(i).BUYERFEEPRICERMUSD    ,
index_dial_main_ra(i).BUYERMKSURE           ,
index_dial_main_ra(i).BUYERREMFLAG          ,
index_dial_main_ra(i).BUYERSTLMID           ,
index_dial_main_ra(i).BUYERSTLMTNAME        ,
index_dial_main_ra(i).BUYERSTLMTBANK        ,
index_dial_main_ra(i).BUYERSTLMTNO         ,
index_dial_main_ra(i).SELLERID             ,
index_dial_main_ra(i).SELLERNAME           ,
index_dial_main_ra(i).SELLERAGENTID        ,
index_dial_main_ra(i).SELLERAGENTNAME      ,
index_dial_main_ra(i).SELLERDEALERID       ,
index_dial_main_ra(i).SELLERDEALERNAME     ,
index_dial_main_ra(i).SELLERMANAGEID       ,
index_dial_main_ra(i).SELLERMANAGENAME     ,
index_dial_main_ra(i).SELLERDEALCURRENCY   ,
index_dial_main_ra(i).SELLERBP             ,
index_dial_main_ra(i).SELLEREXGRATE        ,
index_dial_main_ra(i).SELLEROUTCURRENCY    ,
index_dial_main_ra(i).SELLERSPFLAG         ,
index_dial_main_ra(i).SELLERBRIDGE         ,
index_dial_main_ra(i).SELLERFEEPRICE       ,
index_dial_main_ra(i).SELLERFEEPRICECNY    ,
index_dial_main_ra(i).SELLERFEEPRICEUSD    ,
index_dial_main_ra(i).SELLERFEEPRICERM     ,
index_dial_main_ra(i).SELLERFEEPRICERMCNY  ,
index_dial_main_ra(i).SELLERFEEPRICERMUSD  ,
index_dial_main_ra(i).SELLERMKSURE         ,
index_dial_main_ra(i).SELLERREMFLAG        ,
index_dial_main_ra(i).SELLERSTLMID         ,
index_dial_main_ra(i).SELLERSTLMTNAME      ,
index_dial_main_ra(i).SELLERSTLMTBANK      ,
index_dial_main_ra(i).SELLERSTLMTNO        ,
index_dial_main_ra(i).CREATORID            ,
index_dial_main_ra(i).CREATETIME           ,
index_dial_main_ra(i).MODIFIERID           ,
index_dial_main_ra(i).MODIFYTIME           ,
index_dial_main_ra(i).CHECKTIME            ,
index_dial_main_ra(i).PROCESS              ,
index_dial_main_ra(i).MANO                 ,
index_dial_main_ra(i).DEALTIMES            ,
index_dial_main_ra(i).NODEALDATE
);

commit ;
--dbms_output.put_line(index_dial_main_ra.count);
end test_forall;
/

declare 
begin 
for i in 1..8 loop
dbms_output.put_line(i);
test_forall(i);
end loop ;
--dbms_output.put_line(round(CURRENTPAGE+1,0));
end;