Bonjour à tous (ça faisait longtemps).
j'ai rencontré récemment un problème assez épineux sur mon datawarehouse.
J'ai deux énooOOOooormes tables (disons 300 000 lignes / jour depuis 1 an pour la 1ere et 50 000 lignes / jour depuis 5 ans pour la 2e ) de la structure suivante :
[QUOTE]
Table 1
Id_table1
Id_table2
info1_table1
info2_table1
info3_table1
info4_table1
flag_table1
300 000 lignes/jour -> 70 000 000 lignes
Evidemment ces tables ne ressemblent à rien, le but étant de ramener toutes les informations sans restriction, puis d'en extraire des informations cohérentes pour les stocker proprement.Table 2
Id_table2
...
50 000 lignes/jour -> 60 000 000 lignes
Bon bref, j'avais une clé unique sur la table1, et donc un index :
Pourquoi Id_table2 n'est pas présent dans la clé ? Et bien au moment de la première analyse ça ne paraissait pas nécessaire (d'ailleurs d'un point de vue fonctionnel ça ne l'est pas).Id_table1
info1_table1
info2_table1
info3_table1
info4_table1
Tous les jours, je sélectionne les données exploitables pour les copier dans une autre table et je flag la table1 à 1 (Table1.flag_table1=1). Je fais ça ligne à ligne (ça prend environ 1h, donc c'est acceptable) donc je peux facilement repérer la ligne que je viens de copier et aller mettre à jour son flag.
Le process ressemble à peu près à ça :
Et ça marchait très bien.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 SELECT ma liste de lignes POUR CHAQUE LIGNE INSERT ma ligne dans ma table SI l'insert est ok ALORS UPDATE Table1.flag_table1=1 WHERE Table1.id_table1=xxx AND Table1.id_info1_table1=xxx AND... AND Table1.id_table2=xxx SINON UPDATE Table1.flag_table1=-1 WHERE Table1.id_table1=xxx AND Table1.id_info1_table1=xxx AND... AND Table1.id_table2=xxx
Depuis un moment, on essaye de rapprocher les données de la Table1 de celles de la Table2. En gros on essaye de trouver pour chaque ligne de la Table2 les informations qui peuvent exister dans la Table1.
Pour accélerer un peu la jointure : Table2.id_table2 = Table1.id_table2, j'ai créé un index sur la Table1 :
et ça marchait très bien.id_table2
Mais récemment, en essayant d'optimiser d'autres process, je me suis rendu compte que les statistiques n'étaient jamais calculées. J'ai donc demandé aux dba de calculer les statistiques pour toutes les tables qui étaient inclues dans un process particulier.
Ca a été fait, et tout marchait bien.
Mais avant-hier, le dba a décidé de calculer les statistiques pour toutes les tables (initiative que je loue). Et là c'est le drame : les délais de mon process expliqué plus haut explosent ! Ils passent de 1000 lignes / s à 1 ligne toutes les 4mn.
Après plusieurs heures de recherche j'ai finit par comprendre : suite au recalcul des statistiques, l'optimiseur d'Oracle a considéré qu'il valait mieux utiliser le 2e index basé uniquement sur id_table2 pour identifier la ligne de Table1 à mettre à jour.
En effet si vous regardez dans le process, j'utilise effectivement Table1.id_table2 dans ma requête de mise à jour.
Là où se pose un vrai problème c'est que la sélectivité de mon 2e index (celui qui est basé seulement sur le champ Table1.id_table2) est un peu particulière :
20% des données ont la valeur 0 (ce sont les données qu'on ne peut pas rapprocher)
80 % des données ont une valeur de bonne sélectivité (en gros 5 valeurs id_table1 <-> 1 valeur id_table 2).
Par conséquent, si id_table2 vaut une valeur quelconque, l'utilisation de l'index 1 ou de l'index 2 donne quasiment les même résultats (quoique sûrement un peu plus rapide avec l'index 1).
Par contre si id_table2 vaut 0, l'utilisation de l'index 2 ramène 20% des données de la table, ce qui donne une durée de 4mn pour chaque ligne.
J'ai vérifié en modifiant mon process comme ceci :
Et je retrouve des délais corrects.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8 SELECT ma liste de lignes POUR CHAQUE LIGNE INSERT ma ligne dans ma table SI l'insert est ok ALORS UPDATE Table1.flag_table1=1 WHERE Table1.id_table1=... AND Table1.id_info1_table1=... AND... AND Table1.id_table2=... SINON UPDATE Table1.flag_table1=-1 WHERE Table1.id_table1=xxx AND Table1.id_info1_table1=xxx AND... AND Table1.id_info4_table1=xxx
Donc maintenant arrive la question : est-ce que c'est l'optimiseur qui fait n'importe quoi en utilisant l'index 2 qui présente une sélectivité pourrie alors qu'il pourrait utiliser l'index 1, ou est-ce qu'il y a une vraie erreur d'analyse ? Est-ce que je devrais créer un index 3 qui serait le même que l'index 1 + id_table2 ?
Merci de vos remarques, questions, conseils.
Partager