sql - 如何更改 Oracle 中的更新执行计划？

Question

我有一个大表 foo_large 和一个相对较小(几十万行)的表 foo_small。大表有一个主键列“id”；小的也有“id”列，它是 foo_large 的外键。我想更新 foo_small，以便对于每一行，其 col_y 的值等于 foo_large 对应行中的 col_x。最直接的方式似乎是这样的:

update foo_small sm
set col_y = (
  select col_x
  from foo_large
  where id = sm.id);

但是，这是非常低效的。对于 foo_small 的每一行，foo_large 的对应行由其主键上的索引访问。尽管 foo_small 与 foo_large 相比较小，但它仍然会导致对该表进行数十万次索引扫描。更好的解决方案是在内存中对 foo_small 进行哈希处理，并对 foo_large 执行一次(可能是并行的)全扫描，更新遇到的 foo_small 的匹配行。我可以通过以下方式做到这一点:

update
(
  select /*+ ordered use_hash(lg) parallel(lg 2) */
    sm.*, lg.col_x
  from
    foo_small sm,
    foo_large lg
  where sm.id = lg.id
)
set col_y = col_x;

此查询在一分钟内完成。不幸的是，它还有另一个缺点:它要求启动此查询的用户不仅具有更新 foo_small 的权限，而且还具有更新 foo_large 的权限，即使后一个表实际上并未更新。是否有解决方案可以在不更新连接的情况下强制执行后一个执行计划？我知道我可以编写一段带有批量提取/更新的过程 PL/SQL 代码，并且可能保留大部分性能增益，但我想必须有一种方法可以在单个查询中完成它。

提前致谢。

Answer 1

下面是香农建议的最终查询，它完成了这项工作:

merge /*+ leading(sm) full(lg) use_hash(lg) parallel(lg 2) */
  into foo_small sm
using foo_large lg
  on (lg.id = sm.id)
when matched then
  update set sm.col_y = lg.col_x