Re: Implicit vs. Explicit cursors

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/30
Message-ID: <8h0r79$gro$1@nnrp1.deja.com>#1/1

>>>
>>> In our current project we discovered some bugs which we would never
 have
>>> discovered with explicit cursors (too_many_rows exceptions where
 they
 were
>>> not supposed to occur and which could not be prevented by means of
 unique
>>> constraints...).
>>>
>>> Marc
>>>
>>
>>That would only occur if you use the explicit cursor in a manner that
>>does not check for TOO_MANY_ROWS.
>>
>
>
>I thought one of the main reasons to replace implicit by explicit
 cursors
>was the fact that implicit cursors will always check for extra rows,
 which
>decreases performance (extra fetch needed). Especially in case of slow
>network connections, this can be a problem.
>Now you suggest that we should perform that extra check also in case of
>explicit cursors.
>
>Marc
>
>

Well, firstly -- if you use an EXPLICIT cursor to "skip" that second check --
you have replaced one piece of logic with a very different piece of logic. they
are *not* equivalent.

and secondly -- the double fetch is optimized to take but one trip. It has no
overhead as far as that goes.

Remember, if you have code like:

declare

   l_cnt number;
begin

   select count(*) into l_cnt from T;
end;
/

you would have to write code like:

declare

   cursor c is select count(*) from t;
   l_cnt number;
   l_dummy number;
begin

   open c;
   fetch c into l_cnt;
   if ( c%notfound )
   then

      raise no_data_found;
   end if;
   fetch c into l_dummy;
   if ( NOT c%notfound )
   then

      raise too_many_rows;
   end if;
   close c;
end;
/

to get the same functionality. Anything less is simply *not the same*. The
second fetch is really needed in most cases (don't know how many times I've seen
programs blow up with too_many_rows when they were never expecting to --  its
saved lots of time finding data related errors or errors in logic resulting from
someone thinking the data they were using was unique)...

Implicit cursors are in general, as fast if not faster....

ops$tkyte_at_8i> set timing on
ops$tkyte_at_8i> set echo on
ops$tkyte_at_8i>
ops$tkyte_at_8i>
ops$tkyte_at_8i> declare
  2          l_ename varchar2(25);
  3  begin
  4          for i in 1 .. 10000 loop
  5                  select ename into l_ename from emp where rownum =
1;
  6          end loop;

  7 end;
  8 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.36
ops$tkyte_at_8i>
ops$tkyte_at_8i> declare

  2          l_ename varchar2(25);
  3          cursor c1 is select ename from emp;
  4  begin
  5          for i in 1 .. 10000 loop
  6                  open c1;
  7                  fetch c1 into l_ename;
  8                  close c1;
  9          end loop;

 10 end;
 11 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:02.87
ops$tkyte_at_8i>

ops$tkyte_at_8i> declare

  2     cursor c is select ename from emp where rownum = 1;
  3     l_ename varchar2(25);
  4     l_dummy varchar2(25);
  5  begin
  6      for i in 1 .. 10000 loop
  7             open c;
  8             fetch c into l_ename;
  9             if ( c%notfound )
 10             then
 11                raise no_data_found;
 12             end if;
 13             fetch c into l_dummy;
 14             if ( NOT c%notfound )
 15             then
 16                raise too_many_rows;
 17             end if;
 18             close c;
 19      end loop;

 20 end;
 21 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.12

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Tue May 30 2000 - 00:00:00 CDT