How efficient is PL/SQL Bulk Processing? Does it really help…….

/ Database / By

PL/SQL is procedural language extension for SQL. It has it’s own commands, datatype, operator, etc. You can use SQL statements within PL/SQL code. Here, PL/SQL code is executed by PL/SQL Engine while SQL by SQL Engine. You will never notice any lag as  Oracle database manages this engine switch very efficiently

This is fine for thousands of records. What if you are dealing with millions of records? There would be millions switch between two engine and can add significant execution time, isn’t it? You can use BULK processing feature available in PL/SQL when processing huge number of records. The bulk data processing actually speed up the operation.

In this article, we are going to see,

  1. How efficient in PL SQL bulk processing?
  2. Do you really get any benefit by using bulk processing?

We will create a dummy table and insert records using for loop, direct insert and using bulk insert. Let’s capture the processing time also for each operation. We can use the run time of the operation to identify which one is best and when to use normal for loop, direct insert or PL/SQL bulk collect.

So let’s get started…….

Create a dummy table

Create a dummy table which is an exact copy of DBA_OBJECTS using the script given below,

CREATE TABLE XXDBA_OBJECTS as SELECT * FROM DBA_OBJECTS WHERE 1=2;

Total Records Count in DBA_OBJECTS:- 353426

You can use DBMS_UTILITY.get_cpu_time to print time before and after calling the procedure. I am going to use Set timing on available in SQL * Plus.

1. Direct Insert Into command in PL/SQL

Run the below script which insert data from DBA_OBJECTS to XXDBA_OBJECTS table by using direct SQL insert statement.

SET timing ON;

BEGIN
 INSERT INTO XXDBA_OBJECTS
 SELECT * FROM DBA_OBJECTS;
 END;

Elapsed: 00:00:50.213

Time taken by this script is around – 00:00:50.213

2. Insert using PL/SQL BULK Collect and Insert

Next, use below script which uses BULK Collect and BULK Insert operation.

SET timing ON;
DECLARE
TYPE l_tbl_objects
IS
 TABLE OF DBA_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
 l_objects l_tbl_objects;
BEGIN
 SELECT db.* BULK COLLECT INTO l_objects
 FROM DBA_OBJECTS db;
 FORALL i IN 1..l_objects.count
 INSERT INTO XXDBA_OBJECTS VALUES
 l_objects(i)
 ;
END;


PL/SQL procedure successfully completed.

Elapsed: 00:00:56.231

Time taken by this script is around -00:00:56.231

3. Insert using Normal PL/SQL for Loop

Use below script to insert data using normal for loop available in PL/SQL. Here, each record is inserted in sequence one by one.

SET timing ON;
DECLARE
 CURSOR get_data
 IS
 SELECT db.* FROM DBA_OBJECTS db;
BEGIN
 FOR rec_data IN get_data
 LOOP
 INSERT INTO XXDBA_OBJECTS VALUES rec_data;
 END LOOP;
END;
PL/SQL procedure successfully completed.

Elapsed: 00:01:08.598
Below table shows data with descreasing run time.

Final Comparison – Which is Best Method Direct Insert, BULK or For Loop?

Below method summarize the run time for all the execution methods.

Method Runtime
Direct Insert command 00:00:50.213
Insert using BULK Collect and BULK Insert 00:00:56.231
Insert in Normal for Loop 00:01:08.598

What we can say from above observation,

Above result clearly, show the difference between normal and bulk processing. BULK Processing really efficient compared to for loop.  Direct insert wins the race here.

To conclude, you should always use below approach in sequence when dealing with huge records.

  • Direct insert
  • Bulk processing
  • Normal for loop