Bulk Collect In Oracle

Bulk Collect In Oracle

Definition of Bulk Collect:- Bulk binds can improve the performance when loading collections from a queries. The BULK COLLECT INTO construct binds the output of the query to the collection. To test this create the following table.

below example will help you to understand how it will improve performance.

create a table to understand how bulk collection is working.

CREATE TABLE bulk_collect_test AS
SELECT owner,
       object_name,
       object_id

FROM   all_objects;

Below code will understand how normal code and bulk code will improve performance.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab := t_bulk_collect_test_tab();

  l_start  NUMBER;

BEGIN

  -- Time a regular population.

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *

                  FROM   bulk_collect_test)

  LOOP

    l_tab.extend;

    l_tab(l_tab.last) := cur_rec;

  END LOOP;

  DBMS_OUTPUT.put_line('Regular (' || l_tab.count || ' rows): ' || 

                       (DBMS_UTILITY.get_time - l_start));

  

  -- Time bulk population.  

  l_start := DBMS_UTILITY.get_time;

  SELECT *

  BULK COLLECT INTO l_tab

  FROM   bulk_collect_test;

  DBMS_OUTPUT.put_line('Bulk    (' || l_tab.count || ' rows): ' || 

                       (DBMS_UTILITY.get_time - l_start));

END;

/

Regular (42578 rows): 66

Bulk    (42578 rows): 4

PL/SQL procedure successfully completed.

SQL>

we can see how much regular time has taken and bulk time has taken, it will improve more performance.Remember that collections are held in memory, so doing a bulk collect from a large query could cause a considerable performance problem

we can use by another method by using Limit option in bulk collections.This gives you the benefits of bulk binds, without hogging all the server memory. The following code shows how to chunk through the data in a large table.

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab t_bulk_collect_test_tab;

  CURSOR c_data IS

    SELECT *

    FROM bulk_collect_test;

BEGIN

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 10000;

    EXIT WHEN l_tab.count = 0;

    -- Process contents of collection here.

    DBMS_OUTPUT.put_line(l_tab.count || ' rows');

  END LOOP;

  CLOSE c_data;

END;

/

10000 rows

10000 rows

10000 rows

10000 rows

2578 rows

PL/SQL procedure successfully completed.

SQL>

So we can see that with a LIMIT 10000 we were able to break the data into chunks of 10,000 rows, reducing the memory footprint of our application

from 10g on words PL/SQL compiler converts cursor FOR LOOPs into BULK COLLECTs with an array size of 100. 

Below example will give you clear idea how cursor for loop and bulk collection will work .

SET SERVEROUTPUT ON

DECLARE

  TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;

  l_tab    t_bulk_collect_test_tab;

  CURSOR c_data IS

    SELECT *

    FROM   bulk_collect_test;

  l_start  NUMBER;

BEGIN

  -- Time a regular cursor for loop.

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT *

                  FROM   bulk_collect_test)

  LOOP

    NULL;

  END LOOP;

  DBMS_OUTPUT.put_line('Regular  : ' || 

                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 10.

  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 10;

    EXIT WHEN l_tab.count = 0;

  END LOOP;

  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 10 : ' || 

                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 100.

  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 100;

    EXIT WHEN l_tab.count = 0;

  END LOOP;

  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 100: ' || 

                       (DBMS_UTILITY.get_time - l_start));

  -- Time bulk with LIMIT 1000.

  l_start := DBMS_UTILITY.get_time;

  OPEN c_data;

  LOOP

    FETCH c_data

    BULK COLLECT INTO l_tab LIMIT 1000;

    EXIT WHEN l_tab.count = 0;

  END LOOP;

  CLOSE c_data;

  DBMS_OUTPUT.put_line('LIMIT 1000: ' || 

                       (DBMS_UTILITY.get_time - l_start));

END;

/

Regular  : 18

LIMIT 10 : 80

LIMIT 100: 15

LIMIT 1000: 10

PL/SQL procedure successfully completed.

SQL> 

You can see from this example the performance of a regular FOR LOOP is comparable to a BULK COLLECT using an array size of 100.