Re: Better way (more efficient) to find existance of row
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-lReceived on Tue May 21 2013 - 18:56:46 CEST