Originally posted by Billy Verreynne
> Andy wrote:
>
> > You could try SELECT 'X' FROM DUAL WHERE EXISTS( SELECT 'X' FROM
> table).
>
> Oh yeah.. forgot too mention.
>
> Andy, I take a lead pipe to any developer that use EXISTS in SQL
> on any of
> the databases I look after. :-)
>
> Few developers know how to correctly use it. I have witnessed numerous
> performance horrors because of abusing EXIST.. like doing an
> UPDATE and in
> the WHERE clause use an EXISTS. For every single row matching
> the short
> circuit boolean evaluation in the WHERE clause, a sub SQL
> select is run.
> You have a lot of rows to update, or the sub SQL does an index
> range scan
> or even a join... performance cease to exist.
>
> My opinion - never use EXISTS unless you have a pretty darn good
> reason why
> _nothing_ else but an EXISTS will do the job. IMO, your example
> does not
> necessitate the use of an EXISTS.
>
> --
> Billy
Maybe it doesn't "necessitate" using EXISTS, but in this case EXISTS
happens to be just as good a solution as yours. Seems a bit rough to
criticise Andy for using EXISTS properly just because you have known
others to use it improperly! I have also known people to use COUNT(*)
improperly (like WITHOUT your ROWNUM=1 restriction to check for
existence): shall I take a lead pipe to you ;-)
--
Posted via http://dbforums.com
Received on Fri May 30 2003 - 07:38:34 CDT
