UPDATE table1
SET col1 = 'Y'
WHERE col2 in (select col2 from table2)
在上述查询中,假设内部查询返回10000行。这个带有IN子句的查询会影响性能吗?
如果是,有什么方法可以加速执行?
UPDATE table1
SET col1 = 'Y'
WHERE col2 in (select col2 from table2)
如果子查询返回的行数与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 值的行是否彼此相邻还是随机分布)。无论如何,从性能角度来看,如果您需要执行此更新,则该查询是最快的方法之一。
EXISTS
的查询添加执行计划。 - zerkmsUPDATE table1 outer
SET col1 = 'Y'
WHERE EXISTS (select null
from table2
WHERE col2 = outer.col2)
这可以更好。
要知道哪个更好 - 查看执行计划即可。
来自Oracle:
11.5.3.4 子查询中使用EXISTS与IN的区别
在某些情况下,使用IN比使用EXISTS更好。通常情况下,如果选择谓词在子查询中,则使用IN。如果选择谓词在父查询中,则使用EXISTS。
根据我的经验,当子查询返回大量行时使用EXISTS可以得到更好的计划。
请参见此处以获取更多来自Oracle的讨论信息。