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.
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.
Très bon article. Cours mais précis.
Merci :)
merci pour la clarté de l'information très instructif. Merci beaucoup.