CODEWITHSUNDEEP
sqlplsqloracle11g
AppleGrew
AppleGrew

Reputation: 9570

Which is better - Cursor For Loop or Loop over PLSQL collection populated by Bulk Collect?

Which of them is more performant.

Version 1 uses Cursor For Loop

DECLARE
  total_val number(6);

  CURSOR c1 IS
  SELECT * FROM emp
  ;
BEGIN
  total_val := 0;
  FOR emp_rec IN c1
  LOOP
    total_val := total_val + emp_rec.sal;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('TOTAL SALARIES: ' || total_val);
END;

Version 2 first uses Bulk Collect to get all rows into PLSQL collection then iterates over it.

DECLARE
  total_val number(6);

  CURSOR c1 IS
  SELECT * FROM emp
  ;

  TYPE emp_recs_type IS TABLE OF emp%ROWTYPE;
  emp_recs emp_recs_type;
BEGIN
  total_val := 0;

  OPEN c1;
  FETCH c1 BULK COLLECT INTO emp_recs;
  CLOSE c1;

  FOR l_index IN emp_recs.FIRST..emp_recs.LAST
  LOOP
    total_val := total_val + emp_recs(l_index).sal;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('TOTAL SALARIES: ' || total_val);
END;

Please assume that the Cursor might return many rows, could be in 10s of thousands or more.

Upvotes: 3

Views: 1645

Answers (1)

Tony Andrews
Tony Andrews

Reputation: 132600

The best way to find out is really to set up an experiment and try it.

Of course for your example the best code would be:

DECLARE
  total_val number(6);
BEGIN
  SELECT SUM(sal) INTO total_val FROM emp;

  DBMS_OUTPUT.PUT_LINE('TOTAL SALARIES: ' || total_val);
END;

However, I realise this is just a simple example!

In theory using BULK COLLECT should be better, but in fact these days (certainly in 11G) Oracle silently bulk fetches under the covers anyway in batches of 100 rows - see this AskTom thread.

If using BULK COLLECT yourself and there may be an unspecified number of rows you should use a LIMIT clause, otherwise you could hit memory issues. For your example this would be something like:

DECLARE
  total_val number(6);

  CURSOR c1 IS
  SELECT * FROM emp
  ;

  TYPE emp_recs_type IS TABLE OF emp%ROWTYPE;
  emp_recs emp_recs_type;
BEGIN
  total_val := 0;

  OPEN c1;

  LOOP
    FETCH c1 BULK COLLECT INTO emp_recs LIMIT 100;

    EXIT WHEN c1.COUNT = 0;

    FOR l_index IN emp_recs.FIRST..emp_recs.LAST
    LOOP
      total_val := total_val + emp_recs(l_index).sal;
    END LOOP;

  END LOOP;

  DBMS_OUTPUT.PUT_LINE('TOTAL SALARIES: ' || total_val);
  CLOSE c1;
END;

Upvotes: 2

Related Questions