Comment optimiser une mise à jour SQL qui s'exécute sur une table Oracle avec 700M lignes

UPDATE [TABLE] SET [FIELD]=0 WHERE [FIELD] IS NULL

[TABLE] est une base de données Oracle table avec plus de 700 millions de lignes. J'ai annulé l'exécution de SQL après qu'il avait été en cours d'exécution pendant 6 heures.

Est-il une astuce SQL qui pourrait améliorer les performances? Ou toute autre solution à la vitesse?

EDIT: Cette requête sera exécutée une fois et plus jamais ensuite.

source d'informationauteur b.roth

  1. 10

    Tout d'abord est-il un moment de la requête ou est-il récurrent de la requête ? Si vous avez seulement à faire une fois, vous voudrez peut-être chercher dans l'exécution de la requête en mode parallèle. Vous aurez à analyser toutes les lignes de toute façon, vous pourriez diviser la charge de travail vous-même avec des plages de ROWID (do-it-yourself de parallélisme) ou de l'utilisation d'Oracle fonctionnalités.

    En supposant que vous souhaitez exécuter fréquemment et vous voulez optimiser cette requête, le nombre de lignes avec la field colonne NULLE finira par être petit par rapport au nombre total de lignes. Dans ce cas, un indice pourrait accélérer les choses. Oracle n'a pas d'index des lignes qui ont toutes les colonnes indexées comme NULL si un index sur field ne pas s'en servir par votre requête (puisque vous voulez trouver toutes les lignes où field est NULL).

    :

    • de créer un index sur (FIELD, 0)le 0 va agir comme un non-NULLE de la pseudo-colonne et toutes les lignes seront indexés sur la table.

    • créer une fonction d'index de base sur (CASE WHEN field IS NULL THEN 1 END)ce sera seul indice pour les lignes qui sont les valeurs Null (l'indice serait donc très compact). Dans ce cas, il faudrait réécrire votre requête:

      UPDATE [TABLE] SET [FIELD]=0 WHERE (CASE WHEN field IS NULL THEN 1 END)=1

    Edit:

    Puisque c'est un scénario, vous pouvez utiliser le PARALLEL astuce:

    SQL> EXPLAIN PLAN FOR
  2  UPDATE /*+ PARALLEL(test_table 4)*/ test_table
  3     SET field=0
  4   WHERE field IS NULL;

Explained

SQL> select * from table( dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 4026746538
--------------------------------------------------------------------------------
| Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT      |            | 22793 |   289K|    12   (9)| 00:00:
|   1 |  UPDATE               | TEST_TABLE |       |       |            |
|   2 |   PX COORDINATOR      |            |       |       |            |
|   3 |    PX SEND QC (RANDOM)| :TQ10000   | 22793 |   289K|    12   (9)| 00:00:
|   4 |     PX BLOCK ITERATOR |            | 22793 |   289K|    12   (9)| 00:00:
|*  5 |      TABLE ACCESS FULL| TEST_TABLE | 22793 |   289K|    12   (9)| 00:00:
--------------------------------------------------------------------------------
  2. 5

    Autres utilisateurs mettent à jour les mêmes lignes dans la table en même temps ?

    Si oui, vous pourriez être frappé beaucoup de problèmes de simultanéité (en attente de serrures) et il peut être intéressant de le découper en plus petites transactions.

    DECLARE
  v_cnt number := 1;
BEGIN
 WHILE v_cnt > 0 LOOP
   UPDATE [TABLE] SET [FIELD]=0 WHERE [FIELD] IS NULL AND ROWNUM < 50000;
   v_cnt := SQL%ROWCOUNT;
   COMMIT;
 END LOOP;
END;
/

    Le plus petit de la ROWNUM limiter le moins de simultanéité/problèmes de verrouillage vous serez frappé, mais plus le temps que vous passerez dans le tableau de numérisation.

  3. 3

    Vincent déjà répondu à votre question parfaitement, mais je suis curieux de savoir le "pourquoi" derrière cette action. Pourquoi êtes-vous à jour de toutes les NULS est à 0?

    Ce qui concerne,
    Rob.

  4. 1

    Quelques suggestions:

    1. Déposez tous les indices qui contiennent le TERRAIN avant de lancer votre instruction de mise à JOUR, puis les rajouter plus tard.

    2. Écrire un PL/SQL procédure à suivre ce qui les engage après chaque 1000 ou 10000 lignes.

    Espère que cette aide.

  5. 0

    Vous pourriez obtenir le même résultat sans mise à jour à l'aide d'une instruction ALTER table pour définir les colonnes "valeur par DÉFAUT à 0.