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,
- How efficient in PL SQL bulk processing?
- 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.
|Direct Insert command
|Insert using BULK Collect and BULK Insert
|Insert in Normal for Loop
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