Bulk Collect vs Cursor in Oracle

35 pts.
Tags:
BULK COLLECT statement
Collection Loop
Cursor Loop
LOOP statement
Oracle Cursors
Oracle development
Oracle queries
Hi, I have to process some records in some work table. The no. of records in work table may vary from 100,000 to 4,000,000. To process records in this work table i have to loop through it 1 by 1. So please tell me which is the better option out of these two: 1. Create Cursor for this work table and loop through it. 2. Bulk collect work table into a collection and loop through it. I tried both methods and found looping through collection is giving me very high performance than cursor. Also I can't go back in cursor so have do use some small select queries in cursor loop to archive my purpose while in collection loop and can easily navigate forward and backward. But i have heard that collection can break anytime as it consumes lot of memory. Can someone guide me, what should I use here? Or rather my question which is better between cursor loop and collection loop in my case? Thanks in advance.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Using bulk collect into a collection will be faster, but talking about that amount of records, it could be also dangerous because of the per-session memory it would consume.

In this case you could use the LIMIT clause, to fetch only a smaller group of records, process them, and then fetch another group.

Here is an example:

<pre>PROCEDURE bulk_with_limit (
dept_id_in IN employees.department_id%TYPE
, limit_in IN PLS_INTEGER DEFAULT 100
)
IS
CURSOR employees_cur
IS
SELECT *
FROM employees
WHERE department_id = dept_id_in;

TYPE employee_tt IS TABLE OF employees_cur%ROWTYPE
INDEX BY PLS_INTEGER;

l_employees employee_tt;
BEGIN
OPEN employees_cur;

<b> LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT limit_in;

FOR indx IN 1 .. l_employees.COUNT
LOOP
process_each_employees (l_employees (indx));
END LOOP;

EXIT WHEN employees_cur%NOTFOUND;
END LOOP;</b>

CLOSE employees_cur;
END bulk_with_limit;</pre>

Coincidentally, the last issue of Oracle Magazine has a great article called “On Cursor For Loops” (from which I took this example), which covers, among other things, your specific case.

I read it on a printed version, but you can read it on-line here.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Nikhildhar
    thanks Carlosdl :) while doing some research about this, I came to know that cursor resides in our temp tablespace but as we bulk collect into some collection and this collection resides in the computer memory(RAM). So while using cursor we should consider about our temp tablespace and while using Bulk collect we should consider about server RAM.
    35 pointsBadges:
    report
  • Ali2010
    Hi, I am also facing the same issue, I am using cursor to fetch million of records to join multiple tables it gives fast response comparatively. Then I use the same query to create a temp table by using oracle CATAS logic but unfortunately query becomes stuck and takes more than 48 hours. Please suggest how I can improve the execution of the process either by using cursor for loop logic or by using creating temp table. Thanks
    10 pointsBadges:
    report
  • carlosdl
    Hi Ali2010, I would recommend creating a new question to make sure your query gets the appropriate attention from the community. When doing so, make sure to provide as much details as possible. You might want to explain what you are going to do with those records, and maybe post your current code. Also, if you have already tried using collections, it would be a good idea mentioning it.
    69,065 pointsBadges:
    report
  • Michael Tidmarsh
    CarlosDL also discusses the difference in performance between a Collection loop and a Cursor loop!
    29,385 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following