Re: Implicit vs Explicit cursors re-visited

I'd give you another advice. Always use plain SQL (no pl/sql) if you can do it. Insert as select ALWAYS works faster, and gain is significant, 3 to 9 times, depending on tables layout. Update with subquery doesn't give you that kind of gain, but faster anyway. The reason for it that sql is a native database language, it built into server. PL/SQL has a separate engine. So, as you can imagine, one engine execution is faster than 2-engine! Even implicit cursors are faster than explicit, because you have less engine interaction. One more advice though. Most of aftermarket  documentation is not very reliable. Along with good information it often contains some misinterpretations of authors experience, common programming legends and other stuff which has a little to do with reality. Rule of thumb: if after-market documentation contradicts original one, stick with original.

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..
>
>

Sent via Deja.com
http://www.deja.com/ Received on Thu Jan 18 2001 - 22:11:30 CST