Search code examples
oraclefor-loopcursor

Explicit cursors using bulk collect vs. implicit cursors: any performance issues?


In an older article from Oracle Magazine (now online as On Cursor FOR Loops) Steven Feuerstein showed an optimization for explicit cursor for loops using bulk collect (listing 4 in the online article):

DECLARE
  CURSOR employees_cur is SELECT * FROM employees;
  TYPE employee_tt IS TABLE OF employees_cur%ROWTYPE INDEX BY PLS_INTEGER;
  l_employees   employee_tt;
BEGIN
  OPEN employees_cur;
  LOOP
    FETCH employees_cur BULK COLLECT INTO l_employees LIMIT 100;
    -- process l_employees  using pl/sql only
    EXIT WHEN employees_cur%NOTFOUND;
  END LOOP;
  CLOSE employees_cur;
END;

I understand that bulk collect enhances the performance because there are less context switches between SQL and PL/SQL.

My question is about implicit cursor for loops:

BEGIN
  FOR S in (SELECT * FROM employees)
  LOOP
    -- process current record of S
  END LOOP;
END;

Is there a context switch in each loop for each record? Is the problem the same as with explicit cursors or is it somehow optimized "behind the scene"? Would it be better to rewrite the code using explicit cursors with bulk collect?


Solution

  • Starting from Oracle 10g the optimizing PL/SQL compiler can automatically convert FOR LOOPs into BULK COLLECT loops with a default array size of 100.

    So generally there's no need to convert implicit FOR loops into BULK COLLECT loops.

    But sometimes you may want to use BULK COLLECT instead. For example, if the default array size of 100 rows per fetch does not satisfy your requirements OR if you prefer to update your data within a set.

    The same question was answered by Tom Kyte. You can check it here: Cursor FOR loops optimization in 10g