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

From: Paresh Yadav <yparesh_at_gmail.com>
Date: Tue, 21 May 2013 13:23:44 -0400
Message-ID: <CAPXEL0LDbCU76xSbai2HsicWU+C0S-hgOYaX_Hi7mGsfasruKQ_at_mail.gmail.com>

Using exceptions to handle if-else is a bad idea. The performance will be slow. Better to use If else and try to put most probable outcome of the comparison in the if part.
Thanks
Paresh
Canada - 416-688-1003
USA - 408-351-6896 On Tue, May 21, 2013 at 12:56 PM, Niall Litchfield < niall.litchfield_at_gmail.com> wrote:

> 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
>
>
>

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

Received on Tue May 21 2013 - 19:23:44 CEST