Explicit vs Implicit cursors


Pick any tuning manual, and you'll often see a section about coding with explicit cursors as opposed to implicit ones. The argument seems sound, typically going along the lines of:

"With an explicit cursor, you can open, fetch and close, whereas with an implicit cursor must issue open, fetch, second fetch, close - the second fetch being needed to check for a TOO_MANY_ROWS exception.

However, since 7.3, those cunning people at Oracle have worked around this problem and the second fetch is no longer required. Because PL/SQL is interpreted (watch out for the new native compile option in 9i), implicit cursors will actually run FASTER than explicit since typically you are using less code to achieve the same task.

You can try the following test for yourself:

REM
REM First a temporary table
REM
create table blah ( x number primary key ) ;

insert into blah
select rownum x 
from sys.source$
where rownum < 1000;

REM
REM Make sure its got the full set of statistics
REM
analyze table blah compute statistics;

REM
REM A procedure to do approx 50,000 implicit reads
REM
create or replace procedure imp_test is
 y number;
begin
  for i in 1 .. 50 loop
    for j in 1 .. 999 loop
        select x
        into   y
        from   blah 
        where  x = j;
    end loop;
  end loop;
end;
/

REM
REM A procedure to do approx 50,000 explicit reads
REM
create or replace procedure exp_test is
  cursor c(p number) is
    select x 
    from blah
    where x = p;
 y number;
begin
  for i in 1 .. 50 loop
    for j in 1 .. 999 loop
       open c(j);
       fetch c into y;
       close c;
    end loop;
  end loop;
end;
/

REM
REM We give each one a run to prime the buffers
REM
exec imp_test
exec exp_test

REM
REM And now we see who is king of the roost...
REM
set timing on
exec imp_test
exec exp_test