Oracle 中看似关键的保留视图的更新引发 ORA-01779 [英] UPDATE on seemingly key preserving view in Oracle raises ORA-01779

问题描述

问题

我正在尝试将 Oracle 12.1.0.2.0 中的低性能 MERGE 语句重构为 UPDATE 语句.MERGE 语句如下所示:

MERGE INTO t使用 (SELECT t.rowid 摆脱，u.account_no_new从 t, u, vWHERE t.account_no = u.account_no_oldAND t.contract_id = v.contract_idAND v.tenant_id = u.tenant_id) sON (t.rowid = s.rid)当匹配然后更新设置 t.account_no = s.account_no_new

它主要是低性能的，因为对大(100M 行)表有两次昂贵的访问t

架构

这些是所涉及的简化表:

• t 正在迁移 account_no 列的目标表.
• u 包含account_no_old → account_no_new 映射的迁移指令表
• v 一个辅助表建模contract_id 和tenant_id
之间的一对一关系

架构是:

创建表 v (contract_id NUMBER(18) NOT NULL PRIMARY KEY,租户 ID NUMBER(18) 非空);创建表 t (t_id NUMBER(18) NOT NULL PRIMARY KEY,-- 此处缺少租户 ID 列account_no NUMBER(18) NOT NULL,contract_id NUMBER(18) NOT NULL REFERENCES v);创建表 u (u_id NUMBER(18) NOT NULL PRIMARY KEY,租户 ID NUMBER(18) 非空，account_no_old NUMBER(18) 非空，account_no_new NUMBER(18) 非空，唯一 (tenant_id, account_no_old));

我无法修改架构.我知道添加 t.tenant_id 将通过阻止 JOIN 到 v

来解决问题

替代 MERGE 不起作用:

<块引用>

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

注意，自连接是不可避免的，因为这种替代的等效查询导致 ORA-38104:

MERGE INTO t使用 (选择 u.account_no_old、u.account_no_new、v.contract_id从你，vWHERE v.tenant_id = u.tenant_id) sON (t.account_no = s.account_no_old AND t.contract_id = s.contract_id)当匹配然后更新设置 t.account_no = s.account_no_new

更新视图不​​起作用:

<块引用>

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

直觉上，我会在这里应用传递闭包，这应该保证对于 t 中的每个更新行，u 和 u 中最多只能有 1 行代码>v.但显然，Oracle 无法识别这一点，因此以下 UPDATE 语句不起作用:

更新(选择 t.account_no, u.account_no_new从 t, u, vWHERE t.account_no = u.account_no_oldAND t.contract_id = v.contract_idAND v.tenant_id = u.tenant_id)SET account_no = account_no_new

以上引发ORA-01779.添加未记录的提示 /*+BYPASS_UJVC*/ 在 12c 上似乎不再起作用.

如何告诉 Oracle 该视图是键保留的?

在我看来，视图仍然是key保留的，即对于t中的每一行，v中有恰好一行，因此在 u 中至多一行.因此，视图应该是可更新的.有没有办法重写这个查询，让 Oracle 相信我的判断?

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

解决方案

有没有办法重写这个查询，让 Oracle 相信我的判断?

通过在目标中引入辅助列，我设法说服"Oracle 执行 MERGE:

MERGE INTO (SELECT (SELECT t.account_no FROM dual) AS account_no_temp,t.account_no, t.contract_id从 t) t使用 (选择 u.account_no_old、u.account_no_new、v.contract_id从你，vWHERE v.tenant_id = u.tenant_id) sON (t.account_no_temp = s.account_no_old AND t.contract_id = s.contract_id)当匹配然后更新设置 t.account_no = s.account_no_new;

db<>小提琴演示

<小时>

编辑

上述想法的变体 - 子查询直接移至 ON 部分:

MERGE INTO (SELECT t.account_no, t.contract_id FROM t) t使用 (选择 u.account_no_old、u.account_no_new、v.contract_id从你，vWHERE v.tenant_id = u.tenant_id) sON ((SELECT t.account_no FROM dual) = s.account_no_oldAND t.contract_id = s.contract_id)当匹配然后更新设置 t.account_no = s.account_no_new;

db<>fiddle demo2

相关文章:ON 子句中引用的列无法更新

编辑 2:

MERGE INTO (SELECT t.account_no, t.contract_id FROM t) t使用(选择 u.account_no_old、u.account_no_new、v.contract_id从你，vWHERE v.tenant_id = u.tenant_id) sON((t.account_no,t.contract_id,'x')=((s.account_no_old,s.contract_id,'x')) OR 1=2)当匹配然后更新设置 t.account_no = s.account_no_new;

db<>fiddle demo3

Problem

I'm trying to refactor a low-performing MERGE statement to an UPDATE statement in Oracle 12.1.0.2.0. The MERGE statement looks like this:

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

It is mostly low performing because there are two expensive accesses to the large (100M rows) table t

Schema

These are the simplified tables involved:

• t The target table whose account_no column is being migrated.
• u The migration instruction table containing a account_no_old → account_no_new mapping
• v An auxiliary table modelling a to-one relationship between contract_id and tenant_id

The schema is:

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)
);

I cannot modify the schema. I'm aware that adding t.tenant_id would solve the problem by preventing the JOIN to v

Alternative MERGE doesn't work:

ORA-38104: Columns referenced in the ON Clause cannot be updated

Note, the self join cannot be avoided, because this alternative, equivalent query leads to 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 view doesn't work:

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

Intuitively, I would apply transitive closure here, which should guarantee that for each updated row in t, there can be only at most 1 row in u and in v. But apparently, Oracle doesn't recognise this, so the following UPDATE statement doesn't work:

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

The above raises ORA-01779. Adding the undocumented hint /*+BYPASS_UJVC*/ does not seem to work anymore on 12c.

How to tell Oracle that the view is key preserving?

In my opinion, the view is still key preserving, i.e. for each row in t, there is exactly one row in v, and thus at most one row in u. The view should thus be updatable. Is there any way to rewrite this query to make Oracle trust my judgement?

Or is there any other syntax I'm overlooking that prevents the MERGE statement's double access to t?

解决方案

Is there any way to rewrite this query to make Oracle trust my judgement?

I've managed to "convince" Oracle to do MERGE by introducing helper column in target:

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 demo

---

EDIT

A variation of idea above - subquery moved directly to ON part:

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

Related article: Columns referenced in the ON Clause cannot be updated

EDIT 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

这篇关于Oracle 中看似关键的保留视图的更新引发 ORA-01779的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！