Re: Implicit vs Explicit cursors re-visited

In article <r3sc6tggfu9sl8hgtod73olj083m40k1vf_at_4ax.com>,   Doug <zuestra_at_hotmail.com> wrote:
> According to some after-market documentation I have read.. ALWAYS USE
 EXPLICIT
> cursors..(PL/SQL) this implies that
>
> (psuedo-code)
> cursor c1 is select * from A
> open c1;
> for x in c1
> insert into B x.1 x.2 etc.,
>
> is preferable to the much more simple
>
> insert into B select * from A; (that's it.. one line)...
>
> The reason sited is that memory for the cursor is not closed for an
 implicit
> cursor..(until?)
>
> Can anyone elaborate?
> Some preliminary benchmarks indicate that method 2 (implicit) seems
 to be a bit
> faster... could this be misleading? or is there another trade off
 I'm missing?
>
> This is Oracle 8.1.5 (certainly could be upgraded)
> on AIX 4.3.3
>
> Thanks..
>
>

I was reading something along these lines in 'Oracle Pl/SQL Programming" by Steven Feuerstein. His comments are

"..implicit cursor have the following drawbacks:

[] it less efficient than an explicit cursor ( Release 2.2 or earlier)
[] is more vulnerable to data errors
[] gives you less programmatic control "

"The inefficiency is now the other way around in Release 2.3 and up. Implicit cursor are optimized and in isolation will run faster thatn the corresponding explicit cursor. Generally the differenece between these tow approaches from a performance standpoint are negligible."

He goes into greater detail about this.

His suggestion is always use explicit cursors for selects. From personal experience, I believe this to be a good programming practice. I allows for finer control and a natural extension from selecting 1 row to selecting many. And avoids ugly problems. Again personal experience indicates that performance is not apparently an issue.

For your particular problem, the 'insert .. select .. from ..' is much more efficient than issuing multiple 'insert' commands. Similarly, updating 10 fields in one statement is better than 10 updates of each single field.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/