Re: Better way (more efficient) to find existance of row

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 21 May 2013 17:56:46 +0100
Message-ID: <CABe10sYpybKO9BFp9v291bpDQHXmUhy4_Nrv_br7TgbJ94_Akg_at_mail.gmail.com>

What is the vendor doing? Is it something like select 'Y' from <table> c where c.<col1> = a.<col1> and rownum = 1; if no rows then

   insert into c ...
else
  update c set ... where
end

if so then you *may* want to introduce them either to exceptions or the MERGE statement. If it is something else well then that would depend.

I like Norman's recommendation of exists as well, since it tells me immediately that this is an existence check and not a hack for when you get more than one row back..

On Tue, May 21, 2013 at 3:26 PM, Lyall Barbour <lyallbarbour_at_sanfranmail.com
> wrote:

> Hello,
> I'm trying to find out if there's a better/faster way to find the
> existence of a row.
> The vendor has this query:
> select 'Y' from <table> c where c.<col1> = a.<col1> and rownum = 1
> I made this query:
> select 'Y' from <table> c where c.<col1> = a.<col1> group by a.<col1>
> having count(a.<col1>) >= 1
> I feel like there's a better way. FYI a. table alias is from the outer
> FROM clause.
> Thanks!
> Lyall Barbour
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

-- 
Niall Litchfield
Oracle DBA
http://www.orawin.info


--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 21 2013 - 18:56:46 CEST