Comment faire un commit intermédiaire

Version imprimable

Bonjour

Système : Oracle 9.2

Contexte :Insertion de plus de 100 millions de ligne.

Problème : J'insert dans des tables des millions de ligne et je fait un commit à la fin de ma procédure.
Le probléme et que je test sur un serveur de dev avec quelques millions de ligne sur la table, donc pour l'instant cela fonctionne.
Mais comment fait on pour faire un commit intermédiare (tous les 3 millions de ligne).
Sans trop perdre de performance.
Citation:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 BEGIN sql_str := 'INSERT INTO DATAMART_NMD.'|| pTable || ' select * from DATAMAJ_NMD.' || pTablecopie || ' where DATMAJDMT > ADD_MONTHS(sysdate,' || pMonth || ')'; EXECUTE IMMEDIATE sql_str; EXCEPTION WHEN OTHERS THEN -- On renvoie un code d'erreur code_erreur:='99'; --RAISE; COMMIT;
Merci

18/09/2006, 19h03
Oraman

je te donne le principe à toi de le mettre en oeuvre 8O
tu rajoute une condition dans sql_str sur le rownum et tu met le tout dans une boucle

Code:

where rownum>i and rownum< i+3000000

tu incremente i de 3000000 à chaque fois

franchement côté perf je ne sais pas ce que ça donne
19/09/2006, 08h13
rbaraer

Citation:

Envoyé par Oraman

je te donne le principe à toi de le mettre en oeuvre 8O
tu rajoute une condition dans sql_str sur le rownum et tu met le tout dans une boucle

Code:

where rownum>i and rownum< i+3000000

tu incremente i de 3000000 à chaque fois

franchement côté perf je ne sais pas ce que ça donne

1ère remarque :

Oraman, rownum est une pseudocolonne qui donne le numéro de l'enregistrement dans le resultset, donc sans sous-requête avec un order by, je dirais que l'on n'est pas sûr :

- de traiter tous les enregistrements
- de ne pas en traiter en double

Qu'en dites-vous ?

2ème remarque :

Xavier2701, faire l'insertion en plusieurs fois a une implication qui peut avoir des conséquences : l'état de la table récupéré par chaque SELECT dans le INSERT...SELECT sera celui au début de chacune des requêtes et non l'état de la table au début de la 1ère requête. En conclusion, si la table source n'est PAS DU TOUT modifiée durant ce processus d'insertion, les 2 méthodes seront identiques (avec le bémol sur l'utilisation de rownum), sinon elles ne le seront pas. Cela peut être gênant ou non mais il faut en être conscient :).

3ème remarque :

Plutôt que d'utiliser rownum (car un order by sur une table avec des millions d'enregistrements n'est pas anodin), comme le seul critère de sélection est une date, je découperais l'intervalle de date actuel en plusieurs sous-intervalles.

Si vous avez la version Entreprise, vous pouvez envisager un partitionnement des données sur date et une exécution de la requête en mode PARALLEL. Cela peut très fortement améliorer les perfs sur une requête batch aussi importante. Peut-être quelqu'un d'autre pourra préciser ma pensée car n'ayant "qu'une" version standard, je n'ai jamais utilisé ces fonctionnalités.

Cordialement,

rbaraer
19/09/2006, 09h54
Xavier2701

Merci pour vos réponse:P

Je vais essayer la solution de Oraman je vais faire un test de perf.

Je ne peut pas partitionner ma table :cry: , si vous avez d'autre proposition je suis intéresser.

Merci
19/09/2006, 13h35
remi4444
J'ai vraiment des doutes moi aussi sur l'utilisation du rownum pour la raison suivante:

- La requête "select machin from truc where rownum > x" ne renvoi jamais rien si x est supérieur à 0!

- pour fonctionner il faut faire une requête en 2 niveaux:
Code:

1 2 3 4 select machin from ( select rownum N,machin from truc ) where N > i and N <= i+300000
Pour le premier bloc ça ira, mais pour le dernier, oracle devra stocker toute la table dans son espace temporaire pour en extraire les 300000 dernieres lignes donc ce n'est pas le rollback qui sera saturé mais le TEMP :aie:

Une solution plus fine est envisageable si la table a une clef primaire ou un index unique. Si cette clef primaire est une séquence, alors il suffit de faire des encandrement en fonction de cette séquence. Je suppose que le but n'est pas de faire des insertions avec 300000 lignes exactement mais simplement de frationner à la louche...

Si ce n'est pas une séquence, il serait peut etre interressant de faire un pré-traitement dans une table temporaire restreinte à la (aux) colonne(s) de la clef pour determiner les bornes à utiliser pour les insertions...

Voici une syntaxe interressante sur les grosses volumétries:

Code:

1
2
3
4
5
SELECT f.*
FROM ( SELECT e.*, ROWNUM r
        FROM (SELECT * FROM scott.parts) e
        WHERE ROWNUM <= 10000) f
WHERE f.r >= 1;

19/09/2006, 14h43
remi4444
8O

Je ne vois pas vraiment la différence...

Si on veut vraiment optimiser le rownum, il faut le faire sur un sous-select portant sur un ensemble de colonnes limité au classement qu'on veut faire puis faire une jointure, ainsi on économise des io et de l'espace temporaire.
Code:

1 2 3 4 5 6 7 8 9 select E.* from ( select empno from (select empno,rownum N from emp order by empno) where N > i and N <= i+10000 ) F, emp E where F.empno = E.empno

bon voila ce que j'ais essayer de faire :?

Mais j'ais une erreur cela ne passe pas avec le sql dynamique et il boucle q'une fois il insert mais 200000 et finit ?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 vNb_Boucle := CEIL(vNb_Enreg / 200000); vNb_Cpt := vNb_Boucle; WHILE vNb_Cpt > 0 LOOP vNb_Cpt := vNb_Cpt-1 ; sql_str := 'INSERT INTO DATAMART_NMD.'|| pTable || ' select * from DATAMAJ_NMD.' || pTablecopie || ' where DATMAJDMT > ADD_MONTHS(sysdate,' || pMonth || ') AND ROWNUM <= 200000'; EXECUTE IMMEDIATE sql_str; COMMIT; END LOOP;
désoler je suis débutant donc un peu de mal.

D'ailleurs je voit pas ou je place mon insert avec la synthaxe à SheikYerbouti:question:
code :
Code:

1 2 3 4 5 SELECT f.* FROM ( SELECT e.*, ROWNUM r FROM (SELECT * FROM scott.parts) e WHERE ROWNUM <= 10000) f WHERE f.r >= 1;

Bon je comprend pas je n'ais que les 10000 premier qui sont insérer, la boucle ce fait bien, les incrémentation des compteurs sont bon :mur:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 vNb_Boucle := CEIL(vNb_Enreg / 10000); vNb_Cpt := vNb_Boucle; vNb_cpt1 :=1 ; vNb_cpt2 :=10000 ; WHILE vNb_Cpt > 0 LOOP vNb_Cpt := vNb_Cpt-1 ; sql_str := 'INSERT INTO DATAMART_NMD.'|| pTable || ' select * from DATAMAJ_NMD.' || pTablecopie || ' where DATMAJDMT > ADD_MONTHS(sysdate,' || pMonth || ') AND ROWNUM >= ' || vNb_cpt1 || ' and ROWNUM <= ' || vNb_cpt2 || ''; EXECUTE IMMEDIATE sql_str; vNb_cpt1 := vNb_cpt1 + 10001 ; vNb_cpt2 := vNb_cpt1 + 10000 ; COMMIT; END LOOP;
je voit pas pourquoi alors que les variable cpt1 et cpt2 dans le where sont bien incrémenter qui n'insérer pas la suite des données dans ma table:question:

20/09/2006, 14h37
remi4444

"select machin from table where rownum > nimporte_quoi" ne te renverra jamais rien puisque le rownum compte les numéros de lignes renvoyées dans le curseur du resultat.

quand tu fais "select * from TABLE where rownum >= 10", c'est un peu comme si tu disais "je reçois des lignes les unes apres les autres mais je les jette au fur et à mesure tant que j'en ai pas stocké 10". Mais si tu jette la première, la suivante deviendra la première donc tu la jette aussi, etc... et tu n'atteindra jamais ton stock de 10 (je sais pas si j'ai réussi à me faire comprendre).

Donc pour que le "rownum" fonctionne, il faut que le système ait compté et stocké toutes les lignes précédentes). D'ou la difficulté, d'ou les requêtes imbriquées, et d'ou le risque de mauvaise perf et de saturation de l'espace temporaire....

Relis les exemples qu'on a donné, et remarque qu'il y a des requêtes portant sur des sous-requêtes qui elles contiennent le rownum, seul moyen pour que ça fonctionne...