Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Where Clauses: IN versus =

Re: Where Clauses: IN versus =

From: DA Morgan <damorgan_at_psoug.org>
Date: Mon, 12 Sep 2005 09:19:21 -0700
Message-ID: <1126541911.223770@yasure>


Tim Marshall wrote:
> I'm writing a lot of code in a various apps to construct strings which
> are Oracle SQL statements.
>
> I'm wondering if there is a performance hit with the use of in() versus
> equals. I know for multiple criteria that, for example:
>
> Where FK_Whatever in (123, 124, 16, 4)
>
> is preferable to:
>
> Where FK_Whatever = 123 or FK_Whatever = 124 or FK_Whatever = 16 or
> FK_Whatever = 4
>
> But what about when I have a single criteria? Is:
>
> Where FK_Whatever = 123
>
> faster/preferable to
>
> Where FK_Whatever in (123)?
>
> The reason I ask is because it is much easier to simply write the code
> to construct an in() rather than test to see how many criteria there
> are. I can and have done the latter for years, but wonder if I am just
> wasting my efforts.
>
> Any comment is appreciated, thanks very much in advance.

If your question was

WHERE some_column = 10
versus
WHERE some_column IN (10)

your question would make sense.

But how can you compare the time and overhead of a single value to multiple values?

The solution is to test with your code on your system with your data.

There are almost never stock answers good for all.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)
Received on Mon Sep 12 2005 - 11:19:21 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US