Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
4  
What are the query plans? Are the queries actually using literals 1 and 3? Or are the queries actually using bind variables. Assuming the query plans are different, my first guess would be that statistics on some object were not representative but there are any number of other possibilities. –  Justin Cave Jan 13 at 21:42
    
@JustinCave, they were literals 1 and 3, not bind variables. –  tacos_tacos_tacos Jan 13 at 22:10
4  
Perhaps there's an index on my_column that the optimizer decided not to use in the former case but did use in the latter? When was the last time you gathered stats on the tables? Any number of possibilities... but we need the real query plans... –  N West Jan 13 at 22:20
add comment

2 Answers 2

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.

share|improve this answer
add comment

Oracle Cost Based Optimizer decides if to do OR-expansion. This decision is based on the statistics of the objects involved. If these statistics are wrong it may be possible that a wrong decision is made and the resulting plan is actually worse than the best possible plan.

OR-expansion is treated in this blog entry by Maria Colgan from the Oracle Optimizer team

A reason not to do OR-expansion: if there is no index on my_column and a full table scan is necessary then doing OR-expansion is worse because it needs two tablescans /one for each query) instead of one table scan.

You can use Oracle enterprise manager or the dbms_sqltune-package to check if the values estimated by the CBO differ from the real values.

You can use the trace-event 10053 already mentioned by Adam Musch to see the CBO decisions. There are a lot of resources for this topic. One of the first papers was this paper by Wolfgang Breitling

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.