Question

This question already has answers here:

IN vs OR in the SQL WHERE clause (8 answers)

Closed 10 years ago.

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

备选案文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 )

Answer 1

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

在查询OMG Ponies的答复时,他建议IN比OR更为优化。

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

尽管数据库并非具体,但它从Oracle的角度得到一些很好的答案。

对于其价值而言,使用<代码>IN将非常容易阅读,因此从长远来看更容易得到支持。单靠这样做,就能够回避你们的选择,特别是如果(我怀疑)甲骨质选择者以同样的方式对待他们。

然而,如果贵方的价值观清单非常庞大,那么你或许应当研究一种完全不同的方法,来总结你们的成果。

Answer 2

我假定最佳办法将处理这两种版本。。

Answer 3

甲骨质素优化器将把国家清单转换为OR链。这使你能够做OR-expansion transformation,以进一步优化你的询问。此外, 它可以产生能够加快询问的中转站。

在两个查询、构造或转变中,一切都相等,因此在使用本国或欧洲复兴开发银行之间没有差别。 IN将转变为OR链。

Answer 4

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

友情链接