La requête UPDATE est lente en combinaison avec la clause RETURNING INTO.

Question

J'ai une requête de mise à jour qui renvoie l'ID des lignes mises à jour. Le temps d'exécution de la requête est d'environ 90 secondes. Lorsque je supprime la clause Returning, le temps d'exécution est de 1 ms. La table update_table contient 39000 lignes. La requête met à jour 0 ligne dans ce cas. Quand on met à jour 3 lignes, le temps d'exécution est le même.

DECLARE
  type intTable IS TABLE OF INTEGER;
  idCol intTable;
BEGIN
UPDATE 
  update_table
            SET  
            prop1 = 3, prop2 = NULL
            WHERE EXISTS (
                SELECT null FROM update_table f 
                    INNER JOIN rel_table1 u ON f.ID= u.ID
                    INNER JOIN rel_table2 VP ON f.another_ID = VP.another_ID
                WHERE (u.prop1 = 3) 
                    AND VP.prop1 = 1
                    AND (u.prop2 = 75)
                    AND f.ID = update_table.ID
            )
         ReTURNING ID BULK COLLECT INTO idCol;
.
.
.
  END;

Pourquoi la clause de retour ralentit-elle les requêtes ?

Answer 1

Une bonne partie de l'utilisation d'Oracle consiste à savoir ce qui est "censé" se produire et ce qui ne l'est pas.

Ajout d'un RETURNING INTO n'est pas "censé" ralentir l'exécution de votre mise à jour. Lorsque quelque chose se produit qui n'est pas censé se produire, consultez le site d'assistance d'Oracle pour savoir s'il s'agit d'un bogue connu.

Dans votre cas, il semble que vous rencontrez :

Bogue 27131648 - PLAN SUB OPTIMAL SUR L'ÉTAT DE MISE À JOUR AVEC RETOUR DANS L'INTO.

Je ne suis pas sûr qu'il existe un correctif, mais il y a une solution de contournement simple : utiliser la fonction UNNEST indice. Dans votre cas, ce serait :

UPDATE 
  update_table
            SET  
            prop1 = 3, prop2 = NULL
            WHERE EXISTS (
                SELECT /*+ UNNEST */ null FROM update_table f 
                    INNER JOIN rel_table1 u ON f.ID= u.ID
                    INNER JOIN rel_table2 VP ON f.another_ID = VP.another_ID
                WHERE (u.prop1 = 3) 
                    AND VP.prop1 = 1
                    AND (u.prop2 = 75)
                    AND f.ID = update_table.ID
            )
         ReTURNING ID BULK COLLECT INTO idCol;

Answer 2

Je recommanderais de le diviser en deux parties, d'abord BULK COLLECT et suivant FORALL les identifiants collectés, tous deux extrêmement rapides et vous pourrez continuer à référencer les identifiants mis à jour à partir de idCol .

DECLARE
  type intTable IS TABLE OF INTEGER;
  idCol intTable;
BEGIN
    SELECT f.id 
      BULK COLLECT INTO idCol
      FROM update_table f 
     INNER JOIN rel_table1 u ON f.ID= u.ID
     INNER JOIN rel_table2 VP ON f.another_ID = VP.another_ID
     WHERE (u.prop1 = 3) 
         AND VP.prop1 = 1
         AND (u.prop2 = 75);

    FORALL indx IN 1..idCol.COUNT
        UPDATE update_table
           SET prop1 = 3, prop2 = NULL
         WHERE id = idCol(indx);

.
.
.
END;

J'espère avoir aidé !