rrudnik
12/23/2014 - 3:03 PM

Standard Fetch and BULK Fetch Cursors Comparison

Standard Fetch and BULK Fetch Cursors Comparison

declare
  type onameTab is table of all_objects.object_name%type index by binary_integer;
  l_owner onameTab;
  l_object onameTab;

CURSOR c is SELECT owner, object_name
            FROM all_objects;

l_start number;
l_end number;

begin
  l_start := dbms_utility.get_time;
  for x in c loop
    null;
  end loop;
  l_end := dbms_utility.get_time;
  dbms_output.put_line('Time for Standard Fetch ' || (l_end - l_start) || ' hsec');

  l_start := dbms_utility.get_time:
  open c;
  loop
    EXIT When C%NOTFOUND;
    fetch c BULK COLLECT into l_owner, l_object_name;
  end loop;
  close c;
  l_end := dbms_utility.get_time;
  dbms_output.put_line('Time for BULK Fetch ' || (l_end - l_start) || ' hsec');
end;