IN vs OR of Oracle, which faster?

Question

I'm developing an application which processes many data in Oracle database.
In some case, I have to get many object based on a given list of conditions, and I use SELECT ...FROM.. WHERE... IN..., but the IN expression just accepts a list whose size is maximum 1,000 items.

So I use OR expression instead, but as I observe -- perhaps this query (using OR) is slower than IN (with the same list of condition). Is it right? And if so, how to improve the speed of query?

Answer 1

IN is preferable to OR -- OR is a notoriously bad performer, and can cause other issues that would require using parenthesis in complex queries.

Better option than either IN or OR, is to join to a table containing the values you want (or don't want). This table for comparison can be derived, temporary, or already existing in your schema.

Answer 2

In this scenario I would do this:

1. Create a one column global temporary table
2. Populate this table with your list from the external source (and quickly - another whole discussion)
3. Do your query by joining the temporary table to the other table (consider dynamic sampling as the temporary table will not have good statistics)

This means you can leave the sort to the database and write a simple query.