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

I like to use count(*) into variable when the results are used in pl/sql since you can write the query so that count always returns a value of 0 or 1 for no rows or rows found respectively. Keep in mind that with exists that while Oracle can terminate the sub-query as soon as a matching value is found that if no value is found then Oracle has to search until it knows that no value can be found. In the absence of an index this would mean a full table scan. With a larger table with the target values being well distributed this could result in very fast query execution for hits and very slow no hit response times. Meaning to prevent uneven query performance, the exists sub-query usually should have access to an index.

MPOWEL01> l
  1 select count(*) from sys.dual
  2* where exists (select 'x' from marktest where fld2 = 99) MPOWEL01> /   COUNT(*)

         0

MPOWEL01_at_DUT1> c /99/2/
  2* where exists (select 'x' from marktest where fld2 = 2) MPOWEL01_at_DUT1> /

  COUNT(*)

         1

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paresh Yadav Sent: Tuesday, May 21, 2013 1:24 PM
To: niall.litchfield_at_gmail.com
Cc: lyallbarbour_at_sanfranmail.com; oracle-l Subject: Re: Better way (more efficient) to find existance of row

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


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