May 28, 2009

Benefit of Cursor For loop for 10g

Which will run much slower than the other two, and why?

a.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
BEGIN
FOR employee_rec IN employees_cur LOOP
do_stuff (employee_rec);
END LOOP;
END;

b.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
l_employee employees%ROWTYPE;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur INTO l_employee;
EXIT WHEN employees_cur%NOTFOUND;
do_stuff (l_employee);
END LOOP;
CLOSE employees_cur;
END;

c.
DECLARE
CURSOR employees_cur IS SELECT * FROM employees;
TYPE employees_aat IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
l_employees employees_aat;
BEGIN
OPEN employees_cur;
LOOP
FETCH employees_cur
BULK COLLECT INTO l_employees LIMIT 100;
EXIT WHEN l_employees.COUNT () = 0;
FOR indx IN 1 .. l_employees.COUNT
LOOP
do_stuff (l_employees (indx));
END LOOP;
END LOOP;
CLOSE employees_cur;
END;

(b) Is the slowest. That's because on Oracle 10g and higher, the PL/SQL optimizer will automatically rewrite cursor FOR loops so that they are executed in the same way as the BULK COLLECT query.

No comments: