Re: Which statement would be most efficient?

[the dragon <ceprn@HOTMAIL.COM>]

Let me further qualify this by saying the exists is faster if you are using
an index - as in using the keys.  If no index is being used then having a
single database tag for the data and using an IN might be as fast or faster
- I am sure it depends on the size of the table and number of rows returned,
and other factors like the phase of the moon and the depth of the tides in
the persian gulf multiplied by the dow jones industrial average...

clark 'the dragon' willis
dragon enterprises, consulting services


PSA: Salary <> Slavery.  If you earn a salary, your employer is renting your
services for 40 hours a week, not purchasing your soul.  Your time is the
only real finite asset that you have, and once used it can never be
recovered, so don't waste it by giving it away.

"Time is the coin of your life. It is the only coin you
have, and only you can determine how it will be spent.
Be careful lest you let other people spend it for you."

Carl Sandburg
(1878 - 1967)


----Original Message Follows----

In Oracle at least, using rule based optimization, exists or not exists is
usually faster (see exception) that an IN for 2 reasons: 1) the exists
returns a true condition and stops processing as soon as it occurs where as
an IN builds the values by going through the entire table to collect all the
data 2) an IN statement is an explicit OR statement - such as IN (1, 2, 3)
is the same thing as = 1 OR = 2 OR = 3, and it will end up running the
gammut for each data particle tested.

exception - when the data row that returns a true is the last row in the
table, then there is not difference.




----Original Message Follows----

Exists tend to be more efficient than IN statements.  I would run a test
with an explain plan and see if you are using the best path and can you
optimize either by changing your SQL statement to match the indexes that
exists or build a new index to run with this statement.



_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.