Bulk Collect and Bulk Bind

By | December 11, 2017

Retrieve many row in single fetch and improve the speed of data retrieval by less context switch.

Oracle server used two engine to run PL/SQL block and subprogram.

1) The PL/SQL   runtime engine , which  run  all procedural line of code but passes the SQL statement to the SQL engine.

2)The SQL engine parse and executes the SQL Statements  but in some cases ,returns data to the PL/SQL engine.

  • During execution a PL/SQL statement, every SQL statement cause a context switch between the two engine. When the PL/SQL engine find the SQL statement, it stop and pass the control to SQL engine. The SQL engine execute the statement and returns back to the data in to PL/SQL engine. This transfer of control is call Context switch. Generally switching is very fast between PL/SQL engine but the context switch performed large no of time  hurt performance .
  • SQL engine retrieves all the rows and load them into the collection  and switch back to PL/SQL engine. Using bulk collect multiple row  can be fetched with single context switch.

Example 1:

 

DECLARE

    Type Emp_id_Tab IS TABLE OF Employees.employee_id%TYPE;
    Type Emp_Name_Tab IS TABLE OF Employees.first_name%TYPE;
    Type Emp_sal_Tab IS TABLE OF Employees.salary%TYPE;
    emp_id Emp_id_Tab;
    emp_name Emp_Name_Tab;
    emp_sal Emp_sal_Tab;
CURSOR c1 IS 
    select employee_id,first_name,salary from EMPLOYEES;
    BEGIN
        Open c1;
            FETCH c1 BULK COLLECT INTO emp_id,emp_name,emp_sal;
        Close c1;
     FOR x in emp_id.FIRST..emp_id.LAST
     LOOP
         DBMS_OUTPUT.PUT_LINE('Emp id is : '||emp_id(x) || ' name is :'
         ||emp_name(x) || 'and his salary is  : ' ||emp_sal(x)  );
        
     END LOOP;
    END;

Example 2:

 

DECLARE

    Type stcode_Tab IS TABLE OF demo_bulk_collect.storycode%TYPE;
    Type category_Tab IS TABLE OF demo_bulk_collect.category%TYPE;
    s_code stcode_Tab;
    cat_tab category_Tab;
    Start_Time NUMBER;
    End_Time NUMBER;
CURSOR c1 IS 
    select storycode,category from DEMO_BULK_COLLECT;
    BEGIN
       Start_Time:= DBMS_UTILITY.GET_TIME;
       FOR rec in c1
       LOOP
         NULL;
         --insert into bulk_collect_a values(rec.storycode,rec.category);
       END LOOP;
        End_Time:= DBMS_UTILITY.GET_TIME;
        DBMS_OUTPUT.PUT_LINE('Time for Standard Fetch  :-' ||(End_Time-Start_Time) ||'  Sec');

        Start_Time:= DBMS_UTILITY.GET_TIME;    
        Open c1;
            FETCH c1 BULK COLLECT INTO s_code,cat_tab;
        Close c1;
     FOR x in s_code.FIRST..s_code.LAST
     LOOP
     null;        
     END LOOP;
    End_Time:= DBMS_UTILITY.GET_TIME; 
    DBMS_OUTPUT.PUT_LINE('Using Bulk collect fetch time :-' ||(End_Time-Start_Time) ||'  Sec');
    END;

Attribute OF Bulk Collect :


1) %BULK_ROWCOUNT:-

%BULK_ROWCOUNT(i) stores the number of rows processed by the ith execution of an DML statement..
2) %BULK_EXCEPTIONS:-
It is an associative array that stores information about any exceptions encountered error when using the SAVE EXCEPTIONS clause.  
SQL%BULK_EXCEPTIONS.COUNT:  Represents the number of exceptions.
SQL%BULK_EXCEPTIONS(i).ERROR_INDEX  Specifies the  item in the collection which is throughing the exception
SQL%BULK_EXCEPTIONS(i).ERROR_CODE Specifies the Oracle error code
           FORALL i IN 1..s_code.count SAVE EXCEPTIONS
               INSERT INTO bulk_collect_b (storycode, CATEGORY)
               VALUES (s_code(i), cat_tab(i));
               EXCEPTION
                    WHEN OTHERS THEN
                    IF SQLCODE= -24381 THEN
						FOR indx IN 1..SQL%BULK_EXCEPTIONS.COUNT
						LOOP
							dbms_output.put_line(SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX||':'||SQL%BULK_EXCEPTIONS(indx).ERROR_CODE);
						END LOOP;
                    END IF

Bulk Binding :

Binds Whole arrays of values in a single operation , rather then using loop to performed  the FETCH, INSERT,UPDATE and DELETE operation multiple times.

DECLARE

    Type stcode_Tab IS TABLE OF demo_bulk_collect.storycode%TYPE index by pls_integer;
    Type category_Tab IS TABLE OF demo_bulk_collect.category%TYPE index by pls_integer;
    s_code stcode_Tab;
    cat_tab category_Tab;
    Start_Time NUMBER;
    End_Time NUMBER;

CURSOR c1 IS 
    select storycode,category from DEMO_BULK_COLLECT;
    BEGIN
         Start_Time:= DBMS_UTILITY.GET_TIME;
         OPEN c1;
            FETCH c1 BULK COLLECT INTO s_code,cat_tab;
         CLOSE c1;
          FOR x IN s_code.FIRST .. s_code.LAST
          LOOP
            insert into bulk_collect_a values(s_code(x),cat_tab(x));
          END LOOP;
           End_Time:= DBMS_UTILITY.GET_TIME;
           DBMS_OUTPUT.PUT_LINE('Normal insert :-' ||(End_Time-Start_Time) ||'  hSec');
           Start_Time:= DBMS_UTILITY.GET_TIME;
           OPEN c1;
           FORALL i IN s_code.FIRST..s_code.LAST
               INSERT INTO bulk_collect_b (storycode, CATEGORY)
               VALUES (s_code(i), cat_tab(i));
           CLOSE c1;
           End_Time:= DBMS_UTILITY.GET_TIME;
           DBMS_OUTPUT.PUT_LINE('For ALL Bulk Binding :-' ||(End_Time-Start_Time) ||'  hSec');
    END;

Topic Related Interview Question

 

Leave a Reply

Your email address will not be published. Required fields are marked *