Utilisation de global temporary tables

**LEK** · 12/09/2012, 16h44

Bonjour,
j'ai une table de travail générique (sous Oracle 10gr2) qui est du type global temporary tables
et qui possède 100 champs du type "CHAMP1 VARCHAR2(1000),CHAMP2 VARCHAR2(1000),...." . Elle permet de charger un ensemble de données et d'effectuer un certains nombre de traitements avant de faire un enregistrement dans les tables finales....
J'ai constaté avec son utilisation un problème de performance qui a été résolu en réduisant le nombre de champ et leur longueur...
Cela dit je pensais que les types varchar2 ne prenait que la place nécessitée par le stockage des données?
Comment connaître la limite acceptable de champ et leur longueur que je peux utiliser ?

Merci pour votre aide.

**mnitu** · 12/09/2012, 17h03

Le problème n’est pas la table qui a 100 colonnes de type varchar2(1000) mais plutôt ce qu’on fait avec, plus précisément les traitements dans quels elle est impliqué.

La résolution avec réduction des colonnes et leur taille semble plutôt à la blague avec les 4 ingénieurs : mécanicien, chimiste, électronique, informaticien et la voiture qui tombe en panne

« Et si on ouvre les portières et on les refermes par la suite? »

**LEK** · 12/09/2012, 17h27

Salut, merci de ta réponse et pour la blague... Je conaissais pas mais google est mon ami

Moi aussi je n'ai pas été complètement honnête dans mon optimisation j'ai aussi étudié le plan d'exécution de certaines requêtes que j'ai modifiées car elles effectuaient plusieurs full scan de cette table (plusieurs cas de vérificationssur des valeurs de la même table qui étaient traitée au cas par cas en joignant le tout par des unions... et j'ai retraduit l'ensemble en ne parcourant plus qu'une fois la table et en traitant mes vérifications champ par champ avec des case when... cela dit dur/dur à cause de la limitation sur le nombre de case when possibles...)
Enfin, si je posais la question c'est qu'effectivement je ne sais pas si tailler les tables de cette manière à néanmoins un impact sur mon travail (le chargement en mémoire des lignes de cette table??).

**pachot** · 12/09/2012, 20h57

Bonjour,

Envoyé par LEK

Cela dit je pensais que les types varchar2 ne prenait que la place nécessitée par le stockage des données?

C'est exact, la taille maximum définie pour les colonnes ne change rien au stockage.

Par contre, elle peut être utilisée dans certains cas par l'optimiseur lorsqu'il estime la taille d'un résultat. Et si l'estimation est loin de la réalité, alors le plan d'exécution choisi peut être mauvais.
C'est la seule explication qui me vient à l'esprit ici. Et pour voir si c'est une explication possible, j'ai créé une global temporary table table avec 101 colonnes VARCHAR2(1000) et voici le plan d'exécution d'un 'select *' sur la table vide sans statistiques:

Code :

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

1
2
3
4
5
6
7
8
9
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 | 50702 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 | 50702 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

Il a fait du dynamic sampling puisqu'il n'y a pas de stats. Il a bien vu qu'il y avait peu de ligne (en fait 0 mais estimation à 1).
Par contre, n'ayant pas de lignes pour voir la taille moyenne d'une ligne, il estime à: 50702 octets:
Il y a 100 colonnes de taille maxi 1000 octets, et il semble qu'il estime en moyenne qu'elles sont à moitié rempli (50702/101=502 octets - c'est exactement la taille d'une chaîne de 500 caractères).

Si j'insère 10 lignes avec juste 1 caractère dans la première colonne:

Code :

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

1
2
3
4
5
6
7
8
9
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |   495K|     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |    10 |   495K|     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

Dynamic sampling a bien vu 10 lignes, mais n'a pas regardé la taille moyenne d'un enregistrement. Il estime toujours des lignes à 50000 octets.

Maintenant je calcule les stats (ma table est en on commit preserve rows pour qu'il y ait toujours les lignes au moment du gather_table_stats):

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |    10 |    20 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Là, plus de dynamic sampling, et surtout une bonne estimation de la taille moyenne d'une ligne: 2 octets par ligne.

Donc je pense que le problème de performance vient d'un mauvais plan d'exécution.
Sur ce type d'utilisation, il serait peut-être judicieux de calculer les stats une fois que la table est remplie.

Cordialement,
Franck.

**LEK** · 12/09/2012, 21h43

Merci pour l'explication claire,nette et pour les exemples Pachot!
Je vais de ce pas rajouter une collecte de statistiques suite au remplissage de ma table (sans compter qu'elle ne possède aucun index vu qu'elle est utilisée pour stocker tout type de données).
Par contre je voulais faire confirmer mon optimisation de requête.
J'ai remplacé une requête (avec code généré) du style :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 select ' Le champ : '|| champ2_name ||' doit avoir une 
  longueur max de 5 ' as message 
 from g_wrkTable
 where  
  type_champ='CODEPOSTAL' and Length (champ2)> 12 
UNION 
select ' Le champ : '|| champ6_name ||' doit figurer dans la liste : 1,2.' as message
g_wrkTable
 where  
  type_champ= 'HABITATION_TYPE' and champ6
  not in (1,2)  
union ...

par une autre approche avec des case when:

Code :

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

1
2
3
4
5
6
7
 
 select 
      case when	  type_champ='CODEPOSTAL' and Length (champ2)> 12  then ' Le champ : '|| champ2_name ||' doit avoir une longueur max de 5 '
	when   type_champ= 'HABITATION_TYPE' and champ6   not in (1,2)  then ' Le champ : '|| champ6_name ||' doit figurer dans la liste : 1,2.' as message
      end
 as message 
 from g_wrkTable

Car d'après mon analyse du plan m'indiquait que les unions faisaient de multiples full scan... Du coup je suis quand même limité par le nombre de case when possibles (127) et je dois refaire des unions ensuite ou générer des case when imbriqués...
Que pensez vous de cette "optimisation" ?
Y-a-til plus simple comme approche ? (Le but étant de générer des messages d'erreurs si de mauvaises données vont être importées dans mon système...)

Merci d'avance pour votre aide,
Lek

**skuatamad** · 12/09/2012, 22h20

Envoyé par LEK

Enfin, si je posais la question c'est qu'effectivement je ne sais pas si tailler les tables de cette manière à néanmoins un impact sur mon travail (le chargement en mémoire des lignes de cette table??).

Comme on ne connait pas ton travail, difficile de répondre mais c'est possible :
How are varchar stored internally in a block or page

o Ok, so the code prepares a query that select 10 columns that are varchar2. The
developer, for performance would like to array fetch (very important). They would like
to array fetch say 100 rows (very very typical). So, we have 4000 times 10 times 100 =
alsmost 4meg of RAM the developer must allocate!!

Now consider if that where 10 varchar2(80) fields (its probably much smaller then that)
-- thats about oh 78k. Ask the "data modeler" how much RAM he is willing to kick in for
this system....

Que pensez vous de cette "optimisation" ?

C'est mieux que les UNION qui devraient être des UNION ALL.
Sinon ça ressemble à des contraintes (taille de la colonne, CHECK...) qui peuvent/devraient être implémentées dans la base.

**LEK** · 12/09/2012, 23h03

Merci pour le lien, je n'avais pas vu ce billet sur le site de Tom...
Et cela confirme bien l'impression que j'ai eu dans mes tests ce qui m'a fait effectivement réduire les champs en nombre et taille (en me calant sur les nombres max que j'ai à gérer aujourd'hui)...
Touché : pour le "union all" ....
Cela dit avec mes case when, je risque aussi de reproduire le même problème en ne retenant qu'un cas d'erreur...
Pour les check et autres je suis d'accord, mais il me faudrait créer des tables de travail à la volée, insérer et récupérer en bloc les erreurs... Enfin c'est ce que je crois que tu veux dire ;-)
Et encore je pense que je ne pourrais trouver qu'un cas d'erreur : la première contrainte ou premier check qui pête...
Car dans ce que je décris je suis dans une première étape d'un processus d'"ETL" maison... suite à mes vérifs/check, je fais quelques traitements de transformations et renseigne un certain nombre de tables cibles...

**skuatamad** · 13/09/2012, 00h16

Envoyé par LEK

Pour les check et autres je suis d'accord, mais il me faudrait créer des tables de travail à la volée, insérer et récupérer en bloc les erreurs... Enfin c'est ce que je crois que tu veux dire ;-)

Rassure toi je ne proposerai jamais une folie pareille

Je voyais plutôt les contraintes sur la table temporarire ou sur les tables destinations avec par exemple du dml error logging, mais dur à dire si c'est pertinant.

Sinon s'il y a des centaines de case à faire, j'envisagerais aussi une fonction pipelined :

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
SQL> create table g_wrkTable (type_champ varchar2(30), champ2 varchar2(30));
 
Table created.
 
SQL> 
SQL> insert into g_wrkTable (type_champ, champ2) values ('CODEPOSTAL',lpad('x',15,'x'));
 
1 row created.
 
SQL> insert into g_wrkTable (type_champ, champ2) values ('CODEPOSTAL',lpad('x',5,'x'));
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> 
SQL> create or replace function validateData
  2  return sys.odcivarchar2list
  3  pipelined is
  4    l_message varchar2(500);
  5  begin
  6    for c in (select type_champ, champ2 from g_wrkTable) loop
  7      if (c.type_champ = 'CODEPOSTAL' and length(c.champ2) > 12) then
  8        l_message := ' (type_champ = '||c.type_champ||' , champ2 = '||c.champ2||') Le champ : champ2 doit avoir une longueur max de 5 ';
  9        pipe row(l_message);
 10      end if;
 11    end loop;
 12  end;
 13  /
 
Function created.
 
SQL> show err
No errors.
SQL> select * from table(validateData)
  2  /
 
COLUMN_VALUE
------------------------------------------------------------------------------------------------------------------------
 (type_champ = CODEPOSTAL , champ2 = xxxxxxxxxxxxxxx) Le champ : champ2 doit avoir une longueur max de 5
 
SQL>

En plus ça correspond plus au code de base avec UNION car en fait avec les CASE il y a autant de ligne que dans la table (dans l'exemple allégé présenté il faudrait en plus refiltrer sur message is not null)

**LEK** · 13/09/2012, 01h13

Merci skuatamad,
pour l'idée de la fonction pipeline je n'y avais pas pensé car j'étais focusé sur de la génération en pur sql (sans switch de contexte SQL/PL) mais il est vrai que ce type de fonction garde de bonne perf. Cela dit l'ensemble des contrôles de la requête initiale était généré en dynamique suivant une table de paramétrage qui m'indique quel champ doit avoir quelle longueur, de quel type il doit être, etc...
Pour les contraintes, j'en ai sur mes tables de destination mais je voulais faire un travail en amont pour loggué en clair les erreurs de formats,etc... Je n'en ai pas sur la table temporaire car elle est générique et réutilisé dans x import différents.... Cela dit je pourrais effectivement créé une table temporaire par import, mais là encore pour chaque tenants (ie. clients de mon appli) de mon appli je possède des contraintes différentes, d'où ma solution générique initiale avec une table de paramétrage des vérifs à effectuer par tenants...
Pour le dml_error_logging c'est bien de ce dont je parlais lorsque je disais "récupérer les erreurs en bloc" ou encore en faisant des forall+save exception...
Et oui tu as raison avec cette méthode je retest effectivement tous les cas de figures ;-)
Je m'en vais tester avec une fonction pipeline et faire quelques mesures,
merci encore.

**mnitu** · 13/09/2012, 09h02

@pachot
Il est vrai que en manquant des statistiques l’estimation en octets de la taille du résultat fait par l’optimiseur est loin de la réalité mais votre exemple montre surtout que dans tous les conditions l’optimiseur choisi le seul plan possible dasn ce cas qui est un full table scan !
Et c’est plutôt ça qui pointe vers le vrai problème de l’utilisation (abusive) des tables temporaires. Souvent pour « ne pas se prendre la tête » ces tables sont impliqués dans des traitements de type : prends ces données d’ici, prends les autres de là, supprimes ceux qui n’ont pas un bon karma, met à jour ceux qui sont bleu, etc. Quand c’est fini : fait une jointure avec ces autres tables et modifiez cette autre table ou encore faite un joli rapport. De plus pour optimiser les insertions des données dans la table temporaire nous n’allons surtout pas créer des indexes parce qu’on sait bien qua ça ralentie d’environ 25% les insertions. Et les gens oublie que ces tables n’ont pas des statistiques, que sans index le seul accès possible est full table scan et que c’est type accès concerne toutes les opérations qu’on fait subir à la table une fois que les premières données ont été chargées.

@skuatamad
Je suis bien d’accord que le commentaire du Tom montre par un calcul simple que le client aura besoin de plus de mémoire parce que les zones sont taillée suivant le principe « juste au cas où » !
Mais je pense que notre ami a bien des autres chats à fouetter dans son traitement.
L’idée d’employer des fonctions pipelines peut être juste mais il faut garder à l’esprit que ni ces fonctions ne possède pas des statistiques « par défaut » et qu’il faut faire quelque chose si on les implique dans le mêm type de traitement que les tables temporaires ! Et la façon de l’employer dans un traitement de type ETL doit être de type « enchainement des fonctions de transformation des données » ; c’est dans ce cas d’utilisation qu’elles sont vraiment puissantes.

@LEK
Les vérifications que vous faites dans votre traitement me semblent inutiles et superflues.

**Rei Ichido** · 13/09/2012, 09h32

Effectivement, si la contrainte du CASE vous est problématique, on peut se dire qu'il y a beaucoup de tests à faire ... et que si chaque test implique un full scan, ça pose un très sérieux problème de performance.

S'il y a beaucoup de tests à faire, personnellement je ferai ça en deux temps avec du code binaire plutôt que directement du texte d'erreur (qui en plus ne ramènera que la première erreur rencontrée pour chaque ligne).

Code :

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

1
2
3
4
5
6
7
 
SELECT 
      case when	  type_champ='CODEPOSTAL' AND Length (champ2)> 12  then 1 END +
	case when   type_champ= 'HABITATION_TYPE' AND champ6   NOT IN (1,2)  then 2 +
      case when (autre condition) then 4 +
     ... AS erreurs
FROM tableTempo

Et qui ensuite se décode facilement avec la représentation binaire.
S'il y a trop de tests il faudra probablement découper en plusieurs parties (erreurs1, erreurs2, etc.).

**LEK** · 13/09/2012, 11h08

@mnitu :

Les vérifications que vous faites dans votre traitement me semblent inutiles et superflues

Pour expliquer ma situation :
Les traitements d'import que je réalise se font complètement ou pas du tout (rollback). Lorsque mon traitement d'import plante avec une erreur du type
"ORA-00001: violation de contrainte unique (GEST_CLI.PK_ADDR_CLI)"
mon client prend son téléphone et m'appelle pour que je débuggue et lui indique qu'est ce qui ne va pas dans son set de données.
Les journées où je n'ai pas de chance et ou trois/quatre clients importent des données avec erreur dans mon système, je passe une partie de la journée au téléphone l'autre à rejouer les imports pour leur donner la raison du problème... Ils réimportent me rappellenty pour la ligne suivante.. pour le fichier suivant... A la fin de la semaine, je n'ai rien fait de productif, alors je préfère généré un fichier de log explicite pour le néophite SGBD qui pour moi n'a pas besoin d'avoir son permis oracle pour utiliser mon appli (ou une quelconque appli d'ailleurs)....
Que proposes tu de plus efficace ?
Par contre concernant les optimisations nécessaires dans l'uitilisation des fonctions pipeline (un hint pour obtenir des stats?) et le type « enchainement des fonctions de transformation des données » je suis interessé par tout conseil, ou lien (je regarde sur google en parallèle)

**LEK** · 13/09/2012, 13h26

@Rei Ichido :
Le bit bashing pourrait être une bonne idée mais j'ai vraiment de nombreux cas que j'essaie de traiter dynamiquement et je pense que la solution que tu propose serait plus adaptée si je pouvais n'avoir qu'un nombre déterminés de cas potentiels à vérifiés, non ?

@mnitu :
Sérieusement, quelle serait selon toi la meilleure manière de s'y prendre ?
Créer des tables sur mesures pour chaque imports potentiels et y charger mes données importées en appliquant le dml_error_logging comme l'indique skuatamad ? Dans ce cas là je peux récupérer qu'un message d'erreur oracle sur la première contrainte/check qui passe en erreur par ligne de mon set de données importé (ça veut dire aussi que je n'indique pas tous les autres cas d'erreur) ... Or ce message "technique" n'est pas transmissible tel quel à mes clients...

**mnitu** · 13/09/2012, 14h14

Il est très compliqué des données une solution à un problème qu’on ne comprend pas. Commence donc par décrire les plus détaillé possible ce que tu veux accomplir.
Jusqu’au là j’ai seulement compris que tu fais un traitement impliquant une table temporaire et que ton traitement avait quelques problèmes de performance qui semble à avoir être résolues après modification de la taille des colonnes de la table temporaire.

**Rei Ichido** · 13/09/2012, 14h40

Envoyé par LEK

@Rei Ichido :
Le bit bashing pourrait être une bonne idée mais j'ai vraiment de nombreux cas que j'essaie de traiter dynamiquement et je pense que la solution que tu propose serait plus adaptée si je pouvais n'avoir qu'un nombre déterminés de cas potentiels à vérifiés, non ?

Pas forcément, vous pouvez ensuite faire une jointure avec une table d'erreur appropriée (éventuellement remplie à la volée, de préférence remplie une bonne fois pour toute pour chaque traitement différent).

Code :

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

1
2
3
4
5
6
7
 
INSERT INTO tableDesErreurs (idTraitement, code, libelle)
VALUES (1,1,'Le champ : '|| champ2_name ||' doit avoir une 
  longueur max de 5');
 
INSERT INTO tableDesErreurs (idTraitement, codeErreur, libelleErreur)
VALUES (1,2,' Le champ : '|| champ6_name ||' doit figurer dans la liste : 1,2.');

Code :

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

1
2
3
4
5
 
SELECT s.noLigne, t.libelleErreur
  FROM selectErreurs s
          INNER JOIN tableDesErreurs t
                   ON BITAND(s.erreur,t.codeErreur) = t.codeErreur

**Mohamed.Houri** · 13/09/2012, 14h44

Bonjour Franck,

Envoyé par pachot

Code :

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

1
2
3
4
5
6
7
8
9
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 | 50702 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |     1 | 50702 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Note
-----
   - dynamic sampling used for this statement (level=2)

Code :

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

1
2
3
4
5
6
7
 
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    10 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TEST |    10 |    20 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Ton exemple montre bien la bonne estimation de la taille de la ligne faite par le CBO lorsque celui-ci dispose des statistiques adéquate (avg_col_len dans ce cas) ; il n'en reste pas moins que le calcul des statistiques n'a manifestement eu aucun effet sur le plan d'exécution dans ce cas.

En effet, le coût de l'utilisation d'une table ne dépend pas de la taille de ses colonnes mais plutôt du paramètre db_file_multiblock_read_count et du fait de l’utilisation des statistiques systèmes ou non (workload statistics or noworkload statistics).

Par contre, la taille d’une colonne (avg_col_len) a une grande importance pour le CBO pour le choix de la table directrice (build table) lors d’une jointure du type HASH.

Elle a aussi son importance lors d’un tri (ORDER BY) parce qu’Oracle va dériver le montant de la CPU pour le tri de ces colonnes et donc le volume d’I/O qui peut avoir lieu en mémoire et qui, dû à la taille excessive de ces colonnes, peut dépasser la limite.

Donc, tant que LEK n’a pas tout montré on ne peut pas savoir avec exactitude quel a été son problème de performance et quel a été l’impact réel de ce qu’il a fait sur cette table temporaire dans l’amélioration de la performance.

Je trouve que LEK devrait revoir son traitement pour le rendre ‘’restartable’’. C'est-à-dire qu’à n’importe quel moment il peut relancer son traitement en ne traitant à chaque fois que ce qui ne l’a pas été. Pour cela il peut faire en sorte que chacun de ses enregistrements d'origine soit identifié via un flag (flag_processed par exemple) qui sera à 1 lorsqu’il a été correctement traité et 0 par défaut (non traité ou traité avec une erreur) et au lieu que son traitement soit du genre ''tout ou rien'' il peut le rendre plus flexible avec ce flag et en utilisant des rollback to savepoint

**LEK** · 13/09/2012, 21h11

Salut à tous,
Rei Ichido : merci pour la démo je vois mieux comment tu voyais la chose et effectivement cela offre des possibilités intéressantes.
Mohamed.HOURI : l'ensemble du process est automatisé et je ne peux me permettre de faire un processus en n step... D'après ce que je comprend tu propose que je puisse faire des imports partiels... Mais j'ai peur que cela m'amène dans des complications (données dépendantes par exemple pour lequel j'ai une donnée chapeau qui sera importée sans données filles car celles-ci seraient en erreur par exemple... Cela provoquerait des effets de bords dans mon appli web...)
Mnitu :
je suis resté assez vague pour des raisons de droits commerciaux (..). En fait je possède une appli web (erp) qui est accédée par plusieurs utilisateurs. Chacun de ces utilisateurs travaille dans un environnement cloisonné des autres et ne manipule que ses données.
L'application permet la saisie d'un certain nombre d'informations (je sais c'est vague

), dont la volumétrie peut être trop conséquente pour n'être saisies que par IHM. Parfois aussi le client en question possède déjà sous un format type fichier csv,tableur excel ou voire base de données un historique de données qu'il désire reporté dans mon application.
Pour faciliter la vie de ces gens j'ai mis en place un compte ftp par client, qu'ils utilisent pour uploader directement leur données sous leur format actuel.
J'ai ensuite des routines (C++/C#) qui suivant le cas vont transformés ces données pour les portées vers des fichiers plat csv. Ces fichiers sont ensuite chargés via des tables externes dans ma base oracle (10gr2) directement dans ma table temporaire de travail. Un batch permet alors de transformer (si nécessaire, car mes routines en C++/C# permettent parfois de réaliser complètement la transformation ) ces données dans un format de destination que je maitrise. A partir de là mon programme PL effectue des vérifications sur le format en entrée (contraintes et check diverses) et génère suivant le cas un log digeste pour le client. Si aucune erreur n'est détectée, j'importe ou met à jour les données existantes pour mon client dans ma base de données.
Si des erreurs ont été détectées, le fichier de log est déposé en retour sur le site FTP du client avec l'ensemble des raisons de l'échec.... Ensuite c'est à lui de corriger et de relancer l'ensemble du process!
Voilà j'espère que cela t'auras éclairer sur mon besoin réel (pour les fonctions pipelines tu faisais référence à fournir une aide à l'optimiseur ?)

Sinon pour vous donnez davantage d'informations, j'ai utilisé oradebug pour tracer les infos suivantes lors des requêtes en union pour vérifier les
cas d'erreur potentiels.
Pour rappel il s'agissait d'une requête portant sur une global temporary table
ayant 100 champs du type "CHAMP1 VARCHAR2(1000),CHAMP2 VARCHAR2(1000),...."
de ce genre :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT ' Le champ : '|| champ2_name ||' doit avoir une 
  longueur max de 5 ' AS message 
 FROM g_wrkTable
 WHERE  
  type_champ='CODEPOSTAL' AND Length (champ2)> 12 
UNION 
SELECT ' Le champ : '|| champ6_name ||' doit figurer dans la liste : 1,2.' AS message
g_wrkTable
 WHERE  
  type_champ= 'HABITATION_TYPE' AND champ6
  NOT IN (1,2)  
union ...
order by nom_fichier, line_number

Dans les cas d'import où je devais gérer plusieurs fichiers simultanément, j'ai du splitter mes requêtes en plusieurs car la requête générée pouvait dépassé les 32767 caractères...

Le résultat était le suivant (là je n'ai pas chargé la table car je voulais envoyer le résultat sans avoir à attendre 1 heure) :

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
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.07       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.14       0.15          0      41100          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.21       0.22          0      41100          0           0
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40  (GEST_CLI_JOB)   (recursive depth: 2)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  SORT ORDER BY (cr=41100 pr=0 pw=0 time=150536 us)
      0   SORT UNIQUE (cr=41100 pr=0 pw=0 time=150525 us)
      0    UNION-ALL  (cr=41100 pr=0 pw=0 time=150505 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=2006 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=827 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=813 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=847 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1223 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=794 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=869 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=826 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=807 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=828 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=966 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1254 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=861 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1344 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=814 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=977 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=808 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=816 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1296 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=973 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=820 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=812 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=791 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=831 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=794 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=971 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1286 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1466 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=838 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=833 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=801 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1207 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3299 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1268 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=6076 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1128 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1182 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3294 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1169 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1162 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=8625 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3834 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=862 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1222 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1199 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1264 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=853 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=5175 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1915 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1363 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=8330 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3326 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=868 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=2160 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1181 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=851 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1170 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3749 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=8681 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=839 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=859 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1555 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=809 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=852 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=813 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=899 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1274 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=844 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1192 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=907 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1827 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=995 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=3603 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=844 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1402 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=797 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=836 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=779 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1158 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1920 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=883 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=799 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=910 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=797 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=890 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1343 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=849 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=783 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1322 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1155 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=837 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=863 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=799 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=850 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1257 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=805 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=767 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=1183 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=756 us)
      0     TABLE ACCESS FULL g_wrkTable (cr=411 pr=0 pw=0 time=761 us)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   SORT (ORDER BY)
      0    SORT (UNIQUE)
      0     UNION-ALL
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
      0      TABLE ACCESS   MODE: ANALYZED (FULL) OF 'g_wrkTable' 
                 (TABLE (TEMP))
 
********************************************************************************

Voilà, j'espère que cela vous permettra de constater les raisons de mon problème... Sur des volumes assez faibles je n'ai pas de problèmes de traitement. Dès la centaine de meg en entrée le programme commence à consommer en CPU et en temps d'exécution (l'import se déroule alors en 30/45 minutes au lieu de 2/3 minutes habituelles)....
En tout cas, merci pour vos conseils.
ps : (je tente en parallèle une méthode en c++ pour voir si peux battre oracle à ce jeux là ;-) même si je n'y crois pas trop)

**mnitu** · 14/09/2012, 09h50

Bon, je comprends qu’en entrée de votre traitement vous avez des fichiers csv. Ces fichiers sont lus via des tables externes et chargés dans une table temporaire. Question : pourquoi ne contentez-vous tout simplement d’exploiter ces fichiers directement via SQL, c’est à dire à ne plus les charger dans la table temporaire ?

Par la suite une fois chargées dans la table temporaire vos données sont transformées « dans un format que de destination que j’ai maîtrise ». Je ne peux rien dire sur cette opération obscure mais il sera sage d’étudier la possibilité de l’éliminer en appliquant les transformations au moment de chargement dans les tables. Revenez avec les détails.

Par la suite vous exécutez un programme PL/SQL qui vérifie pour l’ensemble des données les longueurs des valeurs, le domaine de définition, et qui sait encore quelle autres règles de cohérence. A priori vous avez adopté cette stratégie parce que c’est un traitement de type tout ou rien ! Deuxième contraint le client veut avoir une liste « exhaustive » des erreurs avant de relancer son traitement pour éviter les aller retour.

Jusque là rien de spécial. A moi, ce qui me fait peur dans tout cette histoire c’est le mot générique ! A priori votre table contient dans une zone type champ les « types de champ » générique : code postal, type d’habitation etc. Pour chaque « type de champ » vous avez dans une zone le nom du champ actuel et une autre zone contient la valeur du champ. Je pense que maintenant j’ai compris le but de votre « opération de transformation des données dans un format … »

Premier conseil : jetez à la poubelle tout ça et fait N traitements pour chaque type de fichier que vous recevez. Tous vos problèmes de performances vont disparaître par miracle.
Deuxième conseil, utilisez le « array processing » pour accélérer les traitements : BULK Fetch en PL/SQL Array Fetch en C, C++, java, etc. En anglais ils disent : « row by row it’s slow by slow »
Troisième conseil : n’essayez jamais de battre Oracle par un langage de troisième génération, vous allez perdre toujours.
Quatrième conseil : le cas échéant vous pouvez améliorer encore vos traitements (mais pas les génériques) sous certaines conditions avec un traitement des données en parallèle. Mais n’employez pas des techniques sophistiquées avant d’avoir tiré avantage au maximum des techniques classique.

Allez, bon courage

**Waldar** · 14/09/2012, 14h50

Je me permets d'insister sur le premier point évoqué par mnitu.
Le meilleur moyen d'obtenir de la performance est de réaliser une interface par traitement.

Ça peut paraître de prime abord plus coûteux en terme de développement, mais vous n'aurez jamais de plaintes de vos utilisateurs côté performance ou encore cohérence des données (données typées et contraintes).

En général, les utilisateurs ont rarement des besoins très disparates ni volatile, une fois qu'ils ont adopté un type de fichier .csv à vous soumettre, celui-ci bougera peu.

**LEK** · 14/09/2012, 19h53

Bonjour,
merci pour vos conseils. Je comprends maintenant que je dois me séparer de ma logique "générique"...
Comme vous l'aurez compris les outils d'ETL sont assez nouveaux pour moi, les rouages internes d'oracle aussi encore opaques... Pour preuve je chargeaut encore récemment mes fichiers en utilisant utl_file avant de découvrir les tables externes....
Pourquoi j'ai opté pour la généricité :
- je voulais remonter un ensemble clair et si possible exhaustif de messages à mes clients sur la nature des erreurs de formats (type de données/longueur/unicité) suite à l'ananlyse des fichiers qu'ils me fournisse en entrée. En fait, j'ai bien conscience de recoder ce qu'oracle sait déjà faire "by design" car j'ai dû aller jusqu'à renseigner pour chaque import et potentiellement pour chaque client les longueurs que j'attendais d'eux par champs, le type de données, les LOV possibles, etc...
Aussi j'entends bien que j'aurais tout aussi pu laisser oracle faire le travail, mais charger toutes les données directement dans des tables cibles ayant toutes les contraintes nécessaires ne m'amenait pas une exhaustivité des contraintes violées et surtout les messages d'erreur n'était pas digestes/ni "orientés métier" pour un agnostique au SQL....
Cela dit je n'ai actuellement pratiquement aucun traitement ligne par ligne, et j'ai toujours privilégié les traitements en sql pur du type "insert select" et "merge". C'est pour cela que j'étais assez étonné de la gourmandise en mémoire et du temps pris... La partie qui était consommatrice semblait être en effet la vérification des données envoyées et là encore comme je l'ai indiqué j'ai généré dynamiquement le sql (d'après les tables de paramétrages dont j'ai parlé plus haut) pour générer les éventuels messages d'erreurs...
Lorsque je parle du format que "je maitrise ", en fait il s'agit du format que je propose en standard à mes clients et que ceux-ci adoptent de bonne grâce s'ils n'en utilisaient pas déjà un précédemment/s'ils n'ont pas d'existant...
Mais il arrive que pour certains je dois me résoudre à récupérer en entrée une base access, ou un fichier excel en entrée ... Pour ces cas là j'exporte le contenu sous forme de fichiers csv, puis je charge cela en base, j'entame alors un processus de transformation de ces données vers le format standard que j'attendais normalement en entrée, une fois ce stade atteint c'est toujours le même script PL qui s'occupe des insert et "merge" (pratiquement aucun "delete" si ce n'est des "soft delete").... Je ne voudrais pas être amené à dupliquer cette partie commune avec de légères différences à gérer par clients car j'ai peur que la maintenance s'avérere difficile!
Dans d'autre cas où je reçoit des formats binaires propriétaires (base SQL serveur 2005 mobile), j'insère directement dans ma table de travail temporaire avant de lancer mon traitement d'import...
Par exemple dans ce tronc commun en PL/SQL utilisé je reçois un calendrier d'activité fournis la plupart du temps (mais pas toujours...) sous la forme suivante :
- Période d'activité : date de début /date de fin
- Jour d'ouverture/fermeture de l'agence dans la semaine, fourni sous une forme de masque binaire : 1111100 signifiant ouvert du lundi au vendredi et fermé le week-end.
Ces données étant surchargées par des journées exceptionnelles :
- Date d'ouverture exceptionnelle (correspond à une date d'ouverture même en dehors de toute période d'activité ou lors d'un jour de fermeture)
- Date de fermeture exceptionelle

Mon appli a besoin que je stocke ces informations sous le format suivant :
111110011111001111100.... par tranche de 365 bits en commençant à la date d'aujourd'hui et en appliquant les masques déduits des périodes d'activités en surchargeant si nécessaires par les journées exceptionnelles... Bref c'est le type de traitement que je ne sais pas traiter en pur sql... Je me vois mal dupliquer ma routine PL/SQL par interface, j'ai donc capitaliser la transformation en passant par un bloc PL/SQL... Ceci n'étant qu'un exemple réel pour montrer le type de traitement que j'essaye de prendre en charge.

Cela dit mnitu, je comprend le message : je peux trés bien faire mes tansformations à la volée en sql en lisant directement depuis ma table externe!

Bon je sens que mon message commence à devenir longuet, aussi je ne vais pas vous retenir plus longtemps. J'ai vraiment apprécié nos échanges car effectivement ce sont les types de rappels à l'ordre que vous avez émis qui m'empêcheront je pense de plonger tête baissée et sans masque dans l'usine à gaz... Je vais tenter de créer des tables externes sur mesures suivant les différentes dispositions de fichiers que je reçois, ainsi que la vérification par fonction pipeline... Je garde le sujet ouvert juste au cas où je continue de recontrer des problèmes de perf. avec cette nouvelle méthode.

Merci encore à vous.

Utilisation de global temporary tables

SQL Oracle

Discussions similaires

Partager

Partager