Cursor best practice
Oracle provides three possible way to loop through the cursor rows and these are,
- loop with explicit cursor i.e. OPEN, FETCH one row at a time and CLOSE
- Using cursor FOR-LOOP
- 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…
Posts
