J'ai entendu un jour une discussion entre deux développeurs où l'un tentait d'expliquer à l'autre que pour compter les lignes d'un table il était préférable, d'un point de vue performance, de faire un COUNT(1) plutôt qu'un COUNT(*).D'après lui le COUNT(1) était plus rapide car il passerait par l'index alors que le COUNT(*) génèrerait un Full Table Scan.
On est typiquement ici dans un mythe bien ancré dans l'esprit de beaucoup d'utilisateurs des bases de données Oracle (développeurs voir même des DBAs).
Pour prouver que les deux manières de compter les lignes ont des performances identiques il suffit d'exécuter ces deux requêtes et de regarder leur plans ainsi que leurs stats d'exécutions:
Que ce soit avec COUNT(1) ou COUNT(*) le plan d'exécution est identique et les 2 requêtes génèrent le même nombre de logical reads à savoir 1142.
Ce qu'on constate aussi c'est que pour les 2 requêtes l'optimiseur a choisi de faire un Full Table Scan alors que j'ai bien crée un index IDX_T1 sur cette table. L'index étant plus petit que la table on peut se dire qu'on génèrerait moins de logical reads en passant par l'index, n'est-ce pas? Alors pourquoi ignorer l'index?
La raison se situe dans le fait que le champ indexé OBJECT_NAME est NULLABLE, or les valeurs nulles ne sont pas référencées dans un index B-TREE. L'optimiseur ne peut donc pas se baser sur cet index pour compter le nombre de lignes de la table.
Si l'index IDX_T1 avait été un index BITMAP, le CBO aurait pu choisir cet index pour le comptage du nombre de lignes car dans un index BITMAP les valeurs nulles sont indexées.
La preuve:
L'index BITMAP est bien utilisé et le nombre de logical reads est passé de 1142 à 313.
Supprimons cet index BITMAP puis testons ce que donne le plan lorsqu'on crée une contrainte NOT NULL sur la colonne OBJECT_NAME:
Pour les 2 requêtes le CBO a bien choisi le même plan avec accès indexé. Il peut faire ce choix car la contrainte NOT NULL lui indique bien que toutes les valeurs de la colonne se trouvent dans l'index IDX_T1. Vous noterez que l'optimiseur a opté pour une opération de type INDEX FAST FULL SCAN et non pas INDEX FULL SCAN. La différence réside dans le fait que dans l'INDEX FAST FULL SCAN tous les blocks de l'index sont récupérés du disque en accès multi-blocks (équivalent au Full Table Scan) alors que dans le cas de l'INDEX FULL SCAN les accès sont faits de manière mono-block. Il est possible ici de passer par un INDEX FAST FULL SCAN car on n'a pas besoin de suivre la structure de l'index (blocks racine puis branches puis feuilles), on a juste besoin de récupérer les blocks de l'index quel que soit leur ordre et de compter le nombre d'entrées dans les blocs feuilles.
Pour revenir à notre problématique de départ sur la comparaison entre COUNT(1) et COUNT(*) , on voit bien à travers les exemples ci-dessus que quelle que soit la méthode utilisée on obtient les mêmes performances et le même plan.
Trace 10053
Par curiosité, on pourrait regarder ce que donne la trace 10053 pour le cas du COUNT(1).
Je rappelle que le trace 10053 est une trace générée par l'optimiseur lorsqu'on active l'évènement 10053. Cette trace contient pas mal d'informations sur comment l'optimiseur effectue son travail d'optimisation et notamment sur la manière dont il transforme les requêtes (partie Query Transformations):
Et justement, dans la partie Query Transformations de la trace 10053 on remarque les lignes suivantes:
Apparemment le CBO récrit la requête en remplaçant le COUNT(1) par un COUNT(*).
Voyons ce que donne la trace lorsqu'on exécute la requête avec le COUNT(*):
Le "not done" par rapport au "done" de la trace précédente indique qu'ici la récriture n'a pas été effectué.
CONCLUSION:
Dire que que le COUNT(1) est plus performant que le COUNT(*) (ou l'inverse) est un mythe.
On a pu voir, en regardant les plans d'exécutions, que les performances sont identiques dans les deux cas.
La trace 10053 nous montre que l'optimiseur tente de transformer (lorsque c'est possible) les COUNT(1) ou autres types de COUNT en COUNT(*).
On est typiquement ici dans un mythe bien ancré dans l'esprit de beaucoup d'utilisateurs des bases de données Oracle (développeurs voir même des DBAs).
Pour prouver que les deux manières de compter les lignes ont des performances identiques il suffit d'exécuter ces deux requêtes et de regarder leur plans ainsi que leurs stats d'exécutions:
SQL> create table t1 as select * from dba_objects; Table created. SQL> create index idx_t1 on t1(object_name); Index created. SQL> exec dbms_stats.gather_table_stats(user,'T1'); PL/SQL procedure successfully completed. SQL> ALTER SESSION SET STATISTICS_LEVEL=ALL; Session altered. SQL> select count(1) from t1; COUNT(1) ---------- 80034 1 row selected. SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); Plan hash value: 3724264953 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.12 | 1142 | 1139 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.12 | 1142 | 1139 | | 2 | TABLE ACCESS FULL| T1 | 1 | 80034 | 80034 |00:00:00.10 | 1142 | 1139 | ---------------------------------------------------------------------------------------------- SQL> select count(*) from t1; COUNT(*) ---------- 80034 1 row selected. SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); Plan hash value: 3724264953 ---------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.11 | 1142 | 1139 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.11 | 1142 | 1139 | | 2 | TABLE ACCESS FULL| T1 | 1 | 80034 | 80034 |00:00:00.09 | 1142 | 1139 | ----------------------------------------------------------------------------------------------
Que ce soit avec COUNT(1) ou COUNT(*) le plan d'exécution est identique et les 2 requêtes génèrent le même nombre de logical reads à savoir 1142.
Ce qu'on constate aussi c'est que pour les 2 requêtes l'optimiseur a choisi de faire un Full Table Scan alors que j'ai bien crée un index IDX_T1 sur cette table. L'index étant plus petit que la table on peut se dire qu'on génèrerait moins de logical reads en passant par l'index, n'est-ce pas? Alors pourquoi ignorer l'index?
La raison se situe dans le fait que le champ indexé OBJECT_NAME est NULLABLE, or les valeurs nulles ne sont pas référencées dans un index B-TREE. L'optimiseur ne peut donc pas se baser sur cet index pour compter le nombre de lignes de la table.
Si l'index IDX_T1 avait été un index BITMAP, le CBO aurait pu choisir cet index pour le comptage du nombre de lignes car dans un index BITMAP les valeurs nulles sont indexées.
La preuve:
SQL> drop index idx_t1; Index dropped. SQL> create bitmap index idx_bmp_t1 on t1(object_name); Index created. SQL> select count(*) from t1; COUNT(*) ---------- 80034 SQL> select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); select count(*) from t1 Plan hash value: 2708570690 --------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.09 | 313 | 305 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.09 | 313 | 305 | | 2 | BITMAP CONVERSION COUNT | | 1 | 80034 | 49108 |00:00:00.08 | 313 | 305 | | 3 | BITMAP INDEX FAST FULL SCAN| IDX_BMP_T1 | 1 | | 49108 |00:00:00.05 | 313 | 305 | ---------------------------------------------------------------------------------------------------------------
L'index BITMAP est bien utilisé et le nombre de logical reads est passé de 1142 à 313.
Supprimons cet index BITMAP puis testons ce que donne le plan lorsqu'on crée une contrainte NOT NULL sur la colonne OBJECT_NAME:
SQL> drop index idx_bmp_t1; Index dropped. Elapsed: 00:00:00.01 SQL> create index idx_t1 on t1(object_name); Index created. SQL> alter table t1 modify object_name not null; Table altered. SQL> select count(1) from t1; COUNT(1) ---------- 80034 Plan hash value: 2101382132 --------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.08 | 401 | 393 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.08 | 401 | 393 | | 2 | INDEX FAST FULL SCAN| IDX_T1 | 1 | 80034 | 80034 |00:00:00.06 | 401 | 393 | --------------------------------------------------------------------------------------------------- SQL> select count(*) from t1; COUNT(*) ---------- 80034 Plan hash value: 2101382132 ------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.04 | 401 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.04 | 401 | | 2 | INDEX FAST FULL SCAN| IDX_T1 | 1 | 80034 | 80034 |00:00:00.02 | 401 | ------------------------------------------------------------------------------------------
Pour les 2 requêtes le CBO a bien choisi le même plan avec accès indexé. Il peut faire ce choix car la contrainte NOT NULL lui indique bien que toutes les valeurs de la colonne se trouvent dans l'index IDX_T1. Vous noterez que l'optimiseur a opté pour une opération de type INDEX FAST FULL SCAN et non pas INDEX FULL SCAN. La différence réside dans le fait que dans l'INDEX FAST FULL SCAN tous les blocks de l'index sont récupérés du disque en accès multi-blocks (équivalent au Full Table Scan) alors que dans le cas de l'INDEX FULL SCAN les accès sont faits de manière mono-block. Il est possible ici de passer par un INDEX FAST FULL SCAN car on n'a pas besoin de suivre la structure de l'index (blocks racine puis branches puis feuilles), on a juste besoin de récupérer les blocks de l'index quel que soit leur ordre et de compter le nombre d'entrées dans les blocs feuilles.
Pour revenir à notre problématique de départ sur la comparaison entre COUNT(1) et COUNT(*) , on voit bien à travers les exemples ci-dessus que quelle que soit la méthode utilisée on obtient les mêmes performances et le même plan.
Trace 10053
Par curiosité, on pourrait regarder ce que donne la trace 10053 pour le cas du COUNT(1).
Je rappelle que le trace 10053 est une trace générée par l'optimiseur lorsqu'on active l'évènement 10053. Cette trace contient pas mal d'informations sur comment l'optimiseur effectue son travail d'optimisation et notamment sur la manière dont il transforme les requêtes (partie Query Transformations):
SQL> alter system flush shared_pool; System altered. SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = 'aangour_10053_trace'; Session altered. SQL> ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; Session altered. SQL> explain plan for 2 select count(1) from t1; Explained. SQL> ALTER SESSION SET EVENTS '10053 trace name context off'; Session altered.
Et justement, dans la partie Query Transformations de la trace 10053 on remarque les lignes suivantes:
CNT: Considering count(col) to count(*) on query block SEL$1 (#0) ************************* Count(col) to Count(*) (CNT) ************************* CNT: COUNT() to COUNT(*) done. Final query after transformations:******* UNPARSED QUERY IS ******* SELECT COUNT(*) "COUNT(*)" FROM "TOTO"."T1" "T1"
Apparemment le CBO récrit la requête en remplaçant le COUNT(1) par un COUNT(*).
Voyons ce que donne la trace lorsqu'on exécute la requête avec le COUNT(*):
CNT: Considering count(col) to count(*) on query block SEL$1 (#0) ************************* Count(col) to Count(*) (CNT) ************************* CNT: COUNT() to COUNT(*) not done.
Le "not done" par rapport au "done" de la trace précédente indique qu'ici la récriture n'a pas été effectué.
CONCLUSION:
Dire que que le COUNT(1) est plus performant que le COUNT(*) (ou l'inverse) est un mythe.
On a pu voir, en regardant les plans d'exécutions, que les performances sont identiques dans les deux cas.
La trace 10053 nous montre que l'optimiseur tente de transformer (lorsque c'est possible) les COUNT(1) ou autres types de COUNT en COUNT(*).
