Insérer en masse dans la base de données Oracle: Qu'est-ce qui est le mieux: pour boucle de curseur ou un simple Select?

La règle générale est que, si vous pouvez le faire en utilisant une seule instruction SQL au lieu d'utiliser PL/SQL, vous devriez. Il sera généralement plus efficace.

cependant, si vous avez besoin d'ajouter plus de logique procédurale (pour une raison quelconque), vous pourriez avoir besoin D'utiliser PL/SQL, mais vous devriez utiliser des opérations de masse au lieu du traitement ligne par ligne. (Note: dans Oracle 10g et plus tard, votre boucle FOR utilisera automatiquement BULK COLLECT pour récupérer 100 lignes à la fois; cependant votre instruction insert sera toujours fait ligne par ligne).

e.g.

DECLARE
   TYPE tA IS TABLE OF FOO.A%TYPE INDEX BY PLS_INTEGER;
   TYPE tB IS TABLE OF FOO.B%TYPE INDEX BY PLS_INTEGER;
   TYPE tC IS TABLE OF FOO.C%TYPE INDEX BY PLS_INTEGER;
   rA tA;
   rB tB;
   rC tC;
BEGIN
   SELECT * BULK COLLECT INTO rA, rB, rC FROM FOO;
   -- (do some procedural logic on the data?)
   FORALL i IN rA.FIRST..rA.LAST
      INSERT INTO BAR(A,
                      B,
                      C)
      VALUES(rA(i),
             rB(i),
             rC(i));
END;

ce qui précède a l'avantage de minimiser les commutateurs de contexte entre SQL et PL/SQL. Oracle 11g a également un meilleur support pour les tables d'enregistrements afin que vous n'ayez pas à avoir une table PL/SQL séparée pour chaque colonne.

aussi, si le volume de données est très grand, il est possible de changer le code pour traiter les données en lots.

si votre segment rollback / undo peut accommoder la taille de la transaction, alors l'option 2 est meilleure. L'Option 1 est utile si vous n'avez pas la capacité de roll-back nécessaire et que vous devez casser le gros insert en commits plus petits de sorte que vous n'obtiendrez pas de segment de roll-back/undo trop petites erreurs.

un simple insert / select comme votre 2ème option est de loin préférable. Pour chaque insertion dans la 1ère option, vous avez besoin d'un commutateur de contexte pl/sql vers sql. Faites une analyse avec trace / tkprof et examinez les résultats.

si, comme le mentionne Michael, votre rollback ne peut pas gérer la déclaration, alors demandez à votre dba de vous en donner plus. Le disque est bon marché, tandis que les résultats partiels qui viennent de l'insertion de vos données dans les passes multiples est potentiellement assez cher. (Il n'y a presque pas d'annulation associée à un insérer.)

je pense que dans cette question manque une information importante.

combien d'enregistrements allez-vous insérer?

1. si de 1 à cca. 10.000 alors vous devez utiliser la déclaration SQL (comme ils ont dit qu'il est facile à comprendre et il est facile d'écrire).
2. si de l'ACC. 10.000 à l'acc. 100.000 alors vous devez utiliser le curseur, mais vous devez ajouter de la logique pour commettre sur chaque 10.000 enregistrements.
3. si de l'ACC. 100.000 à des millions de personnes, alors vous devriez utiliser en vrac collecter pour de meilleures performances.

Comme vous pouvez le voir en lisant les autres réponses, il ya beaucoup d'options disponibles. Si vous faites juste des lignes < 10k vous devriez aller avec la deuxième option.

en bref, pour environ > 10k tout le chemin pour dire un <100k. C'est une sorte de zone grise. Beaucoup de vieux types vont aboyer sur de gros segments. Mais honnêtement, le matériel et le logiciel ont fait des progrès étonnants à l'endroit où vous pouvez être en mesure de vous en tirer avec l'option 2 pour beaucoup d'enregistrements si vous exécutez le code seulement quelques fois. Sinon, vous devriez probablement commettre toutes les lignes 1k-10k ou ainsi. Voici un extrait de code que j'utilise. Je l'aime parce qu'il est court et je n'ai pas à déclarer un curseur. De plus, il a les avantages de vrac collect et forall.

begin
    for r in (select rownum rn, t.* from foo t) loop
        insert into bar (A,B,C) values (r.A,r.B,r.C);
        if mod(rn,1000)=0 then
            commit;
        end if;
    end;
    commit;
end;

j'ai trouvé ce lien à partir de l'oracle site qui illustre les options plus en détail.

Vous pouvez utiliser:

en vrac collecter avec tout ce qui s'appelle Bulk binding.

parce que PL / SQL forall l'opérateur accélère de 30x pour les inserts de table simples.

BULK_COLLECT et Oracle FORALL ensemble, ces deux fonctionnalités sont connus comme Bulk Binding. Les liants en vrac sont une technique PL / SQL où, au lieu de plusieurs individus SELECT,INSERT,UPDATE ou DELETE les instructions sont exécutées pour extraire, ou stocker des données, à la table, toutes les les opérations sont effectuées immédiatement, en vrac. Cela évite la commutation de contexte que vous obtenez lorsque le moteur PL/SQL doit passer au moteur SQL, puis retour au moteur PL/SQL, et ainsi de suite, lorsque vous accédez individuellement les rangées une à la fois. À n'en vrac se lie avec INSERT,UPDATE et DELETE déclarations, vous placez l'instruction SQL à l'intérieur d'un PL/SQL FORALL déclaration. À n'en vrac se lie avec SELECT déclarations, vous incluez le BULK COLLECT clause SELECT déclaration au lieu d'utiliser INTO.

Il améliore les performances.