Discussion :

[Xavier2701]

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.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Oraman]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[rbaraer]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Xavier2701]

Merci pour vos réponse

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

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

Merci

[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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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...

[SheikYerbouti]

Voici une syntaxe interressante sur les grosses volumétries:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[remi4444]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Xavier2701]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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
code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[Xavier2701]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[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...