Insertion de masse sans interruption

Version imprimable

13/03/2017, 12h10
GoLDoZ

Insertion de masse sans interruption

Bonjour.

Je souhaite insérer les données d'une table dans une autre en "zappant" les erreurs, c'est-à-àdire que si une ligne est déjà présente dans la table cible (selon la clé primaire), elle est ignorée et l'insertion continue.

Je ne souhaite pas faire d'insertion ligne à ligne car il s'agit de gros volumes (table source 3 millions, table cible 45 millions). Je souhaite simplement faire un "insert into... select * from... " (les deux tables ont la même structure) en ignorant les erreurs. L'insertion est dans une fonction PL/SQL mais je ne veux pas de curseurs ou autre sous peine de performances dégradées (il me semble).

Est-ce possible à l'aide d'options (que j'ignore) ?

Merci :D

Quelque chose comme ça :
INSERT INTO table_cible

Code:

1
2
3
4
5
6
7
SELECT  *
FROM    table_source  src
WHERE   NOT EXISTS
        (   SELECT  NULL
            FROM    table_cible cbl
            WHERE   cbl.id_table  = src.id_table
        )

Code:

1
2
3
4
5
6
7
8
INSERT INTO table_cible
SELECT  *
FROM    (   SELECT  *
            FROM    table_source  src
        MINUS
            SELECT  *
            FROM    table_cible   cbl
        )

Si tu veux aussi faire des mises à jour, regarde du côté de la commande MERGE.

13/03/2017, 14h13
GoLDoZ

Merci al1_24.

J'avais pensé à quelque chose comme la première solution mais ça ne me semblait pas très performant. En fait, j'ai testé un DELETE WHERE EXISTS sur la table source avant d'insérer, mais du coup il compare ligne à ligne (les PK). Je voulais voir s'il n'y avait pas une autre façon de faire en occultant volontairement les erreurs.

Pour la seconde, je crois que le minus élimine les lignes exactement similaires et non seulement sur la clé primaire. Elle ne conviendra pas.
13/03/2017, 15h17
Baldrick

Bonjour,

Une autre solution est d'ajouter à votre ordre INSERT le hint /*+ IGNORE_ROW_ON_DUPKEY_INDEX(<nom_de_la_table>, <nom_de_l'index>)*/

Mais je ne saurais trop vous conseiller de faire un MERGE en bonne et due forme : j'ai découvert l'existence de ce hint sur un billet de blog où l'on expliquait les problèmes de performance en cas d'un grand nombre de valeurs invalides.
13/03/2017, 17h19
al1_24

Citation:

Envoyé par GoLDoZ

Pour la seconde, je crois que le minus élimine les lignes exactement similaires et non seulement sur la clé primaire. Elle ne conviendra pas.

Et que fais-tu alors des lignes de la source dont l'identifiant est déjà présent dans la cible mais dont les attributs sont différents ? Les modifications sont perdues ? La mise à jour est effectuée dans un autre traitement ?
14/03/2017, 12h03
GoLDoZ

@Baldrick: merci pour l'info, je vais tester ça. Le MERGE ne m'intéresse pas car les doublons sont censés être exceptionnel et doivent être supprimés. Du coup, peut-être pas de problème de perf par rapport à ce que tu dis.

@al1_24: les lignes supprimées ne sont pas censées arriver! C'est un patch pour faire le boulot que certains ne veulent pas faire... (je m'occupe d'un DWH et on n'est pas censé filtrer les données, sauf que régulièrement on a des fichiers pourris et il faut arbitrer. Dans le cas présent, ce sont des lignes qu'on a déjà reçues, et normalement un minus devrait suffire, mais j'ai standardisé les procédures et ça ne m'arrange pas).
14/03/2017, 15h04
mnitu
Citation:

Envoyé par GoLDoZ

...

Je ne souhaite pas faire d'insertion ligne à ligne car il s'agit de gros volumes (table source 3 millions, table cible 45 millions). Je souhaite simplement faire un "insert into... select * from... " (les deux tables ont la même structure) en ignorant les erreurs. L'insertion est dans une fonction PL/SQL mais je ne veux pas de curseurs ou autre sous peine de performances dégradées (il me semble).

Est-ce possible à l'aide d'options (que j'ignore) ?

Merci :D

D'abord en Oracle la notion de curseur est inhérente à toute requête SQL! En fait ce que vous voulez dire c'est un curseur au sens d'une structure de programmation propre au langage PL/SQL. Mais y compris dans ce cas c'est qui dégrade les performances ce n'est pas l'utilisation d'un curseur mais le traitement qui est conçu a travailler ligne à ligne au lieu de travailler en mode lot, c'est-à-dire ensemble des lignes.

Les solutions que vous pouvez employer sont les suivantes:
- en PL/SQL utilisez ForALL (travail par lot) avec la clause SAVE Exceptions
- en SQL utilisez errror logging
Une différence subtile existe entre les deux méthodes mais cela n'a pas d'importance dans notre discution.

Mais dans tout les cases vous devez comprendre que la simple présence des anomalies et leur traitement, peu importe la méthode, va contribuer à allonger le temps de traitement d'une manière plutôt linéaire: plus de erreurs signifiant plus de temps de traitement.

Si vos erreurs concernent uniquement des doublons alors vous pouvez envisager l'utilisation des solutions déjà proposées.