Y a-t-il un avantage à utiliser BETWEEN dans une instruction select pour les données des mois précédents plutôt que d'utiliser une comparaison avec TO_CHAR dans Oracle ?

Question

Je modifie une requête dans Oracle, dans laquelle les données du mois précédent sont nécessaires. Actuellement, la requête a une clause where avec ce qui suit :

...
...
...
WHERE cust_dt BETWEEN TO_DATE('05-01-2022','mm-dd-yyyy) AND TO_DATE('05-31-2022', 'mm-dd-yyyy')

Je modifie la requête pour que la date de début et la date de fin ne doivent pas être modifiées manuellement chaque mois pour exécuter la requête. Après quelques recherches, j'ai trouvé ce qui suit :

...
...
...
WHERE TO_CHAR(cust_dt, 'MM-YYYY') = TO_CHAR(add_months(sysdate, -1), 'MM-YYYY')

Les résultats que j'obtiens sont ceux que je souhaite, mais je suis curieux de savoir quelle requête sera la plus performante si l'on dispose d'un ensemble de données plus important. Tous les messages que j'ai vus en ligne utilisaient BETWEEN, je me demandais donc s'il y avait une raison à cela.

Je suis complètement novice en ce qui concerne les réglages, les tests, les performances, etc. des requêtes. La requête elle-même est assez complexe avec plusieurs jointures, donc la performance est importante. Pour l'instant, je ne dispose que d'une petite quantité de données de test pour travailler, je suis donc limité dans ce que je peux faire pour trouver le meilleur résultat.

Pour en revenir à ma question, quelle requête serait la meilleure ? Celle qui utilise BETWEEN, ou celle qui utilise TO_CHAR ?

Answer 1

Je vous recommande de ne pas utiliser l'une de vos deux requêtes. La première requête présente le problème de vérifier une plage de dates codée en dur. Il s'agit généralement d'un risque qu'il convient d'éviter dans la mesure du possible. La seconde requête effectue une conversion non nécessaire de date en caractère, ce qui risque d'être lent.

Il suffit d'utiliser les fonctions de date courantes pour obtenir les données du mois précédent. D'après votre description, votre date de référence est la date du jour, c'est-à-dire sysdate.

Afin d'exclure toute comparaison incorrecte en raison de l'heure, vous pouvez utiliser la fonction TRUNC pour supprimer l'heure de la date.

La fonction ADD_MONTHS peut être utilisé pour revenir un mois en arrière.

La fonction LAST_DAY peut être utilisé pour trouver le dernier jour du mois.

En combinant ces éléments, vous pouvez utiliser une clause where comme suit :

WHERE cust_dt BETWEEN ADD_MONTHS(TRUNC(sysdate,'mm'),-1) AND 
LAST_DAY(ADD_MONTHS(TRUNC(sysdate,'mm'),-1));

L'exécution est rapide et sûre et évite les conversions inutiles ou les dates codées en dur. Une dernière remarque : envisagez de modifier la requête ci-dessus en...

WHERE cust_dt BETWEEN ADD_MONTHS(TRUNC(sysdate,'mm'),-1)
AND TRUNC(sysdate,'MM')-INTERVAL '0.001' SECOND;

...selon que vous avez besoin ou non du dernier jour du mois précédent. Voyez ici la différence et essayez : db<>fiddle

Answer 2

WHERE TO_CHAR(cust_dt, 'MM-YYYY') = TO_CHAR(add_months(sysdate, -1), 'MM-YYYY')

N'utilise pas d'index sur le cust_dt à la place, vous devrez créer un index séparé basé sur les fonctions sur la colonne TO_CHAR(cust_dt, 'MM-YYYY')

WHERE cust_dt BETWEEN TO_DATE('05-01-2022', 'mm-dd-yyyy')
                  AND TO_DATE('05-31-2022', 'mm-dd-yyyy')

Utilisera un index sur le cust_dt colonne.

Toutefois, dans Oracle, un DATE Le type de données se compose de 7 octets représentant : le siècle, l'année du siècle, le mois, le jour, l'heure, la minute et la seconde. Le type de données TOUJOURS possède ces composants (mais souvent l'application client que vous utilisez pour accéder à la base de données affichera par défaut uniquement le composant date et non le composant heure - mais le composant heure existera toujours).

Cela signifie que votre requête trouvera les valeurs où cust_dt se situe entre 2022-05-01 00:00:00 y 2022-05-31 00:00:00 . Il ne correspondra pas aux valeurs pour lesquelles cust_dt se situe entre 2022-05-31 00:00:01 y 2022-05-31 23:59:59 .

Pour en revenir à ma question, quelle requête serait la meilleure ?

Ni l'un ni l'autre.

Vous voulez (si vous codifiez les dates en dur) :

WHERE cust_dt >= TO_DATE('05-01-2022', 'mm-dd-yyyy')
AND   cust_dt <  TO_DATE('06-01-2022', 'mm-dd-yyyy')

Ou (si vous recherchez la date de façon dynamique) :

WHERE cust_dt >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND   cust_dt <  TRUNC(SYSDATE, 'MM')

qui utilisera un index sur cust_dt et correspondre à l'ensemble de la plage pour le mois.

_db<>fiddle aquí_