Tip#8 Cursor best practice

Cursor best practice

Oracle provides three possible way to loop through the cursor rows and these are,

1. loop with explicit cursor i.e. OPEN, FETCH one row at a time and CLOSE

2. Using cursor FOR-LOOP

3. Bulk collect and then loop through the collection.

Which of the above methods is most efficient for a cursor?

Without a doubt, you should use BULK COLLECT whenever possible to query information from your tables. It will be significantly faster than either a cursor FOR loop or loop with explicit cursor. However, a BULK COLLECT of many rows can consume a large amount of memory. But you can balance memory utilization against performance improvements by using the LIMIT clause with BULK COLLECT. Here's an example:

DECLARE
CURSOR allrows_cur
IS
SELECT *
FROM order_header_scratch;

TYPE order_header_scratch_aat IS TABLE OF order_header_scratch%ROWTYPE
INDEX BY BINARY_INTEGER;

l_order_header_scratch order_header_scratch_aat;
l_row PLS_INTEGER;
BEGIN
OPEN allrows_cur;

LOOP
FETCH allrows_cur
BULK COLLECT INTO l_order_header_scratch LIMIT 100;

-- Remember: BULK COLLECT will NOT raise NO_DATA_FOUND if no rows
-- are queried. Instead, check the contents of the collection to
-- see if you have anything left to process.
EXIT WHEN l_order_header_scratch.COUNT = 0;

-- Process the data, if any.
l_row := l_order_header_scratch.FIRST;

WHILE (l_row IS NOT NULL)
LOOP
-- Some processing ....
process_order_header_scratch (l_order_header_scratch (l_row));

-- get the next row
l_row := l_order_header_scratch.NEXT (l_row);
END LOOP;
END LOOP;

-- Clean up when done: close the cursor and delete everything
-- in the collection.
CLOSE allrows_cur;

l_order_header_scratch.DELETE;
END;

EDIT : I just happen to read an interesting article regarding cursor for-loop and would certainly suggest to read it. LINK .

Also I forgot to mention why bulk collect is faster compare to other, the biggest advantage of BULK COLLECT is: BULKING! obviously :)

In the cursor Oracle must refresh the binds every loop with the new data. Also the Insert is executed each time! With BULK COLLECT Oracle can put all the values into a collection at once. No need to refresh something again and again hence no soft parses, reading of the new values of the binds…