L'importance de la contrainte NOT NULL

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:

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.