SQL Oracle 的 IN 与 OR，哪个更快？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6514906/
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
IN vs OR of Oracle, which faster?
提问by ?inh H?ng Chau
I'm developing an application which processes many data in Oracle database.
In some case, I have to get many object based on a given list of conditions, and I use SELECT ...FROM.. WHERE... IN..., but the INexpression just accepts a list whose size is maximum 1,000 items.
我正在开发一个应用程序来处理 Oracle 数据库中的许多数据。
在某些情况下，我必须根据给定的条件列表获取许多对象，并且我使用SELECT ...FROM.. WHERE... IN...，但该IN表达式只接受大小最多为 1,000 个项目的列表。
So I use ORexpression instead, but as I observe -- perhaps this query (using OR) is slower than IN(with the same list of condition). Is it right? And if so, how to improve the speed of query?
所以我改用OR表达式，但正如我所观察到的——也许这个查询（使用OR）比IN（使用相同的条件列表）慢。这样对吗？如果是这样，如何提高查询速度？
回答by OMG Ponies
INis preferable to OR-- ORis a notoriously bad performer, and can cause other issues that would require using parenthesis in complex queries.
IN更可取的是OR--OR是一个众所周知的糟糕执行者，并且可能导致需要在复杂查询中使用括号的其他问题。
Better option than either INor OR, is to join to a table containing the values you want (or don't want). This table for comparison can be derived, temporary, or already existing in your schema.
比INor更好的选择OR是加入包含您想要（或不想要）的值的表。这个用于比较的表可以是派生的、临时的或已经存在于您的架构中。
回答by WW.
In this scenario I would do this:
在这种情况下，我会这样做：
- Create a one column global temporary table
- Populate this table with your list from the external source (and quickly - another whole discussion)
- Do your query by joining the temporary table to the other table (consider dynamic sampling as the temporary table will not have good statistics)
- 创建一列全局临时表
- 使用来自外部来源的列表填充此表（并快速 - 另一个完整的讨论）
- 通过将临时表连接到另一个表来进行查询（考虑动态采样，因为临时表不会有很好的统计数据）
This means you can leave the sort to the database and write a simple query.
这意味着您可以将排序留给数据库并编写一个简单的查询。
回答by jva
Oracle internally converts IN lists to lists of ORs anyway so there should really be no performance differences. The only difference is that Oracle has to transform INs but has longer strings to parse if you supply ORs yourself.
无论如何，Oracle 在内部将 IN 列表转换为 OR 列表，因此实际上应该没有性能差异。唯一的区别是 Oracle 必须转换 IN，但如果您自己提供 OR，则需要解析更长的字符串。
Here is how you test that.
这是您测试的方法。
CREATE TABLE my_test (id NUMBER);
SELECT 1
FROM my_test
WHERE id IN (1,2,3,4,5,6,7,8,9,10,
21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35,36,37,38,39,40,
41,42,43,44,45,46,47,48,49,50,
51,52,53,54,55,56,57,58,59,60,
61,62,63,64,65,66,67,68,69,70,
71,72,73,74,75,76,77,78,79,80,
81,82,83,84,85,86,87,88,89,90,
91,92,93,94,95,96,97,98,99,100
);
SELECT sql_text, hash_value
FROM v$sql
WHERE sql_text LIKE '%my_test%';
SELECT operation, options, filter_predicates
FROM v$sql_plan
WHERE hash_value = '1181594990'; -- hash_value from previous query
SELECT STATEMENT
TABLE ACCESS FULL ("ID"=1 OR "ID"=2 OR "ID"=3 OR "ID"=4 OR "ID"=5 OR "ID"=6 OR "ID"=7 OR "ID"=8 OR "ID"=9 OR "ID"=10 OR "ID"=21 OR "ID"=22 OR "ID"=23 OR "ID"=24 OR "ID"=25 OR "ID"=26 OR "ID"=27 OR "ID"=28 OR "ID"=29 OR "ID"=30 OR "ID"=31 OR "ID"=32 OR "ID"=33 OR "ID"=34 OR "ID"=35 OR "ID"=36 OR "ID"=37 OR "ID"=38 OR "ID"=39 OR "ID"=40 OR "ID"=41 OR "ID"=42 OR "ID"=43 OR "ID"=44 OR "ID"=45 OR "ID"=46 OR "ID"=47 OR "ID"=48 OR "ID"=49 OR "ID"=50 OR "ID"=51 OR "ID"=52 OR "ID"=53 OR "ID"=54 OR "ID"=55 OR "ID"=56 OR "ID"=57 OR "ID"=58 OR "ID"=59 OR "ID"=60 OR "ID"=61 OR "ID"=62 OR "ID"=63 OR "ID"=64 OR "ID"=65 OR "ID"=66 OR "ID"=67 OR "ID"=68 OR "ID"=69 OR "ID"=70 OR "ID"=71 OR "ID"=72 OR "ID"=73 OR "ID"=74 OR "ID"=75 OR "ID"=76 OR "ID"=77 OR "ID"=78 OR "ID"=79 OR "ID"=80 OR "ID"=81 OR "ID"=82 OR "ID"=83 OR "ID"=84 OR "ID"=85 OR "ID"=86 OR "ID"=87 OR "ID"=88 OR "ID"=89 OR "ID"=90 OR "ID"=91 OR "ID"=92 OR "ID"=93 OR "ID"=94 OR "ID"=95 OR "ID"=96 OR "ID"=97 OR "ID"=98 OR "ID"=99 OR "ID"=100)
选择语句
表访问已满（“ID”=1 或“ID”=2 或“ID”=3 或“ID”=4 或“ID”=5 或“ID”=6 或“ID”=7 或“ID”= 8 OR “ID”=9 OR “ID”=10 OR “ID”=21 OR “ID”=22 OR “ID”=23 OR “ID”=24 OR “ID”=25 OR “ID”=26 OR "ID"=27 OR "ID"=28 OR "ID"=29 OR "ID"=30 OR "ID"=31 OR "ID"=32 OR "ID"=33 OR "ID"=34 OR "ID "=35 OR "ID"=36 OR "ID"=37 OR "ID"=38 OR "ID"=39 OR "ID"=40 OR "ID"=41 OR "ID"=42 OR "ID"= 43 OR “ID”=44 OR “ID”=45 OR “ID”=46 OR “ID”=47 OR “ID”=48 OR “ID”=49 OR “ID"=50 OR "ID"=51 OR "ID"=52 OR "ID"=53 OR "ID"=54 OR "ID"=55 OR "ID"=56 OR "ID"=57 OR "ID" =58 OR “ID”=59 OR “ID”=60 OR “ID”=61 OR “ID”=62 OR “ID”=63 OR “ID”=64 OR “ID”=65 OR “ID”=66 OR "ID"=67 OR "ID"=68 OR "ID"=69 OR "ID"=70 OR "ID"=71 OR "ID"=72 OR "ID"=73 OR "ID"=74 OR " ID"=75 OR "ID"=76 OR "ID"=77 OR "ID"=78 OR "ID"=79 OR "ID"=80 OR "ID"=81 OR "ID"=82 OR "ID" =83 OR "ID"=84 OR "ID"=85 OR "ID"=86 OR "ID"=87 OR "ID"=88 OR "ID"=89 OR "ID"=90 OR "ID"=91 OR "ID"=92 OR "ID"=93 OR "ID"=94 OR "ID"=95 OR "ID"=96 OR "ID"=97 OR "ID" =98 或“ID”=99 或“ID”=100)
回答by oazabir
I would question the whole approach. The client of the SP has to send 100000 IDs. Where does the client get those IDs from? Sending such a large number of ID as the parameter of the proc is going to cost significantly anyway.
我会质疑整个方法。SP 的客户端必须发送 100000 个 ID。客户端从哪里获得这些 ID？发送如此大量的 ID 作为 proc 的参数无论如何都会花费大量成本。
回答by TrevorH
If you create the table with a primary key:
如果您使用主键创建表：
CREATE TABLE my_test (id NUMBER,
CONSTRAINT PK PRIMARY KEY (id));
and go through the same SELECTs to run the query with the multiple IN values, followed by retrieving the execution plan via hash value, what you get is:
并通过相同的 SELECT 运行具有多个 IN 值的查询，然后通过哈希值检索执行计划，您得到的是：
SELECT STATEMENT
INLIST ITERATOR
INDEX RANGE SCAN
This seems to imply that when you have an IN list and are using this with a PK column, Oracle keeps the list internally as an "INLIST" because it is more efficient to process this, rather than converting it to ORs as in the case of an un-indexed table.
这似乎意味着当您有一个 IN 列表并且将它与一个 PK 列一起使用时，Oracle 会在内部将该列表保留为“INLIST”，因为处理它更有效，而不是像在这种情况下将其转换为 OR未编入索引的表。
I was using Oracle 10gR2 above.
我在上面使用 Oracle 10gR2。
