par Guillaume Lelarge.

Chaînes numériques


Les chaînes numériques sont des nombres enregistrées dans des colonnes textes. Bien que cette pratique soit très mauvaise, elle ne rend pas l'index inutilisable si vous les traitez en permanence comme des chaînes :

SELECT ...
  FROM ...
 WHERE chaine_numerique = '42'

Bien sûr, cette requête peut utiliser un index sur CHAINE_NUMERIQUE. Néanmoins, si vous la comparez à un nombre, la base de données ne peut plus utiliser cette condition comme prédicat d'accès.

SELECT ...
  FROM ...
 WHERE chaine_numerique = 42

Notez les guillemets manquants. Bien que certaines bases de données renvoient une erreur (par exemple PostgreSQL), certaines bases de données ajoutent simplement une conversion implicite à cause de la différence de type.

SELECT ...
  FROM ...
 WHERE TO_NUMBER(chaine_numerique) = 42

Nous avons ici le même problème qu'avant. Un index sur CHAINE_NUMERIQUE ne peut pas être utilisé à cause de l'appel de fonction. La solution est identique : ne convertissez pas la colonne de la table, mais convertissez le terme de la recherche.

SELECT ...
  FROM ...
 WHERE chaine_numerique = TO_CHAR(42)

Vous pourriez vous demander pourquoi la base de données ne le fait pas ainsi automatiquement ? C'est dû au fait que convertir une chaîne en nombre donne toujours un résultat non ambigu. Le contraire n'est pas vrai. Un nombre, formaté en texte, peut contenir des espaces, de la ponctuation et des zéros en tête. Une seule valeur peut être écrite de plusieurs façons :

42
042
0042
00042
...

La base de données ne connaît pas le format numérique utilisé dans la colonne CHAINE_NUMERIQUE, donc selon la logique inverse, la base de données convertit les chaînes en nombres car la transformation n'est pas ambigüe.

La fonction TO_CHAR renvoie seulement une représentation textuelle du nombre. Du coup, cela correspondra seulement à la première des chaînes listées ci-dessus. Si nous utilisons TO_NUMBER, cela correspond à toutes les chaînes. Cela signifie qu'il n'y a pas seulement une différence de performances entre les deux variantes mais aussi une différence sémantique !

Utiliser des chaînes numériques est généralement problématique : cela cause surtout des problèmes de performances dus à la conversion implicite mais cela introduit aussi un risque d'erreurs de conversion pour les nombres invalides. Même la requête la plus triviale qui n'utilise aucune fonction dans la clause where peut déclencher une annulation sur une erreur de conversion s'il y a ne serait-ce qu'un seul nombre invalide stocké dans la table.

Astuce

Utilisez les types numériques pour stocker des nombres.

Notez que le problème n'existe pas dans l'autre sens :

SELECT ...
  FROM ...
 WHERE nombre_numerique = '42'

La base de données va transformer de façon cohérente la chaîne en un nombre. Elle n'applique pas une fonction sur la colonne potentiellement indexée : du coup, un index standard fonctionnera. Néanmoins, il est possible de mal faire une conversion manuelle :

SELECT ...
  FROM ...
 WHERE TO_CHAR(nombre_numerique) = '42'

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license