Oracle使用INSERT ALL插入两表时SYS_GUID不一致如何解决

问题根因 #

SYS_GUID()属于非确定性函数，Oracle执行INSERT ALL语法时，每一个INTO子句都会重新计算一次SELECT列表中的表达式，因此插入两张表时会生成不同的GUID，导致id无法对应。

修复方案 #

将SYS_GUID()的计算逻辑放到内层子查询中，强制提前生成固定的id结果集，再供给INSERT ALL使用即可，适配多行数据返回的场景，正确写法如下：

insert all
 into table_a values(id, name)
 into table_a_a values(id, name)
select id, name from (
  select SYS_GUID() AS id, name from table_source
);

兜底兼容方案 #

如果遇到Oracle优化器自动展开子查询、依旧重复生成GUID的极端情况，可以使用带MATERIALIZE提示的CTE强制固化结果集：

WITH source_data AS (
  SELECT /*+ MATERIALIZE */ SYS_GUID() AS id, name FROM table_source
)
INSERT ALL
 INTO table_a VALUES(id, name)
 INTO table_a_a VALUES(id, name)
SELECT id, name FROM source_data;

验证方法 #

执行插入前可单独运行内层SELECT语句，确认每一行的id固定生成后，再套入INSERT ALL逻辑即可保证两张表的id完全匹配。

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