As specified by Steven Feuerstein into the Oracle Blog Website the bulk processing features of PL/SQL are designed specifically to reduce the number of context switches required to communicate from the PL/SQL engine to the SQL engine.
Using BULK COLLECT plus FORALL instead of standard Insert statement to insert data improve performance dramatically, let’s me show you :
Here is a customer case on how using BULK COLLECT plus FORALL to improve Insert operations for very big tables (more than 1 billion of rows and more than 300Gb in size).
First of all, create a bigfile tablespace which will contain the data:
SQL> CREATE bigfile TABLESPACE tbs1 DATAFILE '+data' SIZE 310G;
Tablespace created.
Elapsed: 00:11:30.87
Next step is to create the table (empty) and move it to the bigfile tablespace:
CREATE TABLE "xxxx"."DBI_FK_NOPART" ( "PKEY" NUMBER(12,0) NOT NULL ENABLE, "BOID" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "METABO" NUMBER(12,0) NOT NULL ENABLE, "LASTUPDATE" TIMESTAMP (9) NOT NULL ENABLE, "PROCESSID" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "ROWCOMMENT" VARCHAR2(15 CHAR) COLLATE "USING_NLS_COMP", "CREATED" TIMESTAMP (9) NOT NULL ENABLE, "CREATEDUSER" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "REPLACED" TIMESTAMP (9) NOT NULL ENABLE, "REPLACEDUSER" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP", "ARCHIVETAG" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP", "MDBID" VARCHAR2(255 CHAR) COLLATE "USING_NLS_COMP", "ITSFORECAST" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "BETRAG" NUMBER(15,2) NOT NULL ENABLE, "ITSOPDETHERKUNFT" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP", "ITSOPDETHKERSTPRM" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP", "ITSFCKOMPPREISSEQ" VARCHAR2(40 CHAR) COLLATE "USING_NLS_COMP", "CLSFCKOMPPREISSEQ" NUMBER(12,0), "ISSUMMANDENDPREIS" NUMBER(12,0) NOT NULL ENABLE, "PARTITIONTAG" NUMBER(12,0) NOT NULL ENABLE, "PARTITIONDOMAIN" VARCHAR2(4 CHAR) COLLATE "USING_NLS_COMP" NOT NULL ENABLE, "FCVPRODKOMPPKEY" NUMBER(12,0), "FCKVPRDANKOMPPKEY" NUMBER(12,0) ) ; --MOVE TABLE TO BIGFILE TABLESPACE ALTER TABLE "xxxx"."DBI_FK_NOPART" MOVE ONLINE TABLESPACE tbs1;
Load Data with BULK COLLECT and FORALL:
SQL> declare type testarray is table of varchar2(3000) index by binary_integer; v_PKEY testarray; v_BOID testarray; v_METABO testarray; v_LASTUPDATE testarray; v_PROCESSID testarray; v_ROWCOMMENT testarray; v_CREATED testarray; v_CREATEDUSER testarray; v_REPLACED testarray; v_REPLACEDUSER testarray; v_ARCHIVETAG testarray; v_MDBID testarray; v_ITSFORECAST testarray; v_BETRAG testarray; v_ITSOPDETHERKUNFT testarray; v_ITSOPDETHKERSTPRM testarray; v_ITSFCKOMPPREISSEQ testarray; v_CLSFCKOMPPREISSEQ testarray; v_ISSUMMANDENDPREIS testarray; v_PARTITIONTAG testarray; v_PARTITIONDOMAIN testarray; v_FCVPRODKOMPPKEY testarray; v_FCKVPRDANKOMPPKEY testarray; cursor cu_cursor is select PKEY,BOID,METABO,LASTUPDATE,PROCESSID,ROWCOMMENT,CREATED,CREATEDUSER,REPLACED,REPLACEDUSER,ARCHIVETAG,MDBID,ITSFORECAST,BETRAG, ITSOPDETHERKUNFT,ITSOPDETHKERSTPRM,ITSFCKOMPPREISSEQ,CLSFCKOMPPREISSEQ,ISSUMMANDENDPREIS,PARTITIONTAG,PARTITIONDOMAIN,FCVPRODKOMPPKEY,FCKVPRDANKOMPPKEY FROM xxx.TableSource; begin dbms_output.put_line('start : '||to_char(sysdate,'dd.mm.rrrr hh24:mi:ss')); open cu_cursor; loop fetch cu_cursor bulk collect into v_PKEY,v_BOID,v_METABO,v_LASTUPDATE,v_PROCESSID,v_ROWCOMMENT,v_CREATED,v_CREATEDUSER,v_REPLACED,v_REPLACEDUSER ,v_ARCHIVETAG,v_MDBID,v_ITSFORECAST,v_BETRAG,v_ITSOPDETHERKUNFT, v_ITSOPDETHKERSTPRM ,v_ITSFCKOMPPREISSEQ ,v_CLSFCKOMPPREISSEQ,v_ISSUMMANDENDPREIS ,v_PARTITIONTAG,v_PARTITIONDOMAIN,v_FCVPRODKOMPPKEY,v_FCKVPRDANKOMPPKEY LIMIT 1000; forall i in 1 .. v_PKEY.count insert into xxx.DBI_FK_NOPART( PKEY,BOID,METABO,LASTUPDATE,PROCESSID,ROWCOMMENT,CREATED,CREATEDUSER,REPLACED,REPLACEDUSER ,ARCHIVETAG,MDBID,ITSFORECAST,BETRAG,ITSOPDETHERKUNFT, ITSOPDETHKERSTPRM ,ITSFCKOMPPREISSEQ ,CLSFCKOMPPREISSEQ,ISSUMMANDENDPREIS ,PARTITIONTAG,PARTITIONDOMAIN,FCVPRODKOMPPKEY,FCKVPRDANKOMPPKEY ) values ( v_PKEY(i),v_BOID(i),v_METABO(i),v_LASTUPDATE(i),v_PROCESSID(i),v_ROWCOMMENT(i),v_CREATED(i),v_CREATEDUSER(i),v_REPLACED(i),v_REPLACEDUSER(i),v_ARCHIVETAG(i),v_MDBID(i),v_ITSFORECAST(i),v_BETRAG(i),v_ITSOPDETHERKUNFT(i), v_ITSOPDETHKERSTPRM(i),v_ITSFCKOMPPREISSEQ(i),v_CLSFCKOMPPREISSEQ(i),v_ISSUMMANDENDPREIS(i),v_PARTITIONTAG(i),v_PARTITIONDOMAIN(i),v_FCVPRODKOMPPKEY(i),v_FCKVPRDANKOMPPKEY(i)); exit when cu_cursor%notfound; end loop; close cu_cursor; dbms_output.put_line('end : '||to_char(sysdate,'dd.mm.rrrr hh24:mi:ss')); end; / start : 15.11.2021 10:30:36 end : 15.11.2021 12:50:23 PL/SQL procedure successfully completed. Elapsed: 02:19:46.80
Gather Statistics:
exec dbms_stats.gather_table_stats('xxx','DBI_FK_NOPART');
Add primary key and indexes (store it into bigfile tablespace) :
ALTER TABLE xxx.DBI_FK_NOPART ADD CONSTRAINT PK6951_1 PRIMARY KEY (PKEY) using index tablespace tbs1; BEGIN CREATE INDEX "xxx"."CLSFCKOMPPREISSEQ695_1" ON "xxx"."DBI_FK_NOPART" ("CLSFCKOMPPREISSEQ") TABLESPACE "TBS1"; CREATE INDEX "xxx"."ITSFCKOMPPREISSEQ695_1" ON "xxx"."DBI_FK_NOPART" ("ITSFCKOMPPREISSEQ") TABLESPACE "TBS1"; CREATE INDEX "xxx"."ITSFORECAST695_1" ON "xxx"."DBI_FK_NOPART" ("ITSFORECAST") TABLESPACE "TBS1" ; CREATE INDEX "xxx"."IX_MDBID_xxx_1" ON "xxx"."DBI_FK_NOPART" ("MDBID") TABLESPACE "TBS1" ; END;
Let’s check statistics of the table :
select owner,table_name,num_rows,blocks, last_analyzed from dba_tables where table_name = 'DBI_FK_NOPART'; OWNER TABLE_NAME NUM_ROWS BLOCKS LAST_ANALYZED XXX DBI_FK_NOPART 1188403800 39871915 15.11.21
Conclusion :
With BULK COLLECT plus FORALL, I inserted more than 1 billion of rows in 02h19.
With standard Insert through a FOR LOOP statement, the Insert never finished, I stopped it after 15 hours of execution and after resizing muliple times the Undo tablespace due to “unable to extend tablespace…” error.