when using cursor to interate over resultset, will the all the resultset be loaded into memory

Question

If resultset is huge, will it be memory-efficient to use cursor?

Answer 1

In general, you should try to use "set" SQL statements that operate on a set of records based upon a query rather than use a cursor and loop through the data. Most DBAs believe that cursors are evil because they take up too much memory, involve locks which prevent other tasks from executing, and are also much more inefficient (sometimes by several orders of magnitude) than a well thought out SQL statement.

Answer 2

Technically, a cursor is always involved when you're issuing a database query. You're probably thinking of the difference between an implicit and explicit cursor?

As far as memory use is concerned, it's not the method you issue the query that matters, it's how you fetch results - you have a choice to fetch results in batches (which uses more memory, but performs better and more efficiently) or fetch each row individually (which uses less memory, but performs worse and less efficiently).

However, the trade-off is not as simple as that. If you have a large number of concurrent connections, all running queries at the same time, efficiency can be more important than memory usage - the sooner a query has finished, the sooner the database is free to serve other requests.

If I'm trying to optimise my PL/SQL for performance and memory usage, I like using a BULK COLLECT, with a LIMIT within a loop, e.g.:

DECLARE
  BATCHSIZE CONSTANT INTEGER := 1000;
  CURSOR mycursor IS SELECT ...;
  TYPE mytabletype IS TABLE OF mycursor%ROWTYPE INDEX BY PLS_INTEGER;
  myarray mytabletype;
BEGIN
  OPEN mycursor;
  LOOP
    FETCH mycursor INTO myarray LIMIT BATCHSIZE;
    EXIT WHEN myarray.COUNT = 0;
    FOR i IN 1..myarray.COUNT LOOP
      -- do the processing on myarray(i)
    END LOOP;
  END LOOP;
  CLOSE mycursor;
END;

The above code makes it simple to adjust the batch size to maximise throughput without using too much memory per session.