oracle从其他表中更新数十亿数据

Question

我有两个大表，表A包含14亿行，表B包含1400万行。我的sql如下：update A set A.cola=(select colb from B where A.cola=B.cold) where exists (select 1 from B where A.cola=B.cold and colb is not null)CCD_ 2、CCD_。表太大，无法在一个事务中更新，但我不熟悉PL/SQL，有人有高效处理这个问题的经验吗？

Answer 1

使用MERGE:

MERGE INTO table_a A
USING ( SELECT A.rowid AS rid, B.colb
FROM table_a A
INNER JOIN table_b B ON A.cola = B.cold
WHERE B.colb IS NOT NULL ) upA
ON (A.rowid = upA.rid)
WHEN MATCHED THEN UPDATE SET A.cola = upA.colb;