在Oracle中，IN子句对性能有何影响？

Question

UPDATE table1 
       SET col1 = 'Y'
     WHERE col2 in (select col2 from table2)

在上述查询中，假设内部查询返回10000行。这个带有IN子句的查询会影响性能吗？

如果是，有什么方法可以加速执行？

- Vivek

---

如果子查询返回10000个结果，则运行时将为零，因为它会抛出错误。我猜你想在子选择中写IN而不是=？ - user330315

你说的没错。我已经做出了更改，谢谢。 - Vivek

如果@zerkms提供的答案对您有帮助，请接受它。查看FAQ以了解为什么应该这样做。 :-) - Guillem Vicens

Answer 1

如果子查询返回的行数与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")

它将扫描两个表格一次，并且只更新TABLE1与两个表格都存在的行。如果你需要更新大量的行，这是一个高效的计划。

有时内部查询的行数相对于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")

在这种情况下，Oracle将从TABLE2读取行，并针对每个（唯一的）行，在TABLE1上执行索引访问。哪种访问更快取决于内部查询的选择性和TABLE1上的索引聚集方式（具有相似 col2 值的行是否彼此相邻还是随机分布）。无论如何，从性能角度来看，如果您需要执行此更新，则该查询是最快的方法之一。

- Vincent Malgrat

---

我的查询怎么样？;-) 请为带有EXISTS的查询添加执行计划。 - zerkms

@zerkms：抱歉，我忘记提到IN子查询会被优化器转换为等效的EXIST子查询。因此它们产生相同的执行计划。个人而言，我发现IN语句更易读，但这高度主观 :) - Vincent Malgrat

Answer 2

UPDATE table1 outer
   SET col1 = 'Y'
 WHERE EXISTS (select null
                 from table2
                WHERE col2 = outer.col2)

这可以更好。

要知道哪个更好 - 查看执行计划即可。

- zerkms

---

我认为这是一个已解决的问题，因为对于Oracle优化器来说，IN和EXISTS通常没有区别，并且会根据需要转换为另一个。http://blogs.oracle.com/optimizer/2010/09/optimizer_transformations_subquery_unesting_part_1.html - Mike Meyers

Answer 3

来自Oracle：

11.5.3.4 子查询中使用EXISTS与IN的区别

在某些情况下，使用IN比使用EXISTS更好。通常情况下，如果选择谓词在子查询中，则使用IN。如果选择谓词在父查询中，则使用EXISTS。

根据我的经验，当子查询返回大量行时使用EXISTS可以得到更好的计划。

请参见此处以获取更多来自Oracle的讨论信息。

- tbone