RE: Better way (more efficient) to find existance of row
- From: "Powell, Mark" <mark.powell2@xxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 21 May 2013 18:11:10 +0000
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@DUT1> c /99/2/
2* where exists (select 'x' from marktest where fld2 = 2)
MPOWEL01@DUT1> /
COUNT(*)
----------
1
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Paresh Yadav
Sent: Tuesday, May 21, 2013 1:24 PM
To: niall.litchfield@xxxxxxxxx
Cc: lyallbarbour@xxxxxxxxxxxxxxx; 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@xxxxxxxxx> 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@xxxxxxxxxxxxxxx
> > 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
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> >
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.orawin.info
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: