Discussion :

[lachik]

Bonjour à tous !

J'ai une petite question, j'ai une requete du genre :

Code :

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

1
2
3
4
5
 
    BEGIN
        INSERT INTO NAME_RANKING (NAME_ID, RANKING_ID)
        SELECT NAME_ID, ranking_id FROM PRODUCT_NAMES WHERE NAME = class_string;
    END;

Cette requête insère des milliers de lignes (voir des millions) , mais forcément, il y a des soucis de performance (idem pour les delete).

Comment puis-je améliorer au mieux cette requête ? Utiliser "FORALL" ou une boucle "FOR" et faire un "COMMIT" tous les n passage ...?

Je suis un peu perdu dans tout ca (car avant de poster j'ai quand meme regardé ce qu'il se faisait

Merci

[star]

si tu as des indexes sur cette table supprime les avant de lancer l'opération ensuite recrée les
Pour une suppression totale des lignes d'une table le mieux est d'utiliser truncate
.

[lachik]

Merci @Star pour ta réponse.

En fait, il s'agit d'une procédure lancé via Java. Je sais pas s'il on peut (et si s'est conseillé) supprimer les index dans une procédure pour les "reconstruire" ensuite.

Pour les deletes, ils ne concernent pas toutes la table, seulement une partie genre entre 100 000 ligne et 1 million .

[mnitu]

La façon de faire est normalement optimale. Utiliser bulk insert ne devrait pas changer grand chose mais j'ai récemment survolé un article de Jonathan Lewis qui montre qu'en 11g dans certaines conditions le bulk se comporte mieux.
Ensuite, ce type de traitement peut s'accélérer en faisant un direct path insert via le hint APPEND. Mais cette méthode pose certaines restrictions comme l'absence des clés étrangères et triggers et a des implications qui faut bien évaluer.
Autre piste consiste dans le chargement en parallèle mais la machine doit avoir de ressources disponible pour y bénéficier sinon l'effet peut être contraire.
Concernant la suppression des masse il y un poste assez connue sur le site de Tom Kyte qui explique que si la proportion des enregistrements supprimes est importante il est mieux de créer une nouvelle table juste avec les enregistrements à garder, détruire l'ancienne table et renommer la nouvelle.
Et comme d'habitude quand vous avez un problème de performance il faut analyser le traitement dans son ensemble et trouver où le temps passe. C'est à partir de la que vous savez précisément sur quoi il faut agir.

[lachik]

Merci @mnitu

Pour la suppression, ca peut etre une bonne idée, je vais essayer.

Et concernant l'utilisation du "forall" est-ce bien ? et faire des commit par paquets, est-ce que ca serait plus rapide ?

[mnitu]

"ForAll" est synonyme de ce que j'ai appelé "bulk insert".
Commit par paquets non.

[lachik]

Très bien, merci pour ces pistes, je vais tester tout ça

[lachik]

Une autre question me tourne dans la tete : pourquoi est-ce que faire un insert d'1 million de lignes par paquets de 200 000 (commit tous les 200 000 lignes) est moins performant que de faire un insert ( et commit) d'1 million de lignes ?

[star]

il y a aussi l'option permettant d'inhiber l'historisation dans les redolog lors d'insertions ou de suppressions en masse
ALTER TABLE X NOLOGGING
http://docs.oracle.com/cd/B19306_01/...clauses005.htm
.

[mnitu]

Une autre question me tourne dans la tete : pourquoi est-ce que faire un insert d'1 million de lignes par paquets de 200 000 (commit tous les 200 000 lignes) est moins performant que de faire un insert ( et commit) d'1 million de lignes ?

Posons la question à l'inverse: pourquoi faire cinq fois insert de 200 000 ligne chacun suivi par un commit sera plus performant que faire un seul insert de 1 million des lignes suivi par un commit ?

[lachik]

[lachik]

@Star, merci pour ta réponse, enfin du concret !

[mnitu]

il y a aussi l'option permettant d'inhiber l'historisation dans les redolog lors d'insertions ou de suppressions en masse
ALTER TABLE X NOLOGGING
http://docs.oracle.com/cd/B19306_01/...clauses005.htm
.

Si vous suivez le lien que vous avez pointé

NOLOGGING is supported in only a subset of the locations that support LOGGING. Only the following operations support the NOLOGGING mode:


DML:

•Direct-path INSERT (serial or parallel) resulting either from an INSERT or a MERGE statement. NOLOGGING is not applicable to any UPDATE operations resulting from the MERGE statement.


•Direct Loader (SQL*Loader)

[lachik]

J'ai tenté d'utiliser FORALL amis sans succès, auriez-vous des petites idées svp ?

J'ai essayé de déclarer le SELECT dans un curseur mais ensuite je n'arrive pas à utiliser FORALL

Code :

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

1
2
 
PLS-00225: subprogram or cursor 'monCurseur' reference is out of scope

...comment faire ?

[mnitu]

Voila un exemple simplifié:

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
 
Declare
 Type Emp_va Is Varray(3) Of emp%Rowtype;
 trEmp        Emp_va;
 cstTailleLot constant Pls_Integer := 3;
 --
 Cursor crs_emp Is 
  Select *
    From emp;
 --
Begin
  open crs_emp;
  Loop
    Fetch crs_emp Bulk Collect Into trEmp Limit cstTailleLot;
    --
    ForAll idx In 1..trEmp.Count()
      Insert Into emp1 Values trEmp(idx);
    --  
    Exit When trEmp.count() < cstTailleLot;
  End Loop;
  close crs_emp;  
End;

Vous devez adapter bien sûr la taille du lot (et celle du tableau varray) avec une valeur entre 100 et 2000.

[lachik]

Merci mnitu !

Mais comment faire avec un select "multi-table" du genre :

Code :

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

1
2
3
4
5
6
7
 
SELECT 
FROM Table1 t1, Table2 t2, Table 3
WHERE t1.id=t2.id
   AND t2.id=t3.id
   AND t3.aaa= 'ok'
   AND t3.bbb= 'ok'.....

J’espère être clair dans ma question, sinon n'hésitez pas à me le dire !

[mnitu]

De la même manière

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
 
Declare
 cstTailleLot constant Pls_Integer := 3;
 --
 Cursor crs_emp Is 
  Select e.empno, 
         e.ename, 
         e.job, 
         e.mgr, 
         e.hiredate, 
         e.sal, 
         e.comm, 
         e.deptno,
         d.dname, 
         d.loc
    From emp e
         Join
         dept d
      On d.deptno = e.deptno;
  --
 Type Emp_va Is Varray(3) Of crs_emp%Rowtype;
 trEmp        Emp_va;
...

[lachik]

Merci pour tout !