如何优化Oracle中的大规模更新

Question

我需要更新一个包含约40*10^6条记录的Oracle大表。 将被修改的行数大约为10^7。 确定要更新的行的查询很复杂，涉及连接。仅查找将被更新的行的ID就需要30分钟。

Select p.some_id from
(select some_id, col2,col3 from t1 where col2='someVulue' and col3 ='someValue') p 
inner join (select some_id from t2 where t2.col3='someValue') q
on p.some_id=q.some_id

现在为了进行更新，我需要添加另一个join或使用IN语句，这将使事情更加糟糕。有没有一种方法可以并行化此过程？或者进行批量更新（每次更新25 * 10 ^ 4行）？有没有一种方法告诉Oracle仅更新前n行？然后是n->2n，然后是2n->3n...？脚本将在生产环境上运行，因此表重建不是可行的选择。更新包括将布尔列设置为true。（如果这有助于理解）

- Ghazouani Med amine

---

这个任务会在服务窗口期间完成吗？因此你需要以任何代价快速运行它吗？还是查询将在有活跃用户时运行？一种并行的方法可以在一个事务中更新所有行，但当数据库被使用时不建议使用。在这种情况下，我宁愿在一个选择中查找ID并将它们复制到临时表中。然后我会分批处理它们，并在每个批次之间进行提交。这意味着需要进行一次昂贵的选择来获取ID，然后进行多个相对便宜的更新。 - ewramner

更多关于更新的细节可能会有所帮助。您只发布了一个 select 查询。我认为我理解了 in 子查询的问题，Jon Heller 的 merge 建议可能会有所帮助，或者是 update (select...join...) set...，然后还有 dbms_parallel_execute 或纯粹的 /*+ parallel */ 用于并行方面。 - William Robertson

Answer 1

执行大规模更新的最快方法是使用并行 DML，像这样：

alter session enable parallel dml;
update /*+ parallel(16) */ some_table set some_column = 1;
commit;

需要注意很多细节。您需要使用企业版。 UPDATE 将在表上获得独占锁定，因此其他人将无法同时向表写入。系统必须具有足够的资源来支持大型 UPDATE，例如足够的重做、撤消、CPU、I/O和配置合理的系统。

（您可能希望将我的示例中的数字 16 更改为适合您系统的数字。如果您想最大限度地提高性能，但也许会牺牲其他进程，请将该数字设置为核心数。）

Oracle 并行处理很好，但并不是真正的优化。它使系统工作更加努力，而不是更加聪明。在尝试并行处理之前，您可能需要查看在 UPDATE 中使用的 SQL 语句。您还可以尝试使用 MERGE。一开始，MERGE 语法可能有点棘手，但它可以帮助避免重复连接，并允许哈希连接，这可以更快地运行以更改大量行。

- Jon Heller

Answer 2

有几种方法。 1）将查询拆分成小块。 例如：按主键拆分表或添加子句 2）如果您需要经常运行此操作，可以考虑分区表，然后并行运行更新 3）检查索引是否正确构建 顺便说一下，如果不会影响应用程序的性能或阻塞其他查询，我认为30分钟并不算长时间，这是很正常的。

- sic wang

Answer 3

批量更新（每次更新25*10^4行）？有没有办法告诉Oracle只更新前n行？然后n->2n，然后2n->3n，其中n为10。

 for loop i in 1..10
 loop
 update table1
 set column_val=x
 where rowid in (select rowid from table1
 where rownum >= (((i-1) * (25*10^4))+1) and rownum <= i*(25*10^4)
 );
 end loop;

- psaraj12