Il y'a quelques semaines de cela j'expliquais à un collègue, qui me posait des questions sur le rôle des index dans Oracle, que les accès indexés pouvaient aussi être utilisés par le CBO pour autre chose qu'une récherche de lignes, comme par exemple pour éviter un tri ou un Full Table Scan.
Je décidai alors de lui faire une petite démo rapide:
J'ai juste crée une table TEST1 de 92320 lignes qui est en fait une copie de DBA_OBJECTS. J'ai également créé un index sur la colonne OBJECT_TYPE.
Ce que je voulais montrer à mon collègue c'était que le CBO était capable de se servir de cet index lors d'un COUNT(*) sur cette table ou bien pour une requête effectuant un tri sur la colonne OBJECT_TYPE.
Sûr de moi je commençai par lui montrer le cas du COUNT(*):
Moi qui voulait épater mon collègue j'avais plutôt l'air bête.
Au lieu de prendre le temps de réfléchir j'ai préféré utiliser un hint pour forcer l'utilisation de l'index mais là encore toujours le Full Scan.
Hum...très embarassé, je suis passé alors au test suivant consistant à trier mes lignes de la table sur la colonne indexée OBJECT_TYPE:
J'ai pourtant un index, trié par définition sur le champ OBJECT_TYPE, et le CBO préfère effectuer un full scan de la table suivi d'une opération de tri (SORT ORDER BY).
Pour info, l'opération SORT AGGREGATE du plan précédent n'effectue aucun tri contrairement à ce que son nom pourrait laisser entendre. Il effectue juste un comptage mais en aucun cas il ne trie quoi que ce soit.
Je vous avoue que je suis resté plusieurs minutes complètement incrédule à ce qu'il se passait. Et c'était pourtant d'une évidence absolue.
Un DESCRIBE sur ma table m'aida à refaire jaillir la lumière dans mon esprit:
Toutes les colonnes sont NULLables.
Vous savez tous qu'un index ne stock pas de valeurs NULL et que donc pour qu'un index puisse être utilisé dans mes 2 requêtes il ne suffit pas qu'il y' ait réellement que des valeurs NULL, il faut que l'optimiseur en soit sûr et certain. C'est ce à quoi sert la contrainte NOT NULL pour le CBO. Elle lui indique qu'il ne peut y avoir de valeurs NULL pour la colonne en question et que donc l'utilisation de l'index est possible sur cette colonne car le CBO a la certitude que l'index contiendra toutes les lignes de la table.
Ajoutons donc la contrainte NOT NULL à la colonne OBJECT_TYPE et regardons les plans des requêtes précédentes:
Quant à la deuxième requête on voit que le CBO a opté pour un parcours séquentiel de l'index (INDEX FULL SCAN) évitant ainsi l'opération SORT ORDER BY effectuant le tri. On constate que grâce à cet index le COST du plan est passé de 22161 à 4492.
Pour un index composite il n'est pas necéssaire que toutes les colonnes soient NOT NULL pour qu'il soit pris en compte. Il suffit qu'au moins une des colonnes constituant l'index soit NOT NULL.
CONCLUSION:
Voilà donc comment malgré plusieurs années d'expérience sur Oracle on arrive à se faire avoir sur des choses évidentes qu'on sait depuis longtemps. Tom Kyte disait qu'il se forcait à lire au moins une fois par an la doc Database Concepts car on a tendance à les oublier et ils permettent de faire face à toutes les problématiques.
Cet article a également pour but de mettre en évidence à quel point il est important de donner un maximum d'informations au CBO pour que celui nous choisisse le plan optimal. Les statistiques objets sont certes indispensables mais l'optimiseur a également besoin de connaitre le type de contrainte (NOT NULL, FK, PK) qui lui permettent notamment d'estimer de meilleures cardinalités.
Je décidai alors de lui faire une petite démo rapide:
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> select count(*) from test1;
COUNT(*)
----------
92320
SQL> create index idx_test1 on test1(object_type);
Index created.
J'ai juste crée une table TEST1 de 92320 lignes qui est en fait une copie de DBA_OBJECTS. J'ai également créé un index sur la colonne OBJECT_TYPE.
Ce que je voulais montrer à mon collègue c'était que le CBO était capable de se servir de cet index lors d'un COUNT(*) sur cette table ou bien pour une requête effectuant un tri sur la colonne OBJECT_TYPE.
Sûr de moi je commençai par lui montrer le cas du COUNT(*):
SQL> explain plan for 2 select count(*) from test1; Explained. SQL> @plan SQL> SET LINES 500 SQL> SET PAGES 500 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------- Plan hash value: 3896847026 -------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 423 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEST1 | 89162 | 423 (1)| 00:00:01 | -------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2)Aie!!!Le CBO a complètement ignoré mon index :-(
Moi qui voulait épater mon collègue j'avais plutôt l'air bête.
Au lieu de prendre le temps de réfléchir j'ai préféré utiliser un hint pour forcer l'utilisation de l'index mais là encore toujours le Full Scan.
Hum...très embarassé, je suis passé alors au test suivant consistant à trier mes lignes de la table sur la colonne indexée OBJECT_TYPE:
SQL> explain plan for select * from test1 order by object_type; Explained. SQL> @plan SQL> SET LINES 500 SQL> SET PAGES 500 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- Plan hash value: 1692556001 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 89162 | 31M| | 22161 (1)| 00:00:01 | | 1 | SORT ORDER BY | | 89162 | 31M| 34M| 22161 (1)| 00:00:01 | | 2 | TABLE ACCESS FULL| TEST1 | 89162 | 31M| | 423 (1)| 00:00:01 | ------------------------------------------------------------------------------------ Note ----- - dynamic statistics used: dynamic sampling (level=2)Mon index n'était toujours pas utilisé T_T
J'ai pourtant un index, trié par définition sur le champ OBJECT_TYPE, et le CBO préfère effectuer un full scan de la table suivi d'une opération de tri (SORT ORDER BY).
Pour info, l'opération SORT AGGREGATE du plan précédent n'effectue aucun tri contrairement à ce que son nom pourrait laisser entendre. Il effectue juste un comptage mais en aucun cas il ne trie quoi que ce soit.
Je vous avoue que je suis resté plusieurs minutes complètement incrédule à ce qu'il se passait. Et c'était pourtant d'une évidence absolue.
Un DESCRIBE sur ma table m'aida à refaire jaillir la lumière dans mon esprit:
SQL> desc test1 Name Null? Type ----------------------------------------------------------------------------------------------------------------- -------- ------------ OWNER VARCHAR2(128) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(128) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(23) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(128) SHARING VARCHAR2(13) EDITIONABLE VARCHAR2(1) ORACLE_MAINTAINED VARCHAR2(1)
Toutes les colonnes sont NULLables.
Vous savez tous qu'un index ne stock pas de valeurs NULL et que donc pour qu'un index puisse être utilisé dans mes 2 requêtes il ne suffit pas qu'il y' ait réellement que des valeurs NULL, il faut que l'optimiseur en soit sûr et certain. C'est ce à quoi sert la contrainte NOT NULL pour le CBO. Elle lui indique qu'il ne peut y avoir de valeurs NULL pour la colonne en question et que donc l'utilisation de l'index est possible sur cette colonne car le CBO a la certitude que l'index contiendra toutes les lignes de la table.
Ajoutons donc la contrainte NOT NULL à la colonne OBJECT_TYPE et regardons les plans des requêtes précédentes:
SQL> alter table test1 modify (object_type not null); Table altered. SQL> explain plan for 2 select count(*) from test1; Explained. SQL> @plan SQL> SET LINES 500 SQL> SET PAGES 500 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------- Plan hash value: 2671621383 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 71 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FAST FULL SCAN| IDX_TEST1 | 89162 | 71 (0)| 00:00:01 | --------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2) SQL> explain plan for select * from test1 order by object_type; Explained. SQL> @plan SQL> SET LINES 500 SQL> SET PAGES 500 SQL> select * from table(dbms_xplan.display); PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------------------------------------- Plan hash value: 2541586222 ----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 89162 | 31M| 4492 (1)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| TEST1 | 89162 | 31M| 4492 (1)| 00:00:01 | | 2 | INDEX FULL SCAN | IDX_TEST1 | 89162 | | 258 (1)| 00:00:01 | ----------------------------------------------------------------------------------------- Note ----- - dynamic statistics used: dynamic sampling (level=2)Ha ha!!! mon honneur est sauf. Cette fois pour le count(*) le CBO a decidé de parcourir l'index pour compter les lignes au lieu de toute la table, ce qui est logique, le segment INDEX étant plus petit que la table il y'a moins de blocks à lire en faisant un INDEX FAST FULL SCAN. D'ailleurs on voit que le COST n'est que de 71 alors qu'il était de 423 avec le Full Table Scan.
Quant à la deuxième requête on voit que le CBO a opté pour un parcours séquentiel de l'index (INDEX FULL SCAN) évitant ainsi l'opération SORT ORDER BY effectuant le tri. On constate que grâce à cet index le COST du plan est passé de 22161 à 4492.
Pour un index composite il n'est pas necéssaire que toutes les colonnes soient NOT NULL pour qu'il soit pris en compte. Il suffit qu'au moins une des colonnes constituant l'index soit NOT NULL.
CONCLUSION:
Voilà donc comment malgré plusieurs années d'expérience sur Oracle on arrive à se faire avoir sur des choses évidentes qu'on sait depuis longtemps. Tom Kyte disait qu'il se forcait à lire au moins une fois par an la doc Database Concepts car on a tendance à les oublier et ils permettent de faire face à toutes les problématiques.
Cet article a également pour but de mettre en évidence à quel point il est important de donner un maximum d'informations au CBO pour que celui nous choisisse le plan optimal. Les statistiques objets sont certes indispensables mais l'optimiseur a également besoin de connaitre le type de contrainte (NOT NULL, FK, PK) qui lui permettent notamment d'estimer de meilleures cardinalités.
