Re: loop

Karen Abgarian wrote:
>
> > You'll find a lot of people saying that explicity declaring cursors is
> > faster than not, ie they will claim that
> >
> > cursor X is select ...
> > open
> > fetch
> > close
> >
> > is faster then
> >
> > select ..
> > into variable
> > from table
> >
> > Ask them to prove it...they'll struggle :-)
> >
> > hth
> > connor
> > --
> > ==============================
> > Connor McDonald
> >
> > http://www.oracledba.co.uk
> >
> > "Some days you're the pigeon, some days you're the statue..."
>
> I think Jonathan Lewis was saying this in his book.
>
> The idea is that for the second one, Oracle has to make sure that the
> second row is NOT returned from the query, because an
> TOO_MANY_ROWS exception is also some piece of information
> that can be returned from the query. The first just does a single fetch,
> the second does two.
>
> IMO makes a lot of sense

Try putting a trace on and look at the results...You'll see (from 7.3 onwards, possibly even earlier) that a second fetch is not required.

A quick look in JL's book and I cannot find any references in the PL/SQL or Tuning chapters that make any reference to the statements you've made.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."