Colonne Null / Not Null / Performances.

Hello,
Dans notre joli métier, il nous arrive souvent d'être confronté à des problématiques de performance. Dans bien des cas, ceux-ci proviennent soit du code, soit du design, voir des deux. Avec l’avènement des applications web et des outils de développement rapide ou l'on dispose d'un tableau avec par exemple les 10 premières lignes d'une liste et Evidemment le tout trié.
Petite table de travail pour l'exemple.

SQL> create table lami as select * from dba_objects where object_name is not null;

Table created.
SQL>

Un petit index histoire de (je pense) m'aider dans les perfs.

SQL>
SQL> create index idx01 on lami(object_name);

Index created.

SQL>

Calculons les stats sur notre table.

SQL> exec dbms_stats.gather_table_stats (USER,'LAMI',cascade=>true);

PL/SQL procedure successfully completed.

SQL>

Tout d'abord un petit rappel. Pour avoir les 10 premières lignes de la table triées, voici ce qu'il ne faut pas faire.

SQL> select object_name from lami where rownum <=10 order by object_name;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
CDEF$
C_FILE#_BLOCK#
ICOL$
I_IND1
I_OBJ#
I_OBJ2
I_OBJ5
I_TAB1
TS$
USER$

10 rows selected.

SQL>

Et voici donc ce qu'il faudrait faire

SQL>
SQL> select * from (select object_name from lami order by object_name) where rownum<=10;

OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production
.xdk_version_12.2.0.1.0_production

10 rows selected.

SQL>

Ceci étant dit, activons les statistiques en sortie pour voir ce que cela donne.

SQL>
SQL> set autotrace traceonly
SQL> set linesize 133
SQL>

Résultat : Un FULL SCAN sur la table, 45 000 lectures logiques et donc pas d'utilisation de mon indexe.
Remarque : J'ai volontairement crée ma table lami avec la clause "where object_name is not null". Sous entendu, a priori pas de raison pour que la colonne de ma table soit null.
On retrouve souvent des tables ou pour certaines colonnes la contrainte not null n'est pas en place alors que lorsqu'on investigue on se rend compte d'un point de vue métier, la colonne en question ne sera JAMAIS null.
Alors lorsqu'on a cette information, pourquoi se priver de donner cette information à Oracle.. Qui sait, il en fera bon usage ;)
Let's go !

SQL>
SQL> alter table lami modify object_name not null;

Table altered.

SQL>

Exécutons à nouveau notre requête.

Pas belle la vie ;) L'index est cette fois utilisé, et le résultat n'est pas négligeable... Seulement 4 lectures logiques contre plus de 45 000 lorsque la colonne n'était pas marquée "Not null".

En effet, comme vous le savez les valeurs "Null" ne sont prises en compte dans un indexes mono colonne, et donc si la colonne n'est pas marquée "Not Null" Oracle devra scanner toute la table pour effectuer le tri afin de ne pas oublier les valeurs potentielles à Null... C'est ballot.. surtout si d'un point de vue métier on sait pertinemment que la colonne ne sera JAMAIS à NULL.

Enjoy.