Les colonnes référencées dans la clause ON ne peuvent pas être mises à jour – Java, SQL et jOOQ.

Online Coding Courses for Kids

SQL standard est un beau langage. Cependant, les implémentations spécifiques au fournisseur ont leurs verrues. Dans Oracle, par exemple, il n’est pas possible de mettre à jour les colonnes d’une instruction MERGE, qui ont été référencées par la clause ON. Par exemple:

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

Maintenant, dans MySQL, nous pouvons exécuter un non standard INSERT .. ON DUPLICATE KEY UPDATE déclaration comme ceci:

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

Dans les coulisses, MySQL vérifiera tout contraintes uniques pour les doublons et rejetez l’insertion, en la remplaçant par l’instruction update à la place. On peut se demander si cela est vraiment utile (idéalement, nous ne voulons vérifier qu’une seule contrainte unique pour les doublons), mais c’est ce que MySQL offre.

Si nous voulons exécuter le même comportement par Oracle, nous pourrions utiliser le MERGE déclaration:

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)

Cela semble raisonnable, mais cela ne fonctionne pas. Nous aurons:

SQL-Fehler: ORA-38104: Les colonnes référencées dans la clause ON ne peuvent pas être mises à jour: “T”. “USER_NAME”

De toute évidence, il s’agit d’une certaine protection contre la situation où une telle mise à jour déplacerait soudainement une ligne du groupe correspondant au groupe non correspondant. Dans cet exemple particulier, il pourrait ne pas ressembler à quelque chose qui pourrait causer des problèmes, mais si des extensions spécifiques au fournisseur telles que le WHERE ou DELETE serait utilisée, les choses pourraient sembler différentes.

Cependant, l’analyseur n’est pas très intelligent, en fait, il n’est presque pas intelligent du tout. Bien qu’il détecte des tentatives extrêmement stupides de contourner cette limitation, comme celle-ci:

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)

Il ne détecte aucune de ces tentatives:

Utilisation d’expressions de valeur de ligne

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)

Apparemment sans aucune pénalité sur le plan d’exécution. Les deux index sont utilisés:

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

Sous-requête corrélée

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)

Cela semble empêcher toute utilisation d’index et doit donc être évité:

----------------------------------------------------------
| Id  | Operation              | Name            | Rows  |
----------------------------------------------------------
|   0 | MERGE STATEMENT        |                 |     1 |
|   1 |  MERGE                 | PERSON          |       |
|   2 |   VIEW                 |                 |       |
|   3 |    NESTED LOOPS OUTER  |                 |     1 |
|   4 |     FAST DUAL          |                 |     1 |
|   5 |     VIEW               | VW_LAT_1846A928 |     1 |
|*  6 |      FILTER            |                 |       |
|   7 |       TABLE ACCESS FULL| PERSON          |     1 |
|   8 |       FAST DUAL        |                 |     1 |
----------------------------------------------------------

Utilisation de NVL () et mise à jour d’une vue à la place

La simple utilisation simple de NVL () dans la clause ON ne fonctionnait pas auparavant. L’analyseur était suffisamment intelligent pour détecter cela. Mais il n’est pas assez intelligent pour détecter NVL () à l’intérieur d’une vue / table dérivée.

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)

Notez que les deux USER_NAME et N les colonnes sont la même chose, mais l’analyseur ne le remarque pas et pense que nous allons bien.

Le plan d’exécution est toujours optimal, car Oracle semble avoir un moyen de optimiser les expressions NVL () (mais pas fusionner et autres!):

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

Utilisation de la clause WHERE

Si nous n’avions pas eu OR prédicat dans notre ON clause, mais une AND prédicat, alors nous aurions pu utiliser le WHERE clause dans Oracle. Cela marche:

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

Ce n’est pas la même requête que celle d’origine. Je viens de l’énumérer ici par souci d’exhaustivité. Pour rappeler également aux lecteurs que cette approche ne semble pas non plus utiliser les index de manière optimale. Seul l’index de clé primaire (de la ON semble être utilisée. La clé unique n’est pas utilisée:

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

Prudent

Soyez prudent lorsque vous appliquez les solutions de contournement ci-dessus. En supposant que ORA-38104 est une bonne chose (c’est-à-dire qu’Oracle pense toujours qu’il devrait être appliqué), les solutions de contournement ci-dessus exposent simplement les bogues de l’analyseur, qui devraient détecter de tels cas. Le comportement ci-dessus a été observé dans Oracle 12c et 18c.

Je crois personnellement que ORA-38104 devrait être abandonné complètement et que la cause profonde de cette restriction devrait être supprimée. Mais il vaut certainement la peine d’explorer d’autres options plutôt que de s’appuyer sur les solutions de contournement ci-dessus dans le code de production, à l’exception de la requête de migration ponctuelle occasionnelle, où de tels trous de boucle sont toujours de bons outils à exploiter.

Close Menu