Столбцы, указанные в предложении ON, не могут быть обновлены – Java, SQL и jOOQ.

3 min


Стандартный SQL – прекрасный язык. Реализации конкретного поставщика, однако, имеют свои бородавки. В Oracle, например, невозможно обновить какие-либо столбцы в операторе MERGE, на которые ссылается предложение ON. Например:

CREATE TABLE person (
  id NUMBER(18) NOT NULL PRIMARY KEY,
  user_name VARCHAR2(50) NOT NULL UNIQUE,
  score NUMBER(18)
);

Теперь в MySQL мы можем запустить нестандартный INSERT .. ON DUPLICATE KEY UPDATE утверждение как это:

INSERT INTO person (id, user_name, score)
VALUES (1, 'foo', 100)
ON DUPLICATE KEY UPDATE
  SET user_name = 'foo', score = 100

За кулисами MySQL проверит все уникальные ограничения для дубликатов и отклонения вставки, заменив вместо этого оператор обновления. Это спорный вопрос, является ли на самом деле полезно (в идеале, мы хотим проверить только одно ограничение уникальности для дублей), но вот что предлагает MySQL.

В случае, если мы хотим запустить такое же поведение в Oracle, мы могли бы использовать MERGE заявление:

MERGE INTO person t
USING (
  SELECT 1 id, 'foo' user_name, 100 score
  FROM dual
) s
ON (t.id = s.id OR t.user_name = s.user_name)
WHEN MATCHED THEN UPDATE
  SET t.user_name = s.user_name, t.score = 100
WHEN NOT MATCHED THEN INSERT (id, user_name, score)
  VALUES (s.id, s.user_name, s.score)

Это выглядит разумно, но это не работает. Мы получим:

SQL-Fehler: ORA-38104: столбцы, указанные в предложении ON, не могут быть обновлены: «T». »USER_NAME»

Очевидно, что это некоторая защита от ситуации, когда такое обновление внезапно переместит строку из сопоставленной группы в несоответствующую. В этом конкретном примере это может выглядеть не так, что может вызвать проблемы, но если специфичные для поставщика расширения, такие как WHERE или же DELETE пункт будет использоваться, все может выглядеть иначе.

Однако синтаксический анализатор не очень умен, на самом деле он почти не умен. Хотя он обнаруживает крайне глупые попытки обойти это ограничение, например:

MERGE INTO person t
USING (
  SELECT 1 id, 'foo' user_name, 100 score
  FROM dual
) s
-- Circumvention attempt here: NVL()
ON (t.id = s.id OR nvl(t.user_name, null) = s.user_name)
WHEN MATCHED THEN UPDATE
  SET t.user_name = s.user_name, t.score = 100
WHEN NOT MATCHED THEN INSERT (id, user_name, score)
  VALUES (s.id, s.user_name, s.score)

Он не обнаруживает ни одной из этих попыток:

Использование выражений значения строки

MERGE INTO person t
USING (
  SELECT 1 id, 'foo' user_name, 100 score
  FROM dual
) s
ON (t.id = s.id OR 
-- Circumvention attempt here: row value expressions
  (t.user_name, 'dummy') = ((s.user_name, 'dummy')))
WHEN MATCHED THEN UPDATE
  SET t.user_name = s.user_name, t.score = 100
WHEN NOT MATCHED THEN INSERT (id, user_name, score)
  VALUES (s.id, s.user_name, s.score)

Казалось бы, без каких-либо штрафов на план исполнения. Оба индекса используются:

---------------------------------------------------------------------------
| Id  | Operation                               | Name            | Rows  |
---------------------------------------------------------------------------
|   0 | MERGE STATEMENT                         "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   1 |  MERGE                                  | PERSON          "https://blog.jooq.org/"https://blog.jooq.org/"   2 |   VIEW                                  "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   3 |    NESTED LOOPS OUTER                   "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   4 |     FAST DUAL                           "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   5 |     VIEW                                | VW_LAT_8626BD41 |     1 "https://blog.jooq.org/"   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| PERSON          |     1 "https://blog.jooq.org/"   7 |       BITMAP CONVERSION TO ROWIDS       "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   8 |        BITMAP OR                        "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   9 |         BITMAP CONVERSION FROM ROWIDS   "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"* 10 |          INDEX RANGE SCAN               | SYS_C00106110   "https://blog.jooq.org/"https://blog.jooq.org/"  11 |         BITMAP CONVERSION FROM ROWIDS   "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"* 12 |          INDEX RANGE SCAN               | SYS_C00106111   "https://blog.jooq.org/"
---------------------------------------------------------------------------

Коррелированный подзапрос

MERGE INTO person t
USING (
  SELECT 1 id, 'foo' user_name, 100 score
  FROM dual
) s
ON (t.id = s.id OR 
-- Circumvention attempt here: correlated subquery
  (SELECT t.user_name FROM dual) = s.user_name)
WHEN MATCHED THEN UPDATE
  SET t.user_name = s.user_name, t.score = 100
WHEN NOT MATCHED THEN INSERT (id, user_name, score)
  VALUES (s.id, s.user_name, s.score)

Это, кажется, предотвращает любое использование индекса, и поэтому его следует избегать:

----------------------------------------------------------
| Id  | Operation              | Name            | Rows  |
----------------------------------------------------------
|   0 | MERGE STATEMENT        "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   1 |  MERGE                 | PERSON          "https://blog.jooq.org/"https://blog.jooq.org/"   2 |   VIEW                 "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   3 |    NESTED LOOPS OUTER  "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   4 |     FAST DUAL          "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   5 |     VIEW               | VW_LAT_1846A928 |     1 "https://blog.jooq.org/"*  6 |      FILTER            "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   7 |       TABLE ACCESS FULL| PERSON          |     1 "https://blog.jooq.org/"   8 |       FAST DUAL        "https://blog.jooq.org/"     1 |
----------------------------------------------------------

Использование NVL () и обновление вида вместо

Простое простое использование NVL () внутри предложения ON раньше не работало. Парсер был достаточно умен, чтобы обнаружить это. Но он недостаточно умен, чтобы обнаруживать NVL () в представлении / производной таблице.

MERGE INTO (
  SELECT id, user_name, nvl(user_name, null) n, score
  FROM person
) t
USING (
  SELECT 1 id, 'foo' user_name, 100 score
  FROM dual
) s
-- Circumvention attempt here: renamed column
ON (t.id = s.id OR t.n = s.user_name)
WHEN MATCHED THEN UPDATE
  SET t.user_name = s.user_name, t.score = 100
WHEN NOT MATCHED THEN INSERT (id, user_name, score)
  VALUES (s.id, s.user_name, s.score)

Обратите внимание, что оба USER_NAME а также N столбцы – это одно и то же, но парсер не замечает этого и думает, что у нас все хорошо.

План выполнения по-прежнему оптимален, поскольку у Oracle, похоже, есть способ оптимизировать выражения NVL () (но не объединять и другие!):

---------------------------------------------------------------------------
| Id  | Operation                               | Name            | Rows  |
---------------------------------------------------------------------------
|   0 | MERGE STATEMENT                         "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   1 |  MERGE                                  | PERSON          "https://blog.jooq.org/"https://blog.jooq.org/"   2 |   VIEW                                  "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   3 |    NESTED LOOPS OUTER                   "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   4 |     FAST DUAL                           "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   5 |     VIEW                                | VW_LAT_46651921 |     1 "https://blog.jooq.org/"   6 |      TABLE ACCESS BY INDEX ROWID BATCHED| PERSON          |     1 "https://blog.jooq.org/"   7 |       BITMAP CONVERSION TO ROWIDS       "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   8 |        BITMAP OR                        "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   9 |         BITMAP CONVERSION FROM ROWIDS   "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"* 10 |          INDEX RANGE SCAN               | SYS_C00106110   "https://blog.jooq.org/"https://blog.jooq.org/"  11 |         BITMAP CONVERSION FROM ROWIDS   "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"* 12 |          INDEX RANGE SCAN               | SYS_C00106111   "https://blog.jooq.org/"
---------------------------------------------------------------------------

Использование предложения WHERE

Если бы у нас не было OR предикат в нашем ON оговорка, но AND предикат, то мы могли бы использовать WHERE оговорка в Oracle. Это работает:

-- NOT the same query as the original one!
MERGE INTO person t
USING (
  SELECT 1 id, 'foo' user_name, 100 score
  FROM dual
) s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE
  SET t.user_name = s.user_name, t.score = 100
  WHERE t.user_name = s.user_name
WHEN NOT MATCHED THEN INSERT (id, user_name, score)
  VALUES (s.id, s.user_name, s.score);

Это не тот же запрос, что и исходный. Я просто перечислил это здесь для полноты картины. Также, чтобы напомнить читателям о том, что этот подход также не использует индексы оптимально. Только индекс первичного ключа (из ON пункт), кажется, используется. Уникальный ключ не используется:

----------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  |
----------------------------------------------------------------
|   0 | MERGE STATEMENT                "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   1 |  MERGE                         | PERSON        "https://blog.jooq.org/"https://blog.jooq.org/"   2 |   VIEW                         "https://blog.jooq.org/"https://blog.jooq.org/"https://blog.jooq.org/"   3 |    NESTED LOOPS OUTER          "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   4 |     VIEW                       "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   5 |      FAST DUAL                 "https://blog.jooq.org/"     1 "https://blog.jooq.org/"   6 |     TABLE ACCESS BY INDEX ROWID| PERSON        |     1 "https://blog.jooq.org/"*  7 |      INDEX UNIQUE SCAN         | SYS_C00106110 |     1 |
----------------------------------------------------------------

Осторожный

Будьте осторожны при применении вышеуказанных обходных путей. Предполагая, что ORA-38104 – хорошая вещь (то есть, что Oracle все еще думает, что это должно быть принудительно выполнено), тогда вышеупомянутые обходные пути просто выявляют ошибки в синтаксическом анализаторе, который должен обнаруживать такие случаи. Вышеуказанное поведение наблюдается в Oracle 12c и 18c.

Я лично считаю, что от ORA-38104 следует отказаться полностью и устранить причину этого ограничения. Но, безусловно, стоит изучить альтернативные варианты, а не полагаться на описанные выше обходные пути в производственном коде, за исключением случайного одноразового запроса на миграцию, где такие дыры в петлях всегда являются хорошими инструментами для использования.


0 Comments

Ваш e-mail не будет опубликован. Обязательные поля помечены *