SQL IN 子句如何影响 oracle 中的性能？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4957942/
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
How does IN clause affect performance in oracle?
提问by Vivek
UPDATE table1
SET col1 = 'Y'
WHERE col2 in (select col2 from table2)
In the above query, imagine the inner query returns 10000 rows. Does this query with IN clause affect performance?
在上面的查询中，假设内部查询返回 10000 行。这个带有 IN 子句的查询会影响性能吗？
If so, what can be done for faster execution?
如果是这样，可以做些什么来加快执行速度？
回答by Vincent Malgrat
if the subquery returns a large number of rows compared to the number of rows in TABLE1, the optimizer will likely produce a plan like this:
如果子查询返回的行数比 TABLE1 中的行数多，优化器可能会生成如下计划：
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 300K| 24M| | 1581 (1)| 00:0
| 1 | UPDATE | TABLE1 | | | | |
|* 2 | HASH JOIN SEMI | | 300K| 24M| 9384K| 1581 (1)| 00:0
| 3 | TABLE ACCESS FULL| TABLE1 | 300K| 5860K| | 355 (2)| 00:0
| 4 | TABLE ACCESS FULL| TABLE2 | 168K| 10M| | 144 (2)| 00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("COL2"="COL2")
It will scan both tables once and update only the rows in TABLE1 common to both tables. This is a highly efficient plan if you need to update lots of rows.
它将扫描两个表一次，并仅更新两个表共有的 TABLE1 中的行。如果您需要更新大量行，这是一个高效的计划。
Sometimes the inner query will have few rows compared to the number of rows in TABLE1. If you have an index on TABLE1(col2), you could then obtain a plan similar to this one:
有时，与 TABLE1 中的行数相比，内部查询的行数很少。如果您在 上有索引TABLE1(col2)，则可以获得与此类似的计划：
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 93 | 4557 | 247 (1)| 00:00:03 |
| 1 | UPDATE | TABLE1 | | | | |
| 2 | NESTED LOOPS | | 93 | 4557 | 247 (1)| 00:00:03 |
| 3 | SORT UNIQUE | | 51 | 1326 | 142 (0)| 00:00:02 |
| 4 | TABLE ACCESS FULL| TABLE2 | 51 | 1326 | 142 (0)| 00:00:02 |
|* 5 | INDEX RANGE SCAN | IDX1 | 2 | 46 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T1"."COL2"="T2"."COL2")
In that case Oracle will read the rows from TABLE2 and for each (unique) row, perform an index access on TABLE1.
在这种情况下，Oracle 将从 TABLE2 中读取行，并且对于每个（唯一的）行，对 TABLE1 执行索引访问。
Which access is faster depend upon the selectivity of the inner query and the clustering of the index on TABLE1 (are the rows with similar value of col2in TABLE1 next to each other or randomly spread?). In any case, performance wise, if you need to perform this update this query is one of the fastest way to do it.
哪个访问更快取决于内部查询的选择性和 TABLE1 上索引的集群（TABLE1 中具有相似值的行col2是彼此相邻还是随机分布？）。在任何情况下，性能方面，如果您需要执行此更新，此查询是执行此操作的最快方法之一。
回答by zerkms
UPDATE table1 outer
SET col1 = 'Y'
WHERE EXISTS (select null
from table2
WHERE col2 = outer.col2)
This could be better
这可能会更好
To get the idea which is better - look at the execution plan.
要了解哪个更好 - 查看执行计划。
回答by tbone
From Oracle:
来自甲骨文：
11.5.3.4 Use of EXISTS versus IN for Subqueries
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.
11.5.3.4 子查询使用 EXISTS 与 IN
在某些情况下，最好使用 IN 而不是 EXISTS。一般来说，如果选择性谓词在子查询中，则使用 IN。如果选择性谓词在父查询中，则使用 EXISTS。
From my experience, I have seen better plans using EXISTS where subquery returns large amount of rows.
根据我的经验，我已经看到使用 EXISTS 的更好的计划，其中子查询返回大量行。
See herefor more discussion from Oracle
有关Oracle 的更多讨论，请参见此处
