Re: count(*) vs. a cursor when determining the existing record on primary key

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
> --
Received on Tue Jun 17 2003 - 05:27:02 CDT