Oracle Many OR vs IN ()的SQL性能调优
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8214688/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL performance tuning for Oracle Many OR vs IN ()
提问by m12345y
I dont have "explain plan" on hand. Could you help to tell which of the below is more efficient?
我手头没有“解释计划”。你能帮忙看看下面哪个更有效吗？
Option 1:
选项1：
select ...
from VIEW_ABC
where STRING_COL = 'AA'
OR STRING_COL = 'BB'
OR STRING_COL = 'BB'
OR ...
OR STRING_COL = 'ZZ'
Option 2:
选项 2：
select ...
from VIEW_ABC
where STRING_COL IN ('AA','BB',...,'ZZ')
回答by Ollie
This is a similar question: IN vs OR of Oracle, which faster?
这是一个类似的问题： IN vs OR of Oracle，哪个更快？
Look at the answer given by OMG Ponies, he suggests that INis more optimized than OR.
看看 OMG Ponies 给出的答案，他认为IN比OR.
There is also this question: IN vs OR in the SQL WHERE Clause
还有这个问题： IN vs OR in the SQL WHERE Clause
Though it is non database specific, it has a few good answers from an Oracle perspective.
虽然它不是特定于数据库的，但从 Oracle 的角度来看，它有一些很好的答案。
For what it's worth, the use of INwould 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.
就其价值而言，使用IN会更容易阅读，因此从长远来看更容易支持。仅此一项就可能影响您的选择，尤其是如果（正如我怀疑的那样）Oracle 优化器以相同的方式对待它们。
If, however, your list of values is very large then you should probably look into a totally different method for retrieving your results.
但是，如果您的值列表非常大，那么您可能应该研究一种完全不同的方法来检索您的结果。
回答by Joe Stefanelli
I would assume the optimizer would treat both versions the same. INis really just syntactic shorthand for multiple ORstatements.
我认为优化器会将两个版本相同。IN实际上只是多个OR语句的句法简写。
回答by givanse
The Oracle optimizer will convert IN-lists to OR-chains. This allows you to do the OR-expansion transformationthat can further optimize your query. Also, It might be able to generate transitive predicates that could speed up the query.
Oracle 优化器会将 IN 列表转换为 OR 链。这允许您进行OR 扩展转换，从而进一步优化您的查询。此外，它可能能够生成可以加速查询的传递谓词。
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.
让两个查询、构造或转换中的所有内容都相等，使用 IN 或 OR 之间没有区别。IN 将转换为 OR 链。
回答by Jai Bathija
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 -
如果列 String_Col 上存在一个，优化器仍将使用和索引。为了保持合适的 SQL 大小以帮助解析，因此与其让 Oracle 解析 250-500 个字符串，不如将所有可能的值放在临时表或 pl/sql 表中，然后像这样加入这个 -
Select /*+ ordered use_nl(x) */ * from pl/sql_table, view_abc x where x.string_col = pl/sql_table.value
Select /*+ordered use_nl(x) */ * from pl/sql_table, view_abc x where x.string_col = pl/sql_table.value
