sql - Oracle 中看似关键的保留 View 的更新引发 ORA-01779

Question

问题

我正在尝试重构一个低性能的 MERGE声明 UPDATE Oracle 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: Columns referenced in the ON Clause cannot be updated

请注意，无法避免自连接，因为这种替代的等效查询会导致 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

更新 View 不​​起作用:

ORA-01779: cannot modify a column which maps to a non-key-preserved table

直观地说，我会在这里应用传递闭包，这应该保证对于 t 中的每个更新行。 , u 中最多只能有 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 该 View 是 key 保留的？

在我看来，该 View 仍然是关键保留，即对于 t 中的每一行，在 v 中正好有一行，因此在 u 中最多只有一行.因此， View 应该是可更新的。有什么办法可以重写这个查询，让 Oracle 相信我的判断吗？

或者有没有我忽略的其他语法可以防止 MERGE语句对 t 的双重访问?

Answer 1

您可以定义一个临时表，其中包含来自 U 的预连接数据。和 V .

使用 contract_id, account_no_old 上的唯一索引支持它(这应该是唯一的)。

那么你可以在 中使用这个临时表可更新的连接 View 。

create table tmp as
  SELECT v.contract_id, u.account_no_old, u.account_no_new
  FROM u, v
  WHERE  v.tenant_id = u.tenant_id;

create unique index tmp_ux1 on tmp ( contract_id, account_no_old);


UPDATE (
  SELECT t.account_no, tmp.account_no_new
  FROM t, tmp
  WHERE t.account_no = tmp.account_no_old
  AND t.contract_id = tmp.contract_id
)
SET account_no = account_no_new
;