Skip to content
Home » Comparison – Which is the best method Direct Insert, Bulk or For Loop in Oracle

Comparison – Which is the best method Direct Insert, Bulk or For Loop in Oracle

PL/SQL is a procedural language extension for SQL. It has its 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 is by SQL Engine. You will never notice any lag as the 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 of switches between two engines and can add significant execution time, isn’t it? What to do?

You can use the BULK processing feature available in PL/SQL or direct insert when processing a huge number of records. The bulk data processing actually speeds up the operation.  Which do you think is better?

In this article, we are going to see, which is the best method Direct Insert, Bulk, or For Loop in Oracle.

Direct Insert, Bulk, or For Loop, Which is better?

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 normally for loop, direct insert, or PL/SQL bulk collect.

So let’s get started…….

1. Create 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 the Set timing available in SQL * Plus.

2. Direct Insert Into command

Run the below script which inserts data from DBA_OBJECTS to XXDBA_OBJECTS table by using a 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 direct insert is around – 00:00:50.213

3. Insert using BULK Collect and Insert

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

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 bulk collect and insert script is around – 00:00:56.231

4. Insert using Normal for Loop

Use the below script to insert data using the 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

Time taken by insert using for loop – 00:01:08.598

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

The below table summarizes 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 you can say from the above observation,

The above result clearly, shows the difference between normal and bulk processing. Direct insert and bulk processing really efficient(50%) compared to for loop.  Direct insert wins the race here as it takes less time.

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

  1. Direct insert
  2. Bulk processing
  3. Normal for loop