Discussion :

[f-demu01]

Bonjour,

je suis en pleine rédaction de ma première vraie procédure PL/SQL, dont le but est d'insérer dans une table tierce, à partir d'une table lignes de commandes, des données de quantité commandée, expédiée, reste à expédier... calculs effectués à partir d'une autre table.
Donc je récupère les lignes de commandes concernées via un curseur, et dans l'exécution de ce curseur, je calcule les positions citées ci dessus, que je stocke dans des variables, tant que le curseur ramène des lignes.

Ma question est de savoir quelle est la solution performante entre effectuer un insert des valeurs A, B, C, D à chaque passage dans la boucle, ou bien gérer un tableau de type RECORD alimenté à chaque passage, puis ne faire qu'une insertion à la fin du traitement du curseur?

J'ai effectué des recherches dans la FAQ et sur le forums PL/SQL, je n'ai pas trouvé d'éléments pertinents. Etant novice dans la création PL/SQL, je crains que certains termes que j'utilise soient inappropriés, aussi n'hésitez pas à pointer mes erreurs sémantiques

je vous remercie par avance de l'aide que vous pourrez m'apporter.

Cordialement,

[skuatamad]

Salut,

Effectivement faire des traitements de masse c'est beaucoup plus performant que les insert ligne à ligne cf tuto

[f-demu01]

merci skuatamad,

je pense effectivement que c'est le genre de comparaison que je n'arrivais pas à trouver. Il me reste à comprendre le fonctionnement

Merci en tous cas de ton aide,

Cordialement

[dgi77]

Il faut toujours garder à l'esprit que l'accès au disque dur est au moins 100 fois plus coûteux que l'accès à la mémoire vive, donc l'accès à une table est au moins 100 fois plus coûteux que l'accès à une variable mémoire.
D'où l'intérêt de faire les algos lourds en manipulant des tableaux, puis de faire un INSERT final, qui au passage peut gagner en performance en l'utilisant avec un FORALL (documenté dans le tutoriel PL/SQL du site).

[mnitu]

Il faut toujours garder à l'esprit que l'accès au disque dur est au moins 100 fois plus coûteux que l'accès à la mémoire vive, donc l'accès à une table est au moins 100 fois plus coûteux que l'accès à une variable mémoire.
D'où l'intérêt de faire les algos lourds en manipulant des tableaux, puis de faire un INSERT final, qui au passage peut gagner en performance en l'utilisant avec un FORALL (documenté dans le tutoriel PL/SQL du site).

Désolé, mais ce n'est pas une histoire d'accès au disque!
Au départ les données sont toujours sur le disque! La lecture ligne à ligne n'implique pas un accès au disque à chaque fetch! Etc.

[f-demu01]

Désolé, mais ce n'est pas une histoire d'accès au disque!
Au départ les données sont toujours sur le disque! La lecture ligne à ligne n'implique pas un accès au disque à chaque fetch! Etc.

Bonjour,

effectivement, la lecture ne me pose pas vraiment de problème, il s'agit en fait de l'insertion de données dans une autre table (données provenant de la lecture d'une table) qui me fait poser des questions: je vois deux façons de faire mon algo, soit en insérant ligne à ligne à chaque fetch, soit en gérant un tableau de records, qui fait l'insertion une fois toutes les lignes traitées et le fetch terminé.

Je vais donc a priori me tourner vers FORALL, avec tout de même une petite précision (pour être sûr): le fait de gérer le commit après mon forall effectuera bien un seul accès disque ?

d'avance merci

Cordialement

[f-demu01]

D'où l'intérêt de faire les algos lourds en manipulant des tableaux, puis de faire un INSERT final, qui au passage peut gagner en performance en l'utilisant avec un FORALL (documenté dans le tutoriel PL/SQL du site).

Bonjour dgi77,

j'ai imprimé, lu et testé le cours de SheikYerbouti concernant l'utilisation des collections et de FORALL. A plusieurs reprises, il est dit que le forall peut être fait directement si la collection (le type record dans mon cas) reprend la structure de la table à alimenter. Cela signifie-t-il que ecla ne fonctionne pas avec une collection dont le schéma ne reprend que certaines colonnes de la table ?

Cordialement,

[mnitu]

Bonjour,

effectivement, la lecture ne me pose pas vraiment de problème, il s'agit en fait de l'insertion de données dans une autre table (données provenant de la lecture d'une table) qui me fait poser des questions: je vois deux façons de faire mon algo, soit en insérant ligne à ligne à chaque fetch, soit en gérant un tableau de records, qui fait l'insertion une fois toutes les lignes traitées et le fetch terminé.

Je vais donc a priori me tourner vers FORALL, avec tout de même une petite précision (pour être sûr): le fait de gérer le commit après mon forall effectuera bien un seul accès disque ?

d'avance merci

Cordialement

Quand c'est possible la solution la plus performante est

Code :

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

1
2
3
 
Insert Into Table 
Select ... From T1, T2 etc.

Parfois cette solution peut être mise en pratique en utilisant les fonctions pipelined.

[SheikYerbouti]

...A plusieurs reprises, il est dit que le forall peut être fait directement si la collection (le type record dans mon cas) reprend la structure de la table à alimenter...

effectivement:

A partir d'oracle9i, les copies d'informations par blocs peuvent être effectuées directement dans les collections d'enregistrements

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
 
SQL> Declare
  2    TYPE   TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE ;
  3    tabrec TYP_TAB_TEST ;
  4    CURSOR C_test is select A, B From TEST ;
  5  begin
  6     -- chargement de la collection depuis la table --
  7     Select A, B BULK COLLECT into tabrec From TEST ;
  8     
  9     -- insertion de lignes à partir de la collection --
 10     Forall i in tabrec.first..tabrec.last
 11         insert into TEST values tabrec(i) ;
 12      
 13     -- mise à jour des données de la collection --
 14     For i in tabrec.first..tabrec.last Loop
 15         tabrec(i).B := tabrec(i).B * 2 ;    
 16     End loop ;
 17     
 18     -- utilisation du curseur --
 19     Open C_test ;
 20     Fetch C_test BULK COLLECT Into tabrec ;
 21     Close C_test ;
 22         
 23  End ;
 24  /
 
Procédure PL/SQL terminée avec succès.

[dgi77]

Au départ les données sont toujours sur le disque! La lecture ligne à ligne n'implique pas un accès au disque à chaque fetch! Etc.

Est-ce que j'ai dit que chaque fetch implique un accès disque ???
Toujours est-il, par contre, qu'il implique un accès disque tous les N fetch, ce qui justifie de passer par des variables mémoires. Je donnais une tendance pour que f-demu01 ait une idée du fonctionnement. Maitenant si tu as la science suffisante pour nous donner la valeur du N en fonction des différents paramètres à prendre en compte, libre à toi de disserter...
Au passage, pourrais-tu aussi nous expliquer l'intérêt du BULK COLLECT si le FETCH n'est pas gourmand en accès disque.
Pour f-demu01, le BULK COLLECT est le pendant du FORALL, mais quand tu "balances" les données dans les variables.

Désolé, mais ce n'est pas une histoire d'accès au disque!

Donc je suis désolé, mais je maintiens que c'est une histoire d'accès au disque!

Quand c'est possible la solution la plus performante est

Code :

INSERT INTO TABLE
SELECT ... FROM T1, T2 etc.
Parfois cette solution peut être mise en pratique en utilisant les fonctions pipelined.

Certes, mais si tu lis le post initial de f-demu01, tout laisse à penser que son algo est fonctionnellement trop compliqué pour être résolu en ensembliste.

Bonjour dgi77,

j'ai imprimé, lu et testé le cours de SheikYerbouti concernant l'utilisation des collections et de FORALL. A plusieurs reprises, il est dit que le forall peut être fait directement si la collection (le type record dans mon cas) reprend la structure de la table à alimenter. Cela signifie-t-il que ecla ne fonctionne pas avec une collection dont le schéma ne reprend que certaines colonnes de la table ?

Cordialement,

Je n'ai pas testé, mais je suppose que si les colonnes manquantes n'ont pas de contrainte NOT NULL, ça devrait passer

[f-demu01]

bonjour,

ne vous énervez pas, tout va bien. Je ne veux pas générer de tensions en demandant de l'aide

je crois que je comprends tout ce que vous avez écrit, cela m'a permis en plus d'élargir mes connaissances (notamment BULK COLLECT: oui, je code en PL/SQL depuis vraiment peu de temps... mais j'aime ca !!). Reste maintenant à terminer l'écriture !

merci de l'aide et de l'intérêt que vous avez tous apporté à ma question

Cordialement,

[dgi77]

Ne t'inquiète pas f-demu01...
J'ai simplement horreur que l'on me prête des discours que je n'ai pas tenus, surtout s'ils sont foireux
Et puis je n'aime pas trop aussi qu'on casse l'explication de quelqu'un sans aucun argumentaire, ni qu'on balance des concepts techniques assez compliqués sans les étayer... ça fait un peu trop "je m'la raconte en lisant PC Expert", à mon goût

[f-demu01]

ok, merci en tous cas.

je considère le problème comme résolu, en revanche, je pense ouvrir d'autres posts (pour d'autres questions, comme la gestion des RECORD avec le package DBMS_SQL...)

merci à tous,

Cordialement,

[mnitu]

Est-ce que j'ai dit que chaque fetch implique un accès disque ???
Toujours est-il, par contre, qu'il implique un accès disque tous les N fetch, ce qui justifie de passer par des variables mémoires. ...

Donc je suis désolé, mais je maintiens que c'est une histoire d'accès au disque!
...

Bref, ce n'est pas parce que c'est toi qui le dit que cella signifie que t'as raison (de plus crier ça n'aide pas). Apprenez à soutenir tes affirmations par des tests ou démonstrations.
Voilà un article (On cursor for loops) écrit par Steven Feurstein dans Oracle Magazine qui explique bien ce qui se passe en réalité.

[SheikYerbouti]

mnitu, le lien que tu donnes sur Oracle Magazine n'aboutit, chez moi, à aucun article en particulier

[mnitu]

mnitu, le lien que tu donnes sur Oracle Magazine n'aboutit, chez moi, à aucun article en particulier

Ni chez moi . C'est pour ça que j'ai indiqué le titre. Donc suivez Contents, Section 50 Developper, article PL/SQL Practices - On cursor for loops.

[dgi77]

Chez moi non plus, mais bon, je ne voulais pas insister...

Edit : je viens de voir ton post mnitu, fallait comprendre quand même...

[dgi77]

Ben je viens de lire l'article, rien de nouveau sur la comète...
Si je comprends bien, il préconise de faire un curseur, dans la limite de 100 lignes ramenées... en le couplant avec un BULK COLLECT (tiens tiens... accès disque ?), et je ne vois aucune explication sur ce qu'il se passe derrière d'un point de vue purement technique (accès disque par exemple).
Et puis... 100 lignes : SUPER !!!
Il arrive qu'on ait à en traiter des millions dans certaines petites boîtes...

Mais tout ça ne traite que de la récupération des données.
Le problème qui était posé ici est qu'à chaque ligne récupérée, il y a un traitement pour générer des données calculées, puis insérer le résultat dans une autre table. Mais là, ni toi mnitu ni l'article de donne de solution, je reste sur ma faim. C'est donc à ce sujet que je préconisais de générer les données calculées en travaillant sur des variables mémoires de type RECORD (ou TABLE), après que les données sources y ait été ramenées par des curseurs agrémentés de BULK COLLECT, puis de les insérer par bloc en fin de traitement (et non une à une => je reviens aux accès disques) via des FORALL INSERT.

Pour ce qui est de la com, sache que si tu as eu l'impression que je criais, c'est que j'étais dépité par tant de mépris, alors que tes solutions ne m'ont toujours pas convaincues, et que j'ai dû me battre pour avoir comme seul argument un article généraliste en anglais sur Oracle Magazine...

[SheikYerbouti]

Autre info, également est que le BULK COLLECT peut-être lui aussi "débité en tranches" via l'argument LIMIT, justement pour soulager les reins du rollback segment.

[mnitu]

Ben je viens de lire l'article, rien de nouveau sur la comète...
Si je comprends bien, il préconise de faire un curseur, dans la limite de 100 lignes ramenées... en le couplant avec un BULK COLLECT (tiens tiens... accès disque ?), et je ne vois aucune explication sur ce qu'il se passe derrière d'un point de vue purement technique (accès disque par exemple).
Et puis... 100 lignes : SUPER !!!
Il arrive qu'on ait à en traiter des millions dans certaines petites boîtes...

Si, l'article il précise que c'est le changement de contexte PL/SQL SQL qui intervient et non pas l’accès au disque. Bref, écrit un petit test qui utilise bulk collect et un autre qui ne l’utilise pas, fais la trace sql et regarde dans le fichier de trace la statistique « disk = number of physical reads of buffers from disk ».

Mais tout ça ne traite que de la récupération des données.
Le problème qui était posé ici est qu'à chaque ligne récupérée, il y a un traitement pour générer des données calculées, puis insérer le résultat dans une autre table. Mais là, ni toi mnitu ni l'article de donne de solution, je reste sur ma faim. C'est donc à ce sujet que je préconisais de générer les données calculées en travaillant sur des variables mémoires de type RECORD (ou TABLE), après que les données sources y ait été ramenées par des curseurs agrémentés de BULK COLLECT, puis de les insérer par bloc en fin de traitement (et non une à une => je reviens aux accès disques) via des FORALL INSERT.

Je pense que j'ai répondu à cette question. Le plus performante est de faire le traitement en pur SQL si c’est possible ; il arrive que certaines calculs peuvent être effectués en SQL. J’ai suggéré également qu’on pourrait employer le PL/SQL via les functions pipelined pour faire ces calculs. Ces fonctions peuvent utiliser le bulk collect à volonté. Mais sans savoir précisément les détails du traitement il est difficile de décider.

Pour ce qui est de la com, sache que si tu as eu l'impression que je criais, c'est que j'étais dépité par tant de mépris, alors que tes solutions ne m'ont toujours pas convaincues, et que j'ai dû me battre pour avoir comme seul argument un article généraliste en anglais sur Oracle Magazine...

Désolé, mais je ne vois pas ou j’étais méprisant, peux tu me la montrer ?

Pour ce qui concerne les tests les voilà.

Pour ce qui concerne les articles voilà un autre et ensuite il y en a pas mal des références dans la doc Oracle et sur le site www.oracle/technology comme ailleurs.

A propos peux tu me montrer un exemple, ou article qui explique les différences par le nombre d’accès au disque.