for与forall效率比较

首先建立两张表，account、account1

create table ACCOUNT
(
  N                   NUMBER not null,
  UP                  NUMBER,
  LANG                NUMBER,
  FD                  DATE not null,
  TD                  DATE not null,
  CATEGORY            NUMBER not null,
)

create table ACCOUNT1
(
  N                   NUMBER not null,
  UP                  NUMBER,
  LANG                NUMBER,
  FD                  DATE not null,
  TD                  DATE not null,
  CATEGORY            NUMBER not null,
)

account表有430W数据，现通过过程分别以for及forall将数据从表account复制到account1。

注：这里只是用这两张表作实验。如需进行表达复制，推荐如下操作：

create table account1 as select * from account where 1=0;

alter table account1 nologging;

insert  /*+append*/ into account1 select * from account;

如机器性能好，可打开并行模式。

alter session enable parallel DML;

insert  /*+append*/ /*+parallel(account1,4)*/ into account1 select * from account;--打开4个进程并行插入

 

好，回归正题，首先看for模式

create or replace procedure p_insert_acct
as
  type t_tab_n is table of account.n%type index by binary_integer;
  type t_tab_up is table of account.up%type index by binary_integer;
  type t_tab_lang is table of account.lang%type index by binary_integer;
  v_tab_n t_tab_n;
  v_tab_up t_tab_up;
  v_tab_lang t_tab_lang;

  v_tab_n_new t_tab_n;
  v_tab_up_new t_tab_up;
  v_tab_lang_new t_tab_lang;

begin
  select a.n,a.up,a.lang bulk collect into v_tab_n, v_tab_up,v_tab_lang
    from account a;

   for idx in v_tab_n.first..v_tab_n.last
    loop
      insert into account1(n,up,lang,fd,td,category)
      values (v_tab_n(idx),v_tab_up(idx),v_tab_lang(idx),
              to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
              to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
              trunc(dbms_random.value(1,5)));  
      if (mod(idx,50000)=0) then
        commit;
      end if;         
    end loop;

end;

插入数据耗时为25.719s

forall模式：

create or replace procedure p_insert_acct
as
  type t_tab_n is table of account.n%type index by binary_integer;
  type t_tab_up is table of account.up%type index by binary_integer;
  type t_tab_lang is table of account.lang%type index by binary_integer;
  v_tab_n t_tab_n;
  v_tab_up t_tab_up;
  v_tab_lang t_tab_lang;

  v_tab_n_new t_tab_n;
  v_tab_up_new t_tab_up;
  v_tab_lang_new t_tab_lang;

begin
  select a.n,a.up,a.lang bulk collect into v_tab_n, v_tab_up,v_tab_lang
    from account a;

    forall idx in v_tab_n.first..v_tab_n.last
      insert into account1(n,up,lang,fd,td,category)
      values (v_tab_n(idx),v_tab_up(idx),v_tab_lang(idx),
              to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
              to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
              trunc(dbms_random.value(1,5)));     
end;
插入数据耗时为7.313s，效率约为for模式的3～4倍。

 

如需插入数据时，进行逻辑处理，可先进行数组赋值，然后使用forall进行插入：

create or replace procedure p_insert_acct
as
  type t_tab_n is table of account.n%type index by binary_integer;
  type t_tab_up is table of account.up%type index by binary_integer;
  type t_tab_lang is table of account.lang%type index by binary_integer;
  v_tab_n t_tab_n;
  v_tab_up t_tab_up;
  v_tab_lang t_tab_lang;

  v_tab_n_new t_tab_n;
  v_tab_up_new t_tab_up;
  v_tab_lang_new t_tab_lang;

begin
  select a.n,a.up,a.lang bulk collect into v_tab_n, v_tab_up,v_tab_lang
    from account a;

  begin
   for idx in v_tab_n.first..v_tab_n.last
   loop
    v_tab_n_new(idx):=v_tab_n(idx)||1111;
    v_tab_up_new(idx):=v_tab_up(idx)||1;
    v_tab_lang_new(idx):=v_tab_lang(idx)||1;
   end loop;
  end;
 
  --forall idx in v_tab_n.first..v_tab_n.last
  forall idx in INDICES  of v_tab_n_new
  insert /*+append*/ into account1(n,up,lang,fd,td,category)
  values (v_tab_n_new(idx),v_tab_up_new(idx),v_tab_lang_new(idx),
          to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
          to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J'),
          trunc(dbms_random.value(1,5)));
end;