Bulk Collect operations (performance optimization) in PL/SQL
Page 1 of 4
APPS TO FUSION
.......Our Journey from Apps To Fusion
Home
Technical Articles
Contributors
RELATED ITEMS
FORALL operations
(performance
Training Articles
Forum
My Book
Receive Email for New Articles
Solutions
Bulk Collect operations (performance
optimization) in PL/SQL
Thursday, 01 May 2008 10:55
Anshuman Ghosh
optimization) in
PL/SQL
User Rating:
Poor
SEARCH
APPS2FUSION
/3
Best RATE
This post is relevant in context of bulk operations we do in PLSQL (mostly as part of
processes or batch jobs. A practical example is, having a cursor which returns a BIG
collection of data, which then has to be processed row by row.
SEARCH
Without using Bulk Collect
The slow (read inefficient) way to do it is to read the cursor row by
row, and process the data.
ExampleDECLARE
my_sal emp.sal%TYPE;
CURSOR c1 IS SELECT sal FROM emp WHERE job = my_job;
BEGIN
...
http://apps2fusion.com/at/gt/ag/253-bulk-collect-operations-performance-op... 2/26/2013
Bulk Collect operations (performance optimization) in PL/SQL
Page 2 of 4
OPEN c1;
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOTFOUND;
bonus := sal * 0.15;
... further processing (business logic)
...
...
END LOOP;
END;
Using Bulk Collect
When using bulk collect, the complete cursor data is fetched into a collection in 1 shot,
thereby saving of multiple reads from cursor (and the associated overhead time
consumed in context switching between SQL fetches and PLSQL program process).
ExamplePROCEDURE process_all_rows
IS
TYPE employees_collection
IS TABLE OF employees%ROWTYPE
INDEX BY PLS_INTEGER;
l_employees employees_collection;
BEGIN
SELECT *
BULK COLLECT INTO l_employees
FROM employees;
FOR indx IN 1 .. l_employees.COUNT
LOOP
my_function_call(l_employees(indx));
END LOOP;
END process_all_rows;
Performance metrics
If you wish to have real time metrics of advantages of using bulk_collect, we can do so
by timing the processes - once using normal cursor operation and once using bulk
collect.
Hits: 7991
Email This
Bookmark
Set as favorite
Comments(5)
Subscribe to this comment's feed
http://apps2fusion.com/at/gt/ag/253-bulk-collect-operations-performance-op... 2/26/2013
Bulk Collect operations (performance optimization) in PL/SQL
Page 3 of 4
How to schedule a stored procedure using dbms_scheduler (frequency 3
min )
written by Kallepalli Sridhar , May 04, 2008
Hi Anil
Can you please guide me how to use DBMS_SCHEDULER for a stored
procedure where the frquency for the program should be for every three min.
Regards
Sridhar
Votes: +1
vote up
vote down
report abuse
vote down
report abuse
...
written by nani , May 26, 2008
Hi ghosh,
Thanks a lot for guidence on bulk collect
Votes: +2
vote up
THANKS
written by needhelp , May 30, 2008
Nice explaination regarding BULK COLLECTION. I want some tips regarding
how to write query in distributed database..? how many main drawbacks
should be keep in mind when writing query.? Because due to it sometime
query takes so much time to fetch single record even.
Votes: +1
vote up
vote down
report abuse
returing plsqltable as refcursor
written by venki , June 03, 2008
how an plsql table can be used in select and i want to send it as trhrough
refcursor as out parameter can u explain it
Votes: +0
vote up
vote down
report abuse
...
written by Anshuman Ghosh , June 03, 2008
Venki,
In the bulk collect example given above, "l_employees" is indeed a pl/sql table.
TYPE employees_collection IS TABLE OF employees&#xRO;WTYPE INDEX
BY PLS_INTEGER;
l_employees employees_collection;
Followed by -
http://apps2fusion.com/at/gt/ag/253-bulk-collect-operations-performance-op... 2/26/2013
Bulk Collect operations (performance optimization) in PL/SQL
Page 4 of 4
SELECT * BULK COLLECT INTO l_employees FROM employees;
I shall post a new article on usage of refcursors..
Votes: +0
vote up
vote down
report abuse
Write comment
Name
Email
Title
Comment
smaller | bigger
Subscribe via email (Registered users only)
Write the displayed characters
Add Comment
Last Updated ( Thursday, 01 May 2008 15:21 )
http://apps2fusion.com/at/gt/ag/253-bulk-collect-operations-performance-op... 2/26/2013
