Bonjour,

Nous travaillons actuellement sur une refonte de notre système, et plus particulièrement sur la migration des données.
Nous avons 2 tables TRANSAC(TRANS_ID, CODE_CONTRAT, LABEL, MONTANT) et TRANSAC_DETAIL(TRANS_ID,NAME,VALUE) contenant respectivement 20 millions et 1 milliards de lignes.
Pour 1 ligne dans la table TRANSAC, nous avons 40 lignes associées dans la table TRANSAC_DETAIL.

Exemple :

TRANSAC:
TRANS_ID = 127843978, CODE_CONTRAT = 'CONTRAT_1', LABEL = 'LABEL_1', MONTANT = 40
TRANS_ID = 127843979, CODE_CONTRAT = 'CONTRAT_1', LABEL = 'LABEL_1', MONTANT = 60

TRANSAC_DETAIL:
TRANS_ID = 127843978, NAME = 'DATE_DEBUT_EFFET', VALUE = '01/01/2014'
TRANS_ID = 127843978, NAME = 'DATE_FIN_EFFET', VALUE = '31/01/2014'
...
TRANS_ID = 127843978, NAME = 'PAYS', VALUE = 'FRANCE

TRANS_ID = 127843979, NAME = 'DATE_DEBUT_EFFET', VALUE = '01/01/2014'
TRANS_ID = 127843979, NAME = 'DATE_FIN_EFFET', VALUE = '31/01/2014'
...
TRANS_ID = 127843979, NAME = 'PAYS', VALUE = 'FRANCE


Le modèle de destination est le suivant:

TRANSACTION_DETAIL(ID_T_GLOBAL, MONTANT, DATE_DEBUT_EFFET, DATE_FIN_EFFET, ..., PAYS):
ID_T_GLOBAL= 1, MONTANT = 40, DATE_DEBUT_EFFET = '01/01/2014', DATE_FIN_EFFET = '31/01/2014', ..., PAYS = 'FRANCE'
ID_T_GLOBAL= 1, MONTANT = 60, DATE_DEBUT_EFFET = '01/01/2014', DATE_FIN_EFFET = '31/01/2014', ..., PAYS = 'FRANCE'

TRANSACTION_GLOBAL(ID_T_GLOBAL, CODE_CONTRAT, LABEL)
ID_T_GLOBAL = 1, CODE_CONTRAT = 'CONTRAT_1', LABEL = 'LABEL_1'

En résumé:
La table TRANSAC_DETAIL organisée en lignes a été réorganisée en colonnes (TRANSACTION_DETAIL)
La table TRANSACTION_GLOBAL est une agrégation des lignes dans TRANSAC.



J'ai commencé par faire une requête de type :

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
insert into transaction_detail
select
SEQ_ID_T_GLOBAL.nextval,
t.montant,
td1.value,
td2.value,
...
td40.value
from transac t
join transac_detail td1 on td1.trans_id = t.trans_id
join transac_detail td2 on td2.trans_id = t.trans_id
...
join transac_detail td40 on td40.trans_id = t.trans_id
where t.label = 'LABEL_1'
and td1.name = 'DATE_DEBUT_EFFET'
and td2.Name = 'DATE_FIN_EFFET'
...
and td40.name = 'PAYS';
Mais le coût des 40 jointures est affreux...

Je suis donc passé à la requête suivante:

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
insert into transaction_detail
select
SEQ_ID_T_GLOBAL.nextval,
t.montant,
(select td.value from transac_detail td where td.trans_id = t.trans_id and name = 'DATE_DEBUT_EFFET'), 
(select td.value from transac_detail td where td.trans_id = t.trans_id and name = 'DATE_FIN_EFFET'), 
..., 
(select td.value from transac_detail td where td.trans_id = t.trans_id and name = 'PAYS'), 
from transac t

Le coût de mon plan d'exécution est de 35 000 sachant que j'ai des index sur:
TRANSAC(LABEL)
TRANSAC(TRANS_ID)
TRANSAC_DETAIL(TRANS_ID, NAME)
Et ces index sont bien utilisés. La majeure partie du coup d'exécution se situe au niveau de la lecture de la table TRANSAC : 34 000.
Les couts de mes select sont de 5.

Néanmoins, quand je lance cette requête, celle ci tourne encore après 48h et je n'en vois jamais le bout ...


J'ai déjà tenté sans succès:

-> dupliquer TRANSAC_DETAIL vers TMP_TRANSAC_DETAIL et créer des partitions sur TRANS_ID: le coût de duplication de 1 milliard de lignes, + l'insert est gigantesque. J'ai laissé tomber

-> Faire du PL/SQL avec un cursor allant chercher seulement les informations sur TRANSAC, et aller chercher les informations dans TRANSAC_DETAIL au sein du bloc BEGIN ... END, puis de faire l'insert. Ma requête tourne encore apres 48h

-> Utiliser le HINT /*+parallel(t, 12) */, sachant que les tables TRANSAC et TRANSAC_DETAIL sont déjà à un degré = 4. Le cout de mon plan d'exécution a bien diminué mais ma requête ne s'est jamais terminée. A mon avis, je suis limité en termes de ressources, cpu, etc.

-> utiliser /*+ APPEND */ dans mon insert en positionnant la table TRANSACTION_DETAIL en nologging avant le lancement de l'insert. Ma requête ne s'est jamais terminée.

-> diviser mon traitement en plusieurs fois en utilisant un filtre dans la clause where sur le label. Je lance donc 7 fois ma requête en indiquant where label = 'LABEL_1', puis where label = 'LABEL_2', etc... ALors cela fonctionne bien pour 6 valeurs sur 7. La 7eme valeur représente 90% de la table du coup, mon index sur LABEL n'est pas utilisé et je me retrouve à faire un full scan de la table. Donc autant lancer une requête unique et non 7 lancement distinct.


Il y a-t-il des améliorations possibles sur ce type de requête? Je commence un peu à craquer là ...
Merci pour votre aide.