Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: count(*) vs. a cursor when determining the existing record on primary key
Ethel Aardvark wrote:
>
> Even a simple "select into" is less efficient than a single cursor.
> Why? If there are two or more rows, the "select into" needs to
> interrogate the DB to find this out to raise the error, the cursor
> method simply does not care.
>
> That said, I gather the PL/SQL and SQL engines are far more tightly
> integrated in version 9.
>
> The key thing that I picked up from my training with Steven was that
> if you are not sure which method is best, create a test-harness and
> try each one out!
>
> ETA
>
> "Scott Mattes" <Scott_at_TheMattesFamily.ws> wrote in message news:<OoqHa.10828$Jw6.4343412_at_news1.news.adelphia.net>...
> > I can't wait to see what the experts say, but I thunked on it a few and
> > this is what I came up with.
> >
> > With a count With a local var
> > and exception With a cursor
> >
> > declare | declare |
> > declare
> > | |
> > l count integer( 1 ); | l key my table.p key%type; |
> > l key my table.p key%type;
> > | |
> > begin | begin |
> > cursor c check for key is
> > | |
> > select p key
> > select count(* ) | select p key |
> > from my table
> > into l count | into l key |
> > where p key = 'a value';
> > from my table | from my table |
> > where p key = 'a value' | where p key = 'a value' |
> > begin
> > and row count = 1; | and row count = 1; |
> > | |
> > open c check for key;
> > if l count = 0 | do something found; |
> > then | |
> > fetch c check for key
> > do something not found | exception |
> > into l key;
> > else | when no data found |
> > do something found | then | if
> > c check for key%notfound
> > end if; | do something wasn't found |
> > then
> > | |
> > do something not found
> > end; | end; |
> > else
> > |
> > do something found
> > | end
> > if;
> > |
> >
> > |
> > close c check for key;
> > |
> >
> > | end;
> >
> > Personally, I prefer the first example, using a count.
> >
> >
> > "jgk" <javapda_at_yahoo.com> wrote in message
> > news:6851e45e.0306161018.1e31fa7f_at_posting.google.com...
> > > If you have a table with a primary key and you want to determine if a
> > > particular primary key exists what is the fastest way to accomplish
> > > this?
> > >
> > > In Steve Feuerstein's great book "Oracle PL/SQL Best Practices"
> > > SQL-08: Use COUNT only when the actual number of occurrences is
> > > needed...he gives some case(s) where a cursor should be used instead
> > > of the count(*) function.
> > >
> > > If you have a table. The table has a primary key field. If you want
> > > to test for existence of a matching primary key is it better to use
> > > the COUNT function and see if there is a match > 0 (it would always be
> > > 1 or zero)...or should a cursor be used?
> > >
> > > Thanks,
> > >
> > > JGK
> > --
actually i think you'll find that implicit cursors are just as fast (in fac typically faster) due an optimization that gets two rows in a single fetch all the back to version 7
if you trace 'select into' you will parse=1, exec=1, fetch=1 in the trace file even in the 'too_many_rows' case
hth
connor
-- ========================= Connor McDonald http://www.oracledba.co.uk "Some days you're the pigeon, some days you're the statue"Received on Wed Jun 18 2003 - 05:01:16 CDT