Oracle中看似关键的保留视图的更新引发了ORA-01779

幸运平安

问题
我正在尝试将性能不佳的MERGE语句重构为UPDATEOracle 12.1.0.2.0中的语句。该MERGE语句如下所示：
MERGE INTO t
USING (
  SELECT t.rowid rid, u.account_no_new
  FROM t, u, v
  WHERE t.account_no = u.account_no_old
  AND t.contract_id = v.contract_id
  AND v.tenant_id = u.tenant_id
) s
ON (t.rowid = s.rid)
WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new
它主要是低性能的，因为对大型（100M行）表有两次昂贵的访问 t
架构图
这些是涉及的简化表：
t``account_no正在迁移其列的目标表。
u含有迁移指令表account_no_old鈫account_no_new映射
v辅助表，用于模拟contract_id和之间的一对一关系tenant_id

该架构为：
CREATE TABLE v (
  contract_id NUMBER(18) NOT NULL PRIMARY KEY,
  tenant_id NUMBER(18) NOT NULL
);
CREATE TABLE t (
  t_id NUMBER(18) NOT NULL PRIMARY KEY,
  -- tenant_id column is missing here
  account_no NUMBER(18) NOT NULL,
  contract_id NUMBER(18) NOT NULL REFERENCES v
);
CREATE TABLE u (
  u_id NUMBER(18) NOT NULL PRIMARY KEY,
  tenant_id NUMBER(18) NOT NULL,
  account_no_old NUMBER(18) NOT NULL,
  account_no_new NUMBER(18) NOT NULL,
  UNIQUE (tenant_id, account_no_old)
);
我无法修改架构。我知道添加t.tenant_id将通过阻止JOIN来解决问题v
替代MERGE不起作用：

: V2 V. l  G/ gORA-38104：ON子句中引用的列无法更新) E* a8 ^: f) ]9 k" ]

注意，无法避免自我联接，因为这种替代的等效查询会导致ORA-38104：
MERGE INTO t
USING (
  SELECT u.account_no_old, u.account_no_new, v.contract_id
  FROM u, v
  WHERE v.tenant_id = u.tenant_id
) s
ON (t.account_no = s.account_no_old AND t.contract_id = s.contract_id)
WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new
UPDATE视图不起作用：
- w0 o/ T3 @1 E3 N1 N+ l0 S. P5 S
ORA-01779：无法修改映射到非键保留表的列5 ]( s1 E/ X' O- ~7 x! R& M

凭直觉，我将在此处应用传递闭包，这应确保in中的每个更新t行inu和in中最多只能有1行v。但是显然，Oracle无法识别这一点，因此以下UPDATE语句不起作用：
UPDATE (
  SELECT t.account_no, u.account_no_new
  FROM t, u, v
  WHERE t.account_no = u.account_no_old
  AND t.contract_id = v.contract_id
  AND v.tenant_id = u.tenant_id
)
SET account_no = account_no_new
以上提出ORA-01779。添加未记录的提示/*+BYPASS_UJVC*/似乎在12c上不再起作用。
如何告诉Oracle视图是关键的？
在我看来，该视图仍然是关键的保护，即，对于每行t，恰好在其中一行v，因此最多
在一行u。因此，该观点应该是可更新的。有什么方法可以重写此查询以使Oracle相信我的判断？
还是有其他我忽略的语法阻止了该MERGE语句的双重访问t？

解决方案:



有什么方法可以重写此查询以使Oracle相信我的判断？

通过在目标中引入帮助器列，我设法“说服” Oracle进行合并：
MERGE INTO (SELECT (SELECT t.account_no FROM dual) AS account_no_temp,
                  t.account_no, t.contract_id
         FROM t) t
USING (
  SELECT u.account_no_old, u.account_no_new, v.contract_id
  FROM u, v
  WHERE v.tenant_id = u.tenant_id
) s
ON (t.account_no_temp = s.account_no_old AND t.contract_id = s.contract_id)
WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;
db & T  v# t' _0 ^% s2 Z
fiddle演示

编辑
上面的想法的变体-子查询直接移至ON部分：
MERGE INTO (SELECT t.account_no, t.contract_id FROM t) t
USING (
   SELECT u.account_no_old, u.account_no_new, v.contract_id
   FROM u, v
   WHERE v.tenant_id = u.tenant_id
) s
ON ((SELECT t.account_no FROM dual) = s.account_no_old
   AND t.contract_id = s.contract_id)
WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;
db  fiddle
( b' E5 ?! N; ]  h# Edemo2
相关文章：ON子句中引用的列无法更新
编辑2：
MERGE INTO (SELECT t.account_no, t.contract_id FROM t) t
USING (SELECT u.account_no_old, u.account_no_new, v.contract_id
   FROM u, v
   WHERE v.tenant_id = u.tenant_id) s
ON((t.account_no,t.contract_id,'x')=((s.account_no_old,s.contract_id,'x')) OR 1=2)
WHEN MATCHED THEN UPDATE SET t.account_no = s.account_no_new;
db  fiddle6 L. X( j/ n" E4 m: ^
demo3

Oracle中看似关键的保留视图的更新引发了ORA-01779

幸运平安 LV1