Bulk binding was introduced in oracle 8i. it enables PL/SQL program to fetch many rows from a cursor in a single call. it also allows DML operation. Whenever PL/SQL block calls SQL statement , control transfers to the SQL engine to fetch the data from the database, and transfer fetched data and control to PL/SQL block for further processing.
Bulk binding decresses context switch between PL/SQL engine and SQL Engine that makes code execution faster.Context switch occurs every time when PL/SQL calls SQL engine to parse, execute or fetch from cursor. Since context switching uses CPU time, by reducing the number of context switching between engines incresses performance of the the program.
How does Bulk Binding Work with SELECT or FETCH Statement?
Lets take an example of cursor which is used to process individual rows returned by SQL query. This will require lot of context switching when we use cursor for loop. When we use bulk binding in our program it will fetch all the data from the database and store it in a collection data type (VARRAY, Nested table, %ROWTYPE, RECORD TYPE, CURSOR TYPE). Once data stored in the collection variable can be manuplate at OS level. This eleminates lot of context switching beween the engine when large data is concerned.
DECLARE
TYPE typ_all_objects IS TABLE OF all_objects%ROWTYPE;
tab_all_objects typ_all_objects := typ_all_objects();
l_start number;
BEGIN
l_start := dbms_utility.get_time;
FOR cur_rec IN (SELECT * FROM all_objects) LOOP
tab_all_objects.extend;
tab_all_objects(tab_all_objects.last) := cur_rec;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Simple Cursor took '|| (dbms_utility.get_time - l_start) ||
' to process '|| tab_all_objects.count||' rows; ');
l_start := dbms_utility.get_time;
SELECT *
BULK COLLECT INTO
tab_all_objects
FROM all_objects;
DBMS_OUTPUT.PUT_LINE('Bulk Collect took '|| (dbms_utility.get_time - l_start) ||
' to process '|| tab_all_objects.count||' rows; ');
END;
/
Simple Cursor took 112 to process 40786 rows;
Bulk Collect took 97 to process 40786 rows;
Why sometime it is necessary to use LIMIT clause in Bulk Binding?
Collection uses system memeory, so doing bulk collection from a large query cause a performance problem. Sometime it is necessary to use smaller chucks without grabbing all server memory. LIMIT clause helps to process data in smaller chucks.
DECLARE
TYPE typ_all_objects IS TABLE OF all_objects%ROWTYPE;
tab_all_objects typ_all_objects;
CURSOR bulk_collect_limit is
SELECT * FROM all_objects;
BEGIN
OPEN bulk_collect_limit;
LOOP
FETCH bulk_collect_limit BULK COLLECT INTO tab_all_objects LIMIT 20000 ;
EXIT WHEN tab_all_objects.COUNT = 0;
-- processing codes
dbms_output.put_line(lpad(tab_all_objects.COUNT,5,' ') || ' Rows');
END LOOP;
CLOSE bulk_collect_limit;
END;
/
20000 Rows
20000 Rows
786 Rows
