Bulk binding in PL/SQL

If you are PL/SQL developer, you are definitely faced with the challenge of improving the performance of your code. Managers are getting more and more obsessed with the response time of the reports and the processes that are used to load information in these reports. In field of analytics, ETL is a part that takes a lot of time for loading and refreshing data that supports decision support system of an organization.

With the introduction of bulk binding, now developers are equipped with a great weapon to tackle performance issues in a PL/SQL code. If we talk bulk binding in PL/SQL, then cursors in PL/SQL are the first thing that comes to the mind. In a PL/SQL code, if we use a cursor to fetch records and do some processing, with each iteration of cursor for loop to fetch record, the Oracle PL/SQL engine switches to SQL engine to fire query in database and fetch the records from it. This causes extra load on the database server making it slower. With use of bulk binding this switch can be reduced to a great extent.

Eg:

/*Code without bulk binding */
DECLARE
    cursor csrTest is SELECT distinct order_no,customer_no FROM order_detail;
BEGIN
    FOR rec in csrTest LOOP
    /*Do some processing with cursor records fetched*/
    END LOOP;
END;

Here if say there are 100000 records to fetch, then there will be 100000 switches.


/*Code with bulk binding */
DECLARE
    cursor csrTest is SELECT distinct order_no,customer_no FROM order_detail;
    TYPE csr_type is TABLE of csrTest%ROWTYPE;
    csrTT csr_type;
BEGIN
OPEN  csrTest;
LOOP
  FETCH csrTest BULK COLLECT into csrTT LIMIT 100;
    FORALL rec in 1..csrTT
    /*Do some processing with pl/sql table records */;
 EXIT WHEN csrTest%NOTFOUND;
 END LOOP;
END;

As here the number of fetches are reduced to 100000/100=1000 times. So the switching is reduced considerably.