ORA_ROWSCN作关联条件的UPDATE..WHERE..EXISTS异常问题分析

结论 #

该差异是EXISTS子句特性与ORA_ROWSCN伪列特性共同作用导致的。

具体原因拆解 #

1. ORA_ROWSCN伪列的特殊属性 #

哪怕开启了ROWDEPENDENCIES行级SCN模式，ORA_ROWSCN本质仍是伪列，它的求值时机和普通存储列不同：

• 普通列的值是直接从数据块中读取的固定值，求值时机不影响匹配结果
• ORA_ROWSCN的值是行访问时动态计算的，只有在顶层谓词/投影中直接关联行标识时，才会返回对应行的真实SCN值

2. EXISTS半连接的优化规则差异 #

• UPDATE WHERE IN和MERGE走常规内连接执行逻辑：会先逐行获取外层表每行的ROWID和对应的ORA_ROWSCN实际值，再和指定的匹配条件做等值比对，只要ROWID和SCN同时匹配就会命中目标行
• UPDATE WHERE EXISTS默认走半连接优化：Oracle优化器会做子查询解嵌套（unnest）和谓词推入操作，错误地将ORA_ROWSCN的求值提前到子查询执行前，没有和ROWID对应的行做动态绑定，导致SCN匹配的谓词直接判定为不成立，最终匹配不到任何行

验证方法 #

你可以给EXISTS子句添加/*+ NO_UNNEST */hint强制关闭子查询解嵌套优化，就能看到EXISTS也可以正常匹配到符合条件的行，示例语法：

UPDATE ora_rowscn_test t1
SET col1 = 'test'
WHERE EXISTS (
    SELECT /*+ NO_UNNEST */ 1 
    FROM 你的匹配表 t2
    WHERE t1.ROWID = t2.row_id_val 
    AND t1.ORA_ROWSCN = t2.scn_val
);

内容的提问来源于stack exchange，提问作者Joshz