Re: Preferred way to check a record exists

"David" <auto90059_at_hushmail.com> wrote

> What's the preferred way to check whether a record actually exists in a
> table?

Ah... my prefered way :-)

--
declare
  person$  TPeople;
begin
  person$ := NEW TPeople( 'insert key here');

  if person$.Exist then
     do-some-stuff;
  end if;
end;
--

But you were looking for SQL? Right - and I did not give you any.
Reason. There is no preferred way. It depends on many factors,
including whether or not you are using PL/SQL object wrappers instead
of working directly with the tables.

Thus, if NO_DATA_FOUND does it for you, use it. If SELECT COUNT(*)
does, then use it. Two things though. Be *consistent* in your code. If
you decide on a standard, then stick to it. Secondly - make sure that
it works as you expect.

For example, doing this is great. It's using a unique index (pk) and a
single row:
SELECT
  count(*) into i$
FROM persons p
WHERE p.person_key = person_key$

However, to check if that person has ever bought something by hitting
the INVOICES table like this, is a bad idea:
SELECT
  count(*) into i$
FROM invoices i
WHERE i.person_key = person_key$

Even with an index, this could be pretty slow should that person have
a whole whack of invoices. Now imagine using an index range scan where
this is a compound index or a full table scan because there are no
index. Ouch.

One of the #1 reasons for slow performance : the assumption that the
data volume is small and/or consistent. It may be like that in
development. No guarantees in production.

And that's my 2'c. Oh yeah 1c more - have to add that if you do use
PL/SQL object wrappers you meet both criteria nicely. The developer
works with a single consistent method for that object - the EXIST
member function. The implementation of which is in a *single* place
where you can fine tune to ensure it works properly and effeciently as
required.

--
Billy