Administration des bases de données

Blogue

Les cours

Comparaison de requêtes indexées sur une colonne numérique par rapport à une colonne VARCHAR2


Rédigé par , le
Cet article est en réponse à une question posée sur ce site, et qui demande que l'on s'y attarde.

Pour faire cette comparaison, je suggère de faire un exemple d'exécution de requêtes identiques, sur des valeurs identiques, mais avec un TYPE de colonne différent.

Tout d'abord, il faut créer une table avec une colonne numérique et une colonne de type VARCHAR...
CREATE TABLE TEST_IDX(
 PKEY NUMBER PRIMARY KEY,
 COL_IDX_NUM NUMBER,
 COL_IDX_CHAR VARCHAR2(10)
);
Puis, insérons 500k lignes dans cette table. Voici la méthode utilisée dans mon cas : 
$conn = new PDO("oci:dbname=ccc", "aaa", "bbb");

for ($i = 1; $i <= 500000; $i++) {
  $statement = 
     $conn->prepare("INSERT INTO TEST_IDX(PKEY, COL_IDX_NUM, COL_IDX_CHAR) ".
                     	  "VALUES(" . $i . ", " . $i . ", " . $i . ")");	
  $statement->execute(); 
}
Maintenant, est-ce qu'Oracle traite ces deux requêtes comme étant identiques, s'il n'y a pas d'index?
SELECT * FROM TEST_IDX WHERE COL_IDX_NUM = 633876;
SELECT * FROM TEST_IDX WHERE COL_IDX_CHAR = '633876';
Plan d'exécution des deux requêtes :
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    24 |   792 |   413   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| TEST_IDX |    24 |   792 |   413   (1)| 00:00:05 |
------------------------------------------------------------------------------
Puisqu'il n'y a pas d'index sur ces colonnes, le chargement complet de la table en mémoire est nécessaire et donc, le temps d'exécution est le même pour les deux requêtes.
 
Ajoutons maintenant un index B-TREE sur ces deux colonnes.
CREATE INDEX test_idx_num ON TEST_IDX(COL_IDX_NUM);
CREATE INDEX test_idx_char ON TEST_IDX(COL_IDX_CHAR);
Ré-exécution des requêtes :
SELECT * FROM TEST_IDX WHERE COL_IDX_NUM = 633876;
SELECT * FROM TEST_IDX WHERE COL_IDX_CHAR = '633876';
La table étant petite, elle entre complètement en RAM et dans les deux cas, les deux requêtes donnent le même temps d'exécution sur ces 500 000 lignes. Le plan d'exécution reste identique également.

Plan d'exécution des deux requêtes.
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    33 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IDX      |     1 |    33 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX_CHAR |     1 |       |     3   ()0| 00:00:01 |
---------------------------------------------------------------------------------------------
Évidemment, le nom de l'index utilisé est TEST_IDX_NUM dans le cas de la requête sur la colonne numérique.

On peut donc constater que la base de données est capable de faire un index B-TREE tout aussi performant sur une colonne numérique qu'une colonne chaîne de caractères.
 
Cela dit...
Est-ce une bonne pratique de stocker des données numériques dans une colonne VARCHAR2? Non, car ceci pourrait :
1- Demander plus d'espace disque au niveau de la sauvegarde. '11111' > 11111 sur un disque dur.
2- Apporter des incohérences au niveau du résultat des requêtes... par exemple :
   SELECT COUNT(*) FROM TEST_IDX WHERE COL_IDX_NUM > 50000; 
   -- résultat : 44999 lignes
   SELECT COUNT(*) FROM TEST_IDX WHERE COL_IDX_CHAR > '50000';
   -- résultat : 55549 lignes !
Concernant le point #2, Oracle aurait pu transtyper la chaîne en nombre si l'on avait retirer les apostrophes autour de celui-ci. Cependant, étant donné le transtypage, l'index n'aurait pas pu être utilisé et on revient donc à un TABLE ACCESS FULL...
   SELECT COUNT(*) FROM TEST_IDX WHERE COL_IDX_CHAR > 50000;
   -- Donne le bon résultat, mais sans bénéficier de notre index, donc plus lent...
Sans l'avoir testé, je crois que dans le cas d'une base de données plus volumineuse, les requêtes sur une colonne numérique seraient plus performantes, étant donné que moins de blocs de données sont nécessaires à l'exécution des requêtes.
 
Les commentaires sur cet article
Par : Jo
2014-04-22 10:27:41

Très bon article. Cours mais précis.

Merci :)

Par : romeo
2017-11-29 17:09:15

merci pour la clarté de l'information très instructif. Merci beaucoup.

Ajouter votre commentaire
(facultatif)
(facultatif et non divulgé)
Captcha image
Prenez le nombre affiché et faites moins 1.
 
Blog  

Les articles


Pour envoyer une demande d'assistance à l'enseignant, cliquez ici