OK, one more yes-no. I tried this for performance and as expected, it is
faster than count on MS and Sybase (and count is very fast on MS). You might
see if there is a variation of it you can use with the Oracle Decode
function....I can't answer if there is or not, however in either case even
though this is probably faster, it won't be portable between some dbms's.
SELECT CASE
WHEN EXISTS(SELECT somecolumn FROM customers WHERE columnname ='thisvalue')
then 1
ELSE 0
END
(I'm installing Oracle on Solaris right now. I'm anxious to get my hands on
it. I've only been off of mainframes for a couple of years, and haven't used
it, but hear some interesting things, like trim() in the where clause causes
indexes to not be used, and count causes a table scan. I'm sure it's all
accurate, but it certainly poses some challenges I've never thought about
having to deal with).
Anyway, just add that one to the possibilities of testing for the existence
from within a Perl script.
Steve H.
-----Original Message-----
From: Steven Lembark [mailto:[EMAIL PROTECTED]]
Sent: Saturday, July 07, 2001 6:16 PM
To: [EMAIL PROTECTED]
Subject: RE: Checking for the existence of a certain row.
- Steve Howard <[EMAIL PROTECTED]> on 07/07/01 17:54:18 -0500:
> do a
> SELECT COUNT(*) FROM <tablename> WHERE ...
>
> A count is almost always faster than actually returning that row, and
> requires even less network bandwidth to return the result. Of course, it a
> result of 1 is returned - the row exists.
Don't try this in Oracle, it immediately degenerates into a
table scan. MySql may handle this differently but selecting
the key (or the first field with restriction on the PK) will
be equally fast w/o fewer risks.
sl
