Oracle Query Performance

Question

As a followup to this two questions and its answers Question 1 and Question 2.

What gives better performance on Oracle using operator in, or or union? I think on a table with >1 mio rows with 10-500 rows per distinct value of y.

select * from table where y in (1,2,3)

or

select * from table where (y = 1 or y = 2 or y = 3)

or

select * from table where y = 1
union
select * from table where y = 2
union
select * from table where y = 3

How is performance influenced if I add a and x = 1? Column x is indexed as well and has same selectivity.

Summary: There is no best practise or advise to use one operator more over the others. In best case all 3 statements have same performance metrics. But it can be that one statement is better than the others.

There are some threads out there that discuss performance measure of sql statements on oracle:

• http://forums.oracle.com/forums/thread.jspa?threadID=501834
• http://asktom.oracle.com/tkyte/runstats.html
• http://stackoverflow.com/questions/1312663/how-to-measure-performance-of-query-in-oracle

The tools you have to deal with are execution plans and tracing tools to obtain best performance.

Answer 1

Do some tracing and study the execution plans and you will know. Read: http://forums.oracle.com/forums/thread.jspa?threadID=501834

Answer 2

A UNION ALL will normally perform better than a UNION as a UNION is a set operation and will remove duplicates by default (as will MINUS and INTERSECT). The UNION ALL means it doesn't do that de-duplication step. In your specific example, since all columns (including Y) are selected and the Y values are, by definition, different for the result sets for each of the three SELECTs, then the de-duplication is unnecessary.

The general rule is, write the query that answers the question you are asking. Then check the explain plan, and see if it is appropriate. Only if if the plan is sub-optimal should you start to worry about how you can re-write the query to improve the plan.

Answer 3

Hi Christian,

I wholeheartedly agree with Theo, you should learn to answer questions like this.

There is an utility on the AskTom site that is easy to use and that will tell you which of any two queries/procedures is faster and which use more resources.

Answer 4

Check this out. This too might be of help. If possible display your results as answer. Might gain valuable knowledge for all of us