建表：

create table sm_histable(    sm_id number(10),    sm_subid number(3),    service_type varchar2(6),    orgton number(3),    orgnpi number(3),    destton number(3),    destnpi number(3),    destaddr varchar2(21),    pri number(3),    pid number(3),    srr number(3),    dcs number(3),    schedule varchar2(21),    expire varchar2(21),    final varchar2(21),    sm_status number(3),    error_code number(3),    udl number(3),    sm_type number(10),    id_hint number(10));

普通INSERT操作：

create or replace procedure redo1 isTYPE T_SM_ID     IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;TYPE T_SM_SUBID IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER;TYPE T_ORGADDR     IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER;TYPE T_DESCADDR IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER;TYPE T_ID_HINT     IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;V_SM_ID T_SM_ID;V_SM_SUBID T_SM_SUBID;V_ORGADDR T_ORGADDR;V_DESTADDR T_DESCADDR;V_ID_HINT T_ID_HINT;I INTEGER;VREDO1 INTEGER;VREDO2 INTEGER;BEGIN   FOR I IN 1..2000   LOOP           V_SM_ID(I):=I;           V_SM_SUBID(I):=12;           V_ORGADDR(I):='444555565';           V_DESTADDR(I):='555555';           V_ID_HINT(I):=I;   END LOOP;   select value into VREDO1 FROM v$sysstat where name='redo size';   for I in 1..2000 loop       INSERT INTO IIMAX.SM_HISTABLE (SM_ID,SM_SUBID,expire,DESTADDR,ID_HINT)       VALUES (V_SM_ID(I),V_SM_SUBID(I),V_ORGADDR(I),V_DESTADDR(I),V_ID_HINT(I));   END LOOP;COMMIT;COMMIT;select value into VREDO2 from v$sysstat where name = 'redo size';select value into VREDO2 from v$sysstat where name = 'redo size';dbms_output.put_line('redo size:' || to_char(VREDO2-VREDO1));end;/

BULK操作

create or replace procedure redo2 isTYPE T_SM_ID     IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;TYPE T_SM_SUBID IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER;TYPE T_ORGADDR     IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER;TYPE T_DESCADDR IS TABLE OF VARCHAR2(21) INDEX BY BINARY_INTEGER;TYPE T_ID_HINT     IS TABLE OF NUMBER(10) INDEX BY BINARY_INTEGER;V_SM_ID T_SM_ID;V_SM_SUBID T_SM_SUBID;V_ORGADDR T_ORGADDR;V_DESTADDR T_DESCADDR;V_ID_HINT T_ID_HINT;I INTEGER;VREDO1 INTEGER;VREDO2 INTEGER;N integer;BEGIN    N:=2000;   FOR I IN 1..N   LOOP           V_SM_ID(I):=I;           V_SM_SUBID(I):=12;           V_ORGADDR(I):='444555565';           V_DESTADDR(I):='555555';           V_ID_HINT(I):=I;   END LOOP;   select value into VREDO1 FROM v$sysstat where name='redo size';   FORALL I in 1..N       INSERT INTO IIMAX.SM_HISTABLE (SM_ID,SM_SUBID,expire,DESTADDR,ID_HINT)       VALUES (V_SM_ID(I),V_SM_SUBID(I),V_ORGADDR(I),V_DESTADDR(I),V_ID_HINT(I));COMMIT;COMMIT;select value into VREDO2 from v$sysstat where name = 'redo size';select value into VREDO2 from v$sysstat where name = 'redo size';dbms_output.put_line('redo size:' || to_char(VREDO2-VREDO1));end;/

SQL> set serveroutput onSQL> select current_scn from v$database;CURRENT_SCN-----------    1291237SQL> exec redo1;redo size:673688PL/SQL procedure successfully completed.

SQL> select current_scn from v$database;CURRENT_SCN-----------    1291424SQL> exec redo2;redo size:119592PL/SQL procedure successfully completed.SQL> select current_scn from v$database;CURRENT_SCN-----------    1291439

 

SQL> exec redo1;

redo size:673688

SQL> exec redo2;

redo size:119592

可以对比看到BULK操作产生的redo量要小很多。更多BULK操作介绍可参考