Migration de données + volumétrie importante + optimisation SQL

Version imprimable

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:

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:

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.

Bonjour,

Vous pouvez faire une requete de cette forme :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
insert into transaction_detail
select
    SEQ_ID_T_GLOBAL.nextval,
    t.montant,
    MAX(CASE WHEN td.name = 'DATE_DEBUT_EFFET' THEN td.value END),
    MAX(CASE WHEN td.name = 'DATE_FIN_EFFET'     THEN td.value END),
    ...
    MAX(CASE WHEN td.name = 'PAYS'                      THEN td.value END)
from transac t
join transac_detail td on td.trans_id = t.trans_id
where t.label = 'LABEL_1'
GROUP BY t.montant
;

Cherchez avec le mot clef PIVOT sur le forum

10/02/2015, 12h13
Waldar

Votre système actuel me paraît correct pour de l'OLTP => les lignes d'une transaction sont facilement manipulables avec le modèle clef / valeur.
Votre nouveau système me paraît correct pour de l'OLAP => c'est vraiment beaucoup plus simple d'analyser les données déjà en colonnes pour n transactions.

D'où la question, à quoi sert votre base ???

Citation:
Envoyé par aieeeuuuuu
Bonjour,

Vous pouvez faire une requete de cette forme :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 insert into transaction_detail select SEQ_ID_T_GLOBAL.nextval, t.montant, MAX(CASE WHEN td.name = 'DATE_DEBUT_EFFET' THEN td.value END), MAX(CASE WHEN td.name = 'DATE_FIN_EFFET' THEN td.value END), ... MAX(CASE WHEN td.name = 'PAYS' THEN td.value END) from transac t join transac_detail td on td.trans_id = t.trans_id where t.label = 'LABEL_1' GROUP BY t.montant ;
Cherchez avec le mot clef PIVOT sur le forum
J'ai fait une tentative de requête pivot mais maintenant je me retrouve à surcharge le tablespace TEMP. On a beau avoir augmenté la taille du tablespace, la requête est trop gourmande en terme d'utilisation du tablespace TEMP. Du coup on est en train de voir jusqu'à quelle taille on peut monter (on a actuellement un tablespace de 64Gb).

18/02/2015, 15h26
erara22

Citation:

Envoyé par Waldar

Votre système actuel me paraît correct pour de l'OLTP => les lignes d'une transaction sont facilement manipulables avec le modèle clef / valeur.
Votre nouveau système me paraît correct pour de l'OLAP => c'est vraiment beaucoup plus simple d'analyser les données déjà en colonnes pour n transactions.

D'où la question, à quoi sert votre base ???

Il s'agit d'un système de facturation. Nous recevons des évènements de système tiers. Un moteur Java nous fait quelques calculs pas bien méchants, puis tout est stocké en base.
Notre système permet donc de restituer l'état de ces évènements. Puis périodiquement, les données sont extraites/aggrégées et renvoyées vers des systèmes avals.
Le modèle de données a été conçu il y a maintenant plusieurs mois, et une remise en question n'est malheureusement pas possible à l'heure actuelle en vu de la mise en production dans quelques mois.

Bref, on tâtonne toujours...
18/02/2015, 16h31
mnitu

Votre problème est dû au fait que vous stockez des données dans un modèle non-relationnelle, générique de type clé-valeur et par la suite vous essayez d’utiliser ces données via SQL qui a été conçu pour des modèles relationnels. Vous ne devez pas donc vous étonner que les performances soient mauvaises dans ce cas. La seule solution consiste à stocker vos données dans un modèle relationnel de le départ.
19/02/2015, 11h21
erara22

Citation:

Envoyé par mnitu

Votre problème est dû au fait que vous stockez des données dans un modèle non-relationnelle, générique de type clé-valeur et par la suite vous essayez d’utiliser ces données via SQL qui a été conçu pour des modèles relationnels. Vous ne devez pas donc vous étonner que les performances soient mauvaises dans ce cas. La seule solution consiste à stocker vos données dans un modèle relationnel de le départ.

C'est pour cela que nous avons changé notre modèle, afin d'améliorer les requêtes de consultations de ces données :D. Ma problématique est donc de migrer les données d'une modèle non relationnel à un modèle relationnel. Je n'étais pas surpris des temps de performances de migration, simplement je sollicitais l'aide de la communauté afin de trouver des techniques d'optimisation que je ne connaissais pas :roll:
19/02/2015, 13h53
mnitu

Donc vous devez faire cette opération une seule fois.
Vous avez une restriction sur la fenêtre de migration ? Autrement dit si votre traitement dure 3 jours et vous avez à votre disposition ces 3 jours cela fera l'affaire. Dans ce cas ce qui compte c'est plus d'arriver à faire le boulot plus que de l'optimiser, vu que c'est du jetable!
19/02/2015, 14h06
erara22

J'ai en effet une fenêtre de 3 jours (en gros un bon gros week end). J'ai déjà fait quelques simulations et pour l'instant je ne tiens pas ces 3 jours mais je l'aurais un jour ... je l'aurais!
19/02/2015, 14h33
mnitu

Et si vous commencez à normaliser la table TRANSAC_DETAIL (avez vous l'espace nécessaire ?) ?
Quelque chose de type (en Pl/SQL)
lecture en bulk de la table TRANSAC_DETAIL order by trans_id
A chaque rupture de transac_id
dans une structure de type record on charge les valeurs des colonnes de l'ancienne table
Case nom
When 'DATE_DEBUT_EFFET' rec.date_deb := value
When 'Pays' then rec.pays = value
...
et vous cumulez ces enregistrement dans un tableau de 1000 (ou < 5000 à tester)
et quand vous atteigniez la limite du lot vous insérez dans la nouvelle table en bulk

A la fin votre table est normalisée.
Cela devrait durer moins d'un jour (pas d'indexes sur la nouvelles tables pendant le traitement).
20/02/2015, 09h11
mnitu
Sur la même idée de construire un record, l'utilisation des fonctions pipelined éventuellement parallélisée devrait permettre d'atteindre des temps de réponse raisonnables.
Code:

1 2 3 insert into nouvelle table Select * from table(pipelined_function)