PL/SQL BULK Processing allows to batch or group number of records and sends in batches to a database for processing.
How PL/SQL Works?
PL/SQL is procedural language extension for SQL. Each command is executed in sequence. PL/SQL code is executed by PL/SQL Engine while SQL by SQL Engine. It is efficiently managed by Oracle Database internally so that you never notice any lag in execution.
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?
In this article, let’s see how fast and efficient is pl sql bulk processing.
Do you really get the benefit of using bulk processing? We will compare run time of sequential and bulk method.
Create Dummy Table
Let’s create a dummy table which is an exact copy of DBA_OBJECTS. Use below script
CREATE TABLE XXDBA_OBJECTS as SELECT * FROM DBA_OBJECTS WHERE 1=2;
Total Record Count in DBA_OBJECTS:- 353426
Below are some examples. You can use DBMS_UTILITY.get_cpu_time to print time before and after calling the procedure. Set timing on also print the execution time.
Example 1 – Direct Insert command
SET timing ON; BEGIN INSERT INTO XXDBA_OBJECTS SELECT * FROM DBA_OBJECTS; END; Elapsed: 00:00:50.213
Example 2 – Insert using BULK Collect and BULK Insert
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
Example 3 – Insert in Normal for Loop
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.
RUN TIME DETAILS
|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|
Above result clearly, show the difference between normal and bulk processing. BULK Processing really efficient. You should always use below approach in sequence when dealing with huge records.
- Direct insert
- Bulk processing
- Normal for loop