[Astuce] Mettre à jour des dizaines de millions de lignes ? [Résolu]

[pepito62]

Bonjour,

Voilà, j'ai une table de codification qui a changé et je dois faire une moulinette
permettant de remplacer l'ancienne valeur du champ en question par la nouvelle.

Un petit exemple :

Table de codif (avant modif) :
Code - Libelle
C1 - Codif 1
C2 - Codif 2
C3 - Codif 3

Table (avec + de 50 millions de lignes)
Code - Libelle - Prix - Taux - Codif - ...
1 - Ticket1 - 12.55 - 19.6 - C1 - ...
...

Table de correspondance :
AncCode - NouvCode
C1 - C12
C2 - C220
C3 - C56

Ma première solution est de parcourir la table USER_TABLES/USER_TAB_COLUMNS pour connaître les tables qui contient la colonne Codif.
Jusque la, pas de problème sauf que :
Je vais tomber sur des tables qui contiennent énormément d'enregistrements (+ de 50 millions)
et je compte mettre à jour la table comme ceci (Information importante : Pas de PK ni d'index sur la colonne Codif) :

Code :

1
2
3
4
5
UPDATE NomTable
   SET Codif = NewCodif
 WHERE Codif = OldCodif;
 
COMMIT;

=> Je n'ai pas encore fait le test mais je pense que ça risque :
- de remonter une exception car nombre de lignes est trop important
- de mettre énormément de temps !!!!


Que me conseillez-vous ? Quels sont vos conseils ?

[remi4444]

Il faut que votre table de correspondance soit indexée sur l'ancien code car ce sera la clef d'entrée de l'update.

Sur votre grosse table, je ferait une boucle pour faire des updates par gros packet (de 1 million par exemple) en jouant sur des encadrement de valeur de la clef primaire.

Aussi, je ferais les choses en 2 temps:
1- Génération automatique du code SQL d'update
2- Exécution de ce code en sortant à la première erreur.

Ainsi vous pourrez contrôler votre processus.

Évidement, avant de faire cette opération délicate, il est indispensable de faire une sauvegarde de la base, ou tout du moins, de chaque table updatée.

[pepito62]

Aussi, je ferais les choes en 2 temps:
1- Génération automatique du code SQL d'update
2- Exécution de ce code en sortant à la première erreur.

C'est ce que je compte faire par contre la gestion de reprise en cas d'erreur est un peu plus compliqué, surtout si je filtre sur une partie de la PK.


Sinon le champ est bien indexé (index non unique) mais cet index est créé avec plusieurs autre champ.

J'ai regardé le plan d'exécution de l'update, il prend bien en compte l'index.

J'ai effectué un test, mise à jour de 400 000 lignes (je crois) sur 24 millions et la mise à jour prend environ 19/20minutes sur une BASE DE TEST, de plus, je ne sais pas si les stats sont à jour...

Ça fait quand même beaucoup de temps, surtout que c'est pour 1 seul valeur la. La table de codif fait 360 enregistrements !!!! et je n'ai pas que cette "opération à faire".

J’essaie de vous donner des imprimes écran demain matin sur la table, les index, le plan d’exécution...

[remi4444]

J'ai effectué un test, mise à jour de 400 000 lignes (je crois) sur 24 millions et la mise à jour prend environ 19/20minutes sur une BASE DE TEST, de plus, je ne sais pas si les stats sont à jour...

C'est sur que ça fait très très long, ça cache quelque chose...

[pepito62]

Voila quelques infos sur la table :







Il est vrai que la mise à jour va être plus rapide si j'ajoute un filtre sur le CodeSaison par exemple mais je vais devoir faire pas mal d'update si je veux balayer toute la table.

Au final, est-ce plus rapide, je ne sais pas....?!

[Waldar]

Et là, les lacunes de conceptions remontent...

On choisi en clef primaire des données qui sont immuables, en cas de doutes on utilise on en créé une avec une séquence.

Donc vous allez faire votre mise à jour, et demain rebelote, vos codifications rechangent. Vous recommencez ?

Vous avez besoin d'une modification importante pour vous débarrasser de ce carcan.

Au niveau des opérations légères au niveau de la BDD, mais qui impliquent une modification de l'amont, vous pouvez :

1. garder votre modèle ainsi, utiliser par jointure votre table de correspondance dans une vue pour afficher les nouveaux codes à partir des anciens.
2. profiter de cette opération pour créer une table de référence, ajouter une clef étrangère sur cette table (pas besoin de supprimer la colonne existante), et toujours utiliser une vue dans votre application / reporting

[remi4444]

j'ai l'impression que vos update se font par une grande boucle et non de manière ensembliste...

Dans tous les cas, c'est clair que ces index très larges ne favorisent pas les performances car l'update entraîne une grosse réorganisation de ces derniers.

[pepito62]

Concernant Waldar,

Ces codifications ne doivent normalement pas changé. Ici c'est un cas exceptionnel.

J'utiliserai bien la table de correspondance lié à cette table. Le problème c'est qu'il n'est pas possible de modifier le programme. Cela engendrait trop de modification...

Bref, quelle optimisation puis-je faire dans la moulinette ?

[orafrance]

Envoyé par pepito62

J'utiliserai bien la table de correspondance lié à cette table. Le problème c'est qu'il n'est pas possible de modifier le programme. Cela engendrait trop de modification...

Et en créant des vues ?

[pepito62]

Non ça serait pareil.

Je ne fais pas que des select sur cette table. Je fais aussi des update, insert, delete donc il a une gestion supplémentaire à mettre en place si je fais ça.

Je vais faire des tests chronométré :
- en faisant une restriction sur CodeClassProd3
- en faisant une restriction sur CodeTypePeriode et CodeClassProd3 (CodeTypePeriode : 3 valeurs possible)
- en faisant un Insert en masse puis un delete en masse

[Waldar]

Envoyé par pepito62

Ces codifications ne doivent normalement pas changé. Ici c'est un cas exceptionnel.

cqfd.

[mnitu]

Dans la mise à jour des grandes tables il est souvent plus performant :

• De créer une nouvelle table avec les données modifiées: create as select
• Supprimer l’ancienne table, drop..
• Renommer la nouvelle table, rename plus indexes, droits, etc.

à la place de l’update.

[pepito62]

Dans la mise à jour des grandes tables il est souvent plus performant :
De créer une nouvelle table avec les données modifiées: create as select
Supprimer l’ancienne table, drop..
Renommer la nouvelle table, rename plus indexes, droits, etc.

à la place de l’update.

Je vais essayer ça dans ma batterie de test. Je vais lancer ça dans quelques instants et je vous donnerai le résultat demain.

- Sinon en créant un index uniquement sur CodeClassProd3 et mettre à jour les stats pour faire mon update, ça n'arrangerait rien ?
- Et en supprimant les index pour les réactiver après l'update ?

[wahnfried]

Peut-être une piste :

a) create d'une nouvelle table avec le résultat escompté (table de base avec colonne mise à jour). => create table as select ....
b) truncate de la table de base
c) désactivation des index de la table de base
d) insert depuis la nouvelle table dans la table de base
e) rebuild des index

[pepito62]

a) create d'une nouvelle table avec le résultat escompté (table de base avec colonne mise à jour). => create table as select ....
b) truncate de la table de base
c) désactivation des index de la table de base
d) insert depuis la nouvelle table dans la table de base
e) rebuild des index

Vaut mieux créer une table et la renommer. Plutôt que de faire une opération en plus. (l'insert)

[pepito62]

La méthode update met 19min

La méthode
- Create Table ... As Select ...
- Create Index ...
- Rename AncienneTable
- Renanme NouvelleTable

met 46s.

Il n'y a pas photo, je pars sur cette solution.

Sinon pensez-vous que je peux effectuer cette méthode même sur des petites tables ? ou vaut mieux faire un update sur les petites tables et un create table sur les grosses tables ?

Dois-je m'embêter à faire un traitement spécifique selon la volumétrie des tables ?

[nuke_y]

Avec la solution de pepito62, vu qu'il faut reproduire sur la nouvelle table tout ce qu'il y avait sur l'ancienne (index, contraintes, triggers, commentaires, etc.) je dirais que soit vous faites un programme complet et vous le passez pour toutes les tables, soit effectivement vous prenez en compte la volumétrie.
C'est un problème qui ne se pose pas avec la solution de wahnfried.

[mnitu]

Envoyé par pepito62

...
Sinon pensez-vous que je peux effectuer cette méthode même sur des petites tables ? ou vaut mieux faire un update sur les petites tables et un create table sur les grosses tables ?

Dois-je m'embêter à faire un traitement spécifique selon la volumétrie des tables ?

Oui. Pour les petites/moyens tables faire un simple update c'est mieux.

[pepito62]

Ok merci, je partais sur cette solution.

Pour les petites et moyennes tables, je fais un update

Pour les grosses tables, un create table as select

- Sinon petites et moyennes tables, c'est combien de lignes environ ? (pour mon traitement)

[pepito62]

Au dessus de 400 000 lignes, faire un "create table as select" semble correct ?