Importance of BULK COLLECT in Oracle PL/SQL #DOTC_MDB

 BULK COLLECT IN ORACLE AND IT’S ADVATAGE IN DATABASE

 

Hello Everyone,

In this blog we will discuss about BULK collect and its importance in Oracle Database. First we need to understand what is Bulk collect and how it’s improves the performance of a improving the speed of data retrieval.

Before going through this I suggest you to please go through my previous blogs related with PL/SQL block for better understanding.

BULK Collect: BULK COLLECT reduces context switches between SQL and PL/SQL engine and allows SQL engine to fetch the records at one time only. In simple word we can say BULK COLLECT SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval. BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable. The main advantage of using BULK COLLECT is it increases the performance by reducing the interaction between SQL and PL/SQL engine to improve the performance of our program.

SYNTAX OF BULK COLLECT

SELECT <column FROM TABLE> BULK COLLECT INTO bulk_varaible  FROM <table>;

FETCH <cursor> BULK COLLECT INTO <bulk_varaible >;

Syntax is similar to PL/SQL other block but here before INTO clause we specify BULK COLLECT to reduce context switches between SQL AND PL/SQL Engine.

Note: BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable

For example let’s write one simple Bulk collect statement in PL/SQL to avoid context switching.

DECLARE

 TYPE ABC IS TABLE OF VARCHAR2(20);      -- COLLECTION NESTED TABLE

 V_NAME ABC;

BEGIN

    SELECT ENAME BULK COLLECT INTO V_NAME FROM EMP;

 FOR I IN 1..V_NAME.COUNT

LOOP

   DBMS_OUTPUT.PUT_LINE(I||' '||V_NAME(I));

END LOOP;

END;

/

Below is the output of above program , here we use collection type variable in BULK collect.

 

 

Another example of BULK Collect by using Cursor for the same program Using Different Table.

 

DECLARE CURSOR C1 IS

    SELECT EMPNAME FROM EMPLOYEE;

TYPE ABC IS TABLE OF VARCHAR2(20);

V_NAME ABC;

BEGIN

  OPEN C1;

   FETCH C1 BULK COLLECT INTO V_NAME;

   CLOSE C1;

  FOR I IN 1..V_NAME.COUNT

  LOOP

  DBMS_OUTPUT.PUT_LINE ('EMPLOYEE NAME IS ' ||' '||V_NAME(I));

   ​END LOOP;

​END;

/

 

Below is the output of above query:

 

Let’s understand LIMIT in Bulk Collect.

LIMIT :  LIMIT clause IN Oracle PL/SQL  restricts the number of rows fetched using BULK COLLECT with FETCH statement. In another word Oracle PL/SQL  has provided LIMIT clause that defines the number of records that needs to be included in the bulk.

Syntax of LIMIT in BULK COLLECT is as below :

FETCH <cursor_name> BULK COLLECT INTO <bulk_variable> LIMIT <size>;

Below is the program of BULK COLLECT using LIMIT clause .

 

DECLARE CURSOR C1 IS

    SELECT EMPNAME FROM EMPLOYEE WHERE DEPTNO IN (30,40,20);

TYPE ABC IS TABLE OF VARCHAR2(20);

V_NAME ABC;

BEGIN

  OPEN C1;

   FETCH C1 BULK COLLECT INTO V_NAME LIMIT 10;

   CLOSE C1;

  FOR I IN 1..V_NAME.COUNT

  LOOP

  DBMS_OUTPUT.PUT_LINE ('EMPLOYEE NAME IS ' ||' '||V_NAME(I));

   ​END LOOP;

​END;

/

 

Output of above program is as below:

 

 

FOR ALL STATEMENT: FORALL allows to perform the DML operations on data in bulk. It is similar to that of FOR loop statement except in FOR loop things happen at the record-level whereas in FORALL there is no LOOP concept. Instead the entire data present in the given range is processed at the same time. FOR ALL STATEMENT INSERTs, UPDATEs, and DELETEs that use collections to change multiple rows of data very quickly. No need to use loop for retrieving and performing multiple records.

Syntax of FOR ALL STATEMENT

FORALL <loop_variable>in<lower range> .. <higher range>

 

<DML operations>;

 

Below is the program by using FOR ALL STATEMENT.

DECLARE

   CURSOR C1 IS SELECT EMPNAME FROM EMPLOYEE;

   TYPE ABC IS TABLE OF VARCHAR2(100);

   V_NAME ABC;

   BEGIN

   OPEN C1;

    FETCH C1 BULK COLLECT INTO  V_NAME LIMIT 12;

    FOR I IN V_NAME.FIRST .. V_NAME.LAST

LOOP

   DBMS_OUTPUT.PUT_LINE('NAME IS ' ||I);

   END LOOP;

   FORALL J IN V_NAME.FIRST .. V_NAME.LAST

    UPDATE EMPLOYEE SET DEPTNO=40 WHERE EMPNAME=V_NAME(J);

   DBMS_OUTPUT.PUT_LINE('DEPTNO IS UPDATED TO 40');

    CLOSE C1;

  END;

/

 

Below is the output of above program.

 

 

 

 

BULK COLLECT Attributes : ATTRIBUTES are similar to cursor attributes BULK COLLECT has %BULK_ROWCOUNT(n) that returns the number of rows affected in the nth DML statement of the FORALL statement.

Advantage of BULK COLLECT and FOR ALL STATEMENT in ORACLE PL/SQL.

Every program PL/SQL developers write includes both PL/SQL and SQL statements. PL/SQL statements are run by the PL/SQL statement executor and SQL statements are run by the SQL statement executor. When the PL/SQL runtime engine encounters a SQL statement, it stops and passes the SQL statement over to the SQL engine. It uses Context Switches and Performance of the query .

 

Writing one program to see the performance of both using FOR LOOP and FOR ALL STATEMENT and see the execution time of both and see the performance .

Below is the program.

 

DECLARE

  TYPE ABC IS TABLE OF TEST.NUM%TYPE INDEX BY PLS_INTEGER;

  TYPE BCD IS TABLE OF TEST1.NAME%TYPE INDEX BY PLS_INTEGER;

   V_NUM  ABC;

   V_NAME BCD;

  ITERATIONS CONSTANT PLS_INTEGER := 500;

  T1 NUMBER;

  T2 NUMBER;

  T3 NUMBER;

BEGIN

  FOR J IN 1..ITERATIONS

LOOP 

     V_NUM(J) := J;

     V_NAME(J) := 'Part No. ' || TO_CHAR(J);

  END LOOP;

  T1 := DBMS_UTILITY.GET_TIME;

  FOR I IN 1..iterations LOOP 

     INSERT INTO TEST VALUES (V_NUM(I), V_NAME(I));

  END LOOP;

  T2 := DBMS_UTILITY.GET_TIME;

  FORALL i IN 1..iterations 

     INSERT INTO TEST1 VALUES (V_NUM(I), V_NAME(I));

  T3 := DBMS_UTILITY.GET_TIME;

  DBMS_OUTPUT.PUT_LINE('EXECUTION  IN SECOND');

  DBMS_OUTPUT.PUT_LINE('---------------------');

  DBMS_OUTPUT.PUT_LINE('FOR LOOP EXECUTION: ' || TO_CHAR((T2 - T1)/100));

  DBMS_OUTPUT.PUT_LINE('FORALL EXECUTION:   ' || TO_CHAR((T3 - T2)/100));

  COMMIT;

END;

/

 

Output of above program is as below.

 

 

Please go through this blog as BULK COLLECT and FOR ALL STATEMENT Play important role in Performance tuning . This blog is important for Fresher’s as well as experienced professional who is preparing for interview  as lots of questions asked from this topic. Also try to use Bulk collect in Sub- program and in Dynamic SQL. Please let me know if you have any questions.

Thanks.