5

I dont have "explain plan" on hand. Could you help to tell which of the below is more efficient?

Option 1:

select ... 
    from VIEW_ABC 
    where STRING_COL = 'AA' 
       OR STRING_COL = 'BB' 
       OR STRING_COL = 'BB' 
       OR ...
       OR STRING_COL = 'ZZ'

Option 2:

select ... 
    from VIEW_ABC 
    where STRING_COL IN ('AA','BB',...,'ZZ')
Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
m12345y
  • 73
  • 1
  • 2
  • 4
  • That question is open to any database and different optimizers will have different implementations. The accepted answer for that question is specific to MySQL. Another of the high-voted answers talks about Oracle. – givanse Aug 04 '12 at 22:40

4 Answers4

7

This is a similar question: IN vs OR of Oracle, which faster?

Look at the answer given by OMG Ponies, he suggests that IN is more optimized than OR.

There is also this question: IN vs OR in the SQL WHERE Clause

Though it is non database specific, it has a few good answers from an Oracle perspective.

For what it's worth, the use of IN would be far easier to read and therefore easier to support in the long run. That alone might sway your choice, especially if (as I suspect) the Oracle optimiser treats them both the same way.

If, however, your list of values is very large then you should probably look into a totally different method for retrieving your results.

Community
  • 1
  • 1
Ollie
  • 17,058
  • 7
  • 48
  • 59
2

I would assume the optimizer would treat both versions the same. IN is really just syntactic shorthand for multiple OR statements.

Joe Stefanelli
  • 132,803
  • 19
  • 237
  • 235
1

The Oracle optimizer will convert IN-lists to OR-chains. This allows you to do the OR-expansion transformation that can further optimize your query. Also, It might be able to generate transitive predicates that could speed up the query.

Having everything equal in two queries, constructs or transformations, there won't be a difference between using IN or OR. IN will be transformed to OR chains.

givanse
  • 14,503
  • 8
  • 51
  • 75
0

The Optimizer will still use and index, if there exists one on the column String_Col. In order to maintain a decent size of the SQL to help parsing, so rather than have Oracle parse a 250-500 character string, you are better off, putting all possible values in a temp table or a pl/sql table and then join like this -

Select /*+ ordered use_nl(x) */ * from pl/sql_table, view_abc x where x.string_col = pl/sql_table.value

Jai Bathija
  • 11
  • 1
  • But hints and a temporary table can add many other potential problems. Now the optimizer needs to understand another table. The overhead of that extra table may be greater than the reduction in parse time from a smaller query string. And if the optimizer estimates that temporary table incorrectly it can lead to problems such as not using partition pruning. If performance is the main goal, then you should provide as much information as possible to the optimizer, such as hard-coding large literals. – Jon Heller Nov 23 '11 at 06:38