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

Question

问题

我正在尝试将性能不佳的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不起作用：

ORA-38104：ON子句中引用的列无法更新

注意，无法避免自我联接，因为这种替代的等效查询会导致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视图不起作用：

ORA-01779：无法修改映射到非键保留表的列

凭直觉，我将在此处应用传递闭包，这应确保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？

Answer 1

有什么方法可以重写此查询以使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 <>
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
demo2

相关文章：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 <> fiddle
demo3