Discussion :

[labolabs]

Bonjour,

J'ai besoin d'insérer plus de 6 millions de records dans une table (partitionnée). Pour l'instant mon script ne fait que des commandes INSERT normaux...et ceci me prend à peu près 2h pour tout charger !!

Y a t-il un moyen pour optimiser mes insertions?

(Les indexes sur ma table sont supprimés avant l'insertion, recrées après)

Merci d'avance.

[yong321]

Beaucoup de insert ... values ou un insert ... select? Si insert ... select, optimisez select (la requête) et utilisez insert /*+ append */.

Quelle est la principale wait event pendant l'insert?
select event, p1, p2, p3 from v$session_wait where sid = ...

[mnitu]

Bonjour,

J'ai besoin d'insérer plus de 6 millions de records dans une table (partitionnée). Pour l'instant mon script ne fait que des commandes INSERT normaux...et ceci me prend à peu près 2h pour tout charger !!

Y a t-il un moyen pour optimiser mes insertions?

Oui! Mais, il va falloir donner un peu plus d'informations sur le traitement: d'où le données provient, comment les insert sont fait, etc.

[labolabs]

Voici le scénario de mon script:

1 - Je récupère les données à partir d'une table T1
2 - Je fais des contrôles sur les champs de T1
3 - Si tous les contrôles sont valides, je fais un INSERT INTO T2 VALUES ...

[Waldar]

Quel genre de contrôles ?
Si ce sont des contrôles SQL, vous pouvez les intégrer directement à la requête initiale, puis tout insérer dans votre table en mode APPEND.

[mnitu]

Bref, il manque encore quelques infos...
Voici un lien qui explore l'utilisation des fonctions pipelined comme solution. Mais, dans un premier temps vous pouvez peut être vous limitez à l'utilisation du bulk et forall (si votre script est en PL/SQL bien sur).

[labolabs]

Merci pour vos réponses. Oui il s'agit de contrôles SQL, du genre:

* Si Champ 1 est NULL
* Si Champ 2 > Champ 3
* Si Champ 4 NOT IN (...) etc.

Le souci est qu'il y à peu près 100 contrôle de ce genre !! tout mettre dans une seule requête n'est pas la bonne idée à mon avis...

Je vais essayer l'insertion en mode APPEND puis les fonctions pipelined et tester les performances...Je vous tiens au courant du résultat.

[sgora]

Au fait, un petit détail, le more insert /*+ APPEND */ ne fonctionne pas avec la clause values, seulement avec des insert select...

[labolabs]

Je suis entrain de tester des insertions en masse en utilisant FORALL, sauf que j'ai un problème de PK !!

Dans la table où j'insère il y a une séquence qui doit s'incrémenter selon la valeur d'un autre champ; Exemple:
FIELD_NAME|SEQUENCE
ABC|1
ABC|2
ABC|3
ADB|1
ADB|2
...
(je ne peux pas utiliser de séquence définie dans Oracle pour faire de NEXTVAL !)

En gros j'utilise un SELECT MAX()+1 de de ma table, mais apparemment ceci ne marche pas !!

Quelqu'un a une idée comment éviter ce problème de PK?

Merci d'avance

[Waldar]

Il faut donner toutes les informations, notamment la description de la PK !
Ce que vous cherchez à obtenir de ce fait pas avec une séquence, utilisez la fonction row_number().

[pachot]

Bonjour,

Le souci est qu'il y à peu près 100 contrôle de ce genre !! tout mettre dans une seule requête n'est pas la bonne idée à mon avis...

Et pourquoi pas ? En tout cas entre une requête de 3 pages et 6 millions d'inserts, je n'hésite pas une seconde
Et si c'est trop, découper en plusieurs requêtes avec tables temporaires.

Aujourd'hui on peut faire de grosses requêtes, performantes, lisibles et maintenables (possibilité de factoriser par exemple avec WITH). Il y a beaucoup de chose qu'on peut faire en sql, et qui sont faite pour ca (comme row_number() que suggère Waldar)

Cordialement,
Franck.

[mnitu]

...
Et si c'est trop, découper en plusieurs requêtes avec tables temporaires.

...

Frank,
Ca c'est plutôt pour plomber les performances.

[labolabs]

Je viens de tester les insertions en utilisant les fonctions pipelined (lien): le résultat est très satisfaisant, je gagne à peu près 1/3 du temps que je faisait avant

Est ce qu'il est possible d'utiliser ces fonctions pipelined pour insérer dans plusieurs tables à la fois? Pour l'instant voici la commande utilisée:

Code :

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

1
2
3
4
5
6
INSERT  /*+ PARALLEL(f, 10) */
INTO    T1 f 
	  ( code, name )
SELECT  code, name
FROM    TABLE(
			MY_PACKAGE.LOAD_T ( CURSOR(SELECT /*+ PARALLEL(p, 10) */ * FROM TABLE_SOURCE p)));

Je devrais tester si TABLE_SOURCE.champ1 = 'Y', j'insère dans T1, sinon je devrais insérer dans T2. C'est possible de le faire?

[Waldar]

Vous pouvez faire un insert conditionnel :

Code :

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

1
2
3
4
5
6
INSERT
  WHEN champ1 = 'Y' THEN
  INTO T1 VALUES (...)
  ELSE
  INTO T2 VALUES (...)
SELECT ...

[labolabs]

Y a t-il une différence entre l'instruction:

Code :

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

1
2
3
4
5
6
INSERT
  WHEN champ1 = 'Y' THEN
  INTO T1 VALUES (...)
  ELSE
  INTO T2 VALUES (...)
SELECT ...

et celle ci ?

Code :

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

1
2
3
4
5
6
INSERT FIRST
  WHEN champ1 = 'Y' THEN
  INTO T1 VALUES (...)
  ELSE
  INTO T2 VALUES (...)
SELECT ...

[Waldar]

Faisons le test :

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
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
create table t1 (nm number(1));
create table t2 (nm number(1));
 
insert 
  when nb <= 1 then
  into t1 (nm) values (nb)
  when nb <= 2 then
  into t2 (nm) values (nb)  
select 1 as nb from dual union all
select 2       from dual;
 
select * from t2; 
 
NM
1
2
 
rollback;
 
insert all
  when nb <= 1 then
  into t1 (nm) values (nb)
  when nb <= 2 then
  into t2 (nm) values (nb)  
select 1 as nb from dual union all
select 2       from dual;
 
select * from t2; 
 
NM
1
2
 
rollback;
 
insert first
  when nb <= 1 then
  into t1 (nm) values (nb)
  when nb <= 2 then
  into t2 (nm) values (nb)  
select 1 as nb from dual union all
select 2       from dual;
 
select * from t2; 
 
NM
2

Donc sans mot clef c'est un INSERT ALL.

Vous avez raison de soulever ce point car dans votre cas c'est bien un INSERT FIRST dont il semble que vous ayez besoin.

[labolabs]

Merci pour la réponse Waldar. En effet j'aurais besoin d'un INSERT FIRST dans mon cas !

Aussi une autre remarque qui me vient à l'esprit ! Si je reprends votre example (modifé):

Code :

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

1
2
3
4
5
6
7
8
9
10
CREATE TABLE T_COURANT (nm number(1), statut char(4));
CREATE TABLE T_HISTORIQUE (nm number(1), statut char(4));
CREATE TABLE TABLE_SOURCE (nm number(1), statut char(4));
 
INSERT 
  when statut = 'COUR' then
  INTO T_COURANT (nm, statut) VALUES (nm, statut)
  when statut = 'HIST' then
  INTO T_HISTORIQUE (nm, statut) VALUES (nm, statut)  
SELECT * FROM TABLE_SOURCE;

Il me semble dans ce cas qu'on ne fait pas des insertions au fur et à mesure, mais qu'on insère d'abord toutes les lignes qui ont statut = 'COUR' dans la table T_COURANT, puis après commence l'insertion dans la table T_HISTORIQUE quand statut = 'HIST' !!! sachant que ma table TABLE_SOURCE n'est pas triée (on peut avoir 3 lignes avec statut = 'COUR' puis une avec 'HIST' puis une autre 'COUR' etc.).

Je veux dire qu'il y aura pas de différence côté performances si j'avais écris un code comme celui là:

Code :

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

1
2
3
4
5
6
7
8
9
 
INSERT 
  INTO T_COURANT (nm, statut)
  SELECT * FROM TABLE_SOURCE where statut = 'COUR';
 
 
INSERT 
  INTO T_HISTORIQUE (nm, statut)
  SELECT * FROM TABLE_SOURCE where statut = 'HIST';

Je pensais qu'avec un INSERT WHEN on pouvait gagner du temps dans les insertions !

[Waldar]

Dans le premier cas vous lisez une fois les blocks, dans le second deux fois.
Après ça dépend de paramètres de cache, de partition, d'index etc etc etc.

Le plus simple (pour vous), c'est encore de comparer les deux solutions !

[labolabs]

J'avais fais le test pour insérer 2.5 millions de lignes !

Le premier cas a mis 8 minutes 10 secondes, le deuxième 8 minutes 18 secondes.

Après comme vous dîtes ça dépend des paramètres du cache, des indexes, etc.

Je vais utiliser finalement le INSERT FIRST WHEN ...

[mnitu]

...
Il me semble dans ce cas qu'on ne fait pas des insertions au fur et à mesure, mais qu'on insère d'abord toutes les lignes qui ont statut = 'COUR' dans la table T_COURANT, puis après commence l'insertion dans la table T_HISTORIQUE quand statut = 'HIST' !!! ...

Qu’est-ce qu'il vous fait croire ça ? Le plan d'exécution ne montre guerre de trace d'un pareil comportement.