ОБНОВЛЕНИЕ на казалось бы сохраняющем ключ представление в Oracle поднимает 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 решит проблему, не допуская 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

ОБНОВЛЕНИЕ вид не работает:

ORA-01779: невозможно изменить столбец, который сопоставляется с таблицей без сохранения ключа

Интуитивно я бы применил транзитивное замыкание здесь, что должно гарантировать, что для каждой обновленной строки в tможет быть не более 1 строки в u И в 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

Попытка сделать это с более простым обновлением. Все еще требует подвыбора.

update t
set t.account_no = (SELECT u.account_no_new
  FROM u, v
  WHERE t.account_no = u.account_no_old
  AND t.contract_id = v.contract_id
  AND v.tenant_id = u.tenant_id);

Бобби

Answer 2

Вы можете определить временную таблицу, содержащую предварительно объединенные данные из U а также V,

Поддержите его уникальным индексом contract_id, account_no_old (который должен быть уникальным).

Затем вы можете использовать эту временную таблицу в обновляемом представлении соединения.

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
;

Answer 3

Есть ли способ переписать этот запрос, чтобы заставить Oracle доверять моему мнению?

Мне удалось "убедить" Oracle сделать MERGE, введя вспомогательный столбец в 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