In Oracle, why would an OR / LIKE(A,B) take far longer than running the same query for each condition separately and unioning I have a complicated subquery that looks something like this

SELECT ...
FROM ...
WHERE
   my_column IN (1,3)


It takes a long time to run.

I tried splitting it out into:

SELECT ...
FROM ...
WHERE
   (my_column = 1 OR my_column = 3)


and that had no impact on the performance.

However, when I run:

SELECT ...
FROM ...
WHERE
   my_column = 1


and

SELECT ...
FROM ...
WHERE
   my_column = 3


Each runs quickly separately.

And when I union the results:

SELECT ...
FROM ...
WHERE
   my_column = 1
UNION ALL -- or UNION
SELECT ...
FROM ...
WHERE
   my_column = 3


It also runs quickly.

My question is, why would this EVER be the case?
If you're not clearing the buffer cache between runs, you don't know how much faster the query's running due to optimization and how much of that is due to the data already being cached.  Using the AUTOTRACE functionality in SQL*Plus allows one to see how much work (physical reads and logical reads) go into obtaining the result set.

Setting event 10053 to show to the optimization of the SQL statement would show why Oracle made the choice it did to not perform OR-expansion, which is what transforming a query with an IN condition or an OR statement into a UNION ALL query of disjoint queries.

Without more data, I suspect my_column is indexed, but you don't have a histogram on it.  A histogram would tell Oracle how relatively common or rare those values are in your table.  If that doesn't fix it, the USE_CONCAT hint can force Oracle to change Query 2 above into Query 5.