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
javapda_at_yahoo.com (jgk) 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
If you have insert or update dilemma:
if running 9i you may consider merge statement.
otherwise you can use insert with dup_val_on_index exception, or
update with sql%rowcount, depending which operation is more frequent.
Received on Tue Jun 17 2003 - 22:55:34 CDT