Performance sur dates dans clause Where

[lbar012001]

Bonjour,
J'ai une requête assez lourde qui utilise des tables des +100 000 000
d'enregistrements.
Je souhaite extraire des données sur une base mensuelle.
Quand je mets en guise de test dans mon Where une instruction du type

Code :

WHERE madate = '01/12/2010'

J'ai le résultat quasi instantanément

mais avec, pour tout décembre du

Code :

1
2
WHERE madate >= '01/12/2010
and madate < '01/01/2011'

Ca ne renvoie plus rien avant des heures et des heures

Je recherche la méthode optimatale pour manipuler les dates dans des clauses Where .

Merci
Laurent

PS: je ne suis que requêteur occasionel et sans droits particuliers donc je n'ai pas idée de comment le modèle de données est fichu (index...) c'est un vrai bordel.

[diplomegalo]

Je crois bien que dans ton cas il y a une conversion implicite qui mange pas mal de temps.

Tu peux donc essayer dans un premier temps de faire la conversion toi même :

Code :

madate > TO_DATE('01/12/2010', 'dd/mm/yyyy')

Après il faut voir, j'ai pas trop d'idées

En espèrant que ça t'aide

Diplomegalo

[JerryMouse]

Effectivement.
mais moi je ne te recommande pas l'utilisation de cette conversion automatique de type appelée transtypage automatique.
en effet, en ce qui concerne les dates, le résultat de ce transtypage dépend du format de date par défaut du poste qui exécute la commande.

Code :

WHERE madate = '01/12/2010'

peut vouloir dire 01 décembre 2010 si le format de la date de la machine est du DD/MM/YYYY
tout comme cela peut signifier 12 janvier 2010 si le format est du MM/DD/YYYY. cela peut donc donner des résultats inattendu.

Et si par contre le format et du YY/MM/DD, là mon gars, t'es foutu.
il faut donc toujour utiliser soit du to_date(), soit du to_char() quand tu veux manipuler des dates.

[ojo77]

Lors de la première requête vous demandez à l'optimiseur de répondre à une égalité.

Son estimation va donc être la suivante :

• Soit il devra parcourir tous les blocs de la table pour ramener les valeurs égales à 01/12/2010 (Full table scan) ou de la partition s'il y a du partitionnement sur la date.
• Soit il devra parcourir l'index (en supposant qu'il y en ait un) pour obtenir l'entrée 01/12/2010 (index scan) et aller chercher le ou les blocs associés dans la table (table fetch by rowid)

Si un index existe effectivement, celà peut être assez rapide et c'est certainement ce qui se produit dans votre cas.

Dans la seconde requête vous demandez à l'optimiseur de calculer le chemin pour un interval couvrant un mois.

Pour l'optimiseur, le choix devient donc le suivant

• Soit il devra parcourir tous les blocs de la table pour ramener les valeurs de décembre 2010 (Full table scan) ou de la partition s'il y a du partitionnement sur la date.
• Soit il devra parcourir l'index (en supposant qu'il y en ait un) pour obtenir toutes les entrées de décembre (index range scan) et aller chercher le ou les blocs associés dans la table (table fetch by rowid)

Il es possible dans ce second cas que l'optimiseur estime que le full table scan est moin coûteux que l'accès par index et choisisse donc de parcourir soit vos 100 000 000 d'enregistrements soit tous les enregistrements d'une partition correspondant à votre date.

Afin de pousser un peu plus le diagnostique vous devez savoir

• Si votre table est partitionnée et quel est la clé de partitionnement.
• Combien d'enregistrements sons sensés être remontés dans par votre seconde requête.
• Combien d'enregistrement contient en moyenne un bloc.
• Combien de valeurs distinctes de date existent en tout.
• Quelles sont les statistiques sur les objets pouvant entrer en jeu dans cette requête.
• ...

L'outil SQLT disponible sur metalink note 215187.1 peut vous aider à récupérer l'ensemble de ces données et commencer à comprendre ce qui se passe au niveau de l'optimiseur Oracle pour votre requête.