Utilité d'un index sur requête "IS NOT NULL" ?

Version imprimable

22/02/2013, 11h00
jastroc

Utilité d'un index sur requête "IS NOT NULL" ?
Bonjour tout le monde,

Voilà le contexte :
J'ai une simple requête
Code:

1 2 3 4 SELECT * FROM matable T WHERE T.c IS NOT NULL ORDER BY T.a, T.b;
Aurais-je un intérêt à créer un index sur le champ a / Un index est-il toujours utile (pour gagner en temps d'exécution) ?

Pour trouver une valeur précise (a='toto' ou a>'toto') c'est sur, mais j'ai un doute sur des critères du genre "IS (NOT) NULL" ?

merci :)
22/02/2013, 11h18
Waldar
La décision dépend de deux choses :
1. votre SGBD
2. la distribution des données dans votre colonne
22/02/2013, 11h37
jastroc

Pour le SGDB c'est Oracle (>9) normalement et éventuellement Informix aussi (mais c'est plus Oracle qui m'intéresse).

Qu'appelez vous la répartition des données ? Ses valeurs possibles ? Le champ est de type DATE donc il n'y a pas un nombre fixe de valeurs possibles.

Ce que j'appelle répartition des données c'est la distribution des valeurs dans votre colonne :

Code:

1
2
3
  select macolonne, count(*)
    from matable
group by macolonne;

L'index est utile si le filtre est sélectif : c'est à dire qu'il renvoie peu de lignes.

Il faut savoir que de nombreux SGBD n'indexent pas les nulls, Oracle en fait partie.
Néanmoins vous pouvez le leurrer en indexant une constante :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table t_jastroc (col number(1) null);
 
insert into t_jastroc (col) values (0);
insert into t_jastroc (col) values (1);
insert into t_jastroc (col) values (2);
insert into t_jastroc (col) values (3);
insert into t_jastroc (col) values (4);
insert into t_jastroc (col) values (5);
insert into t_jastroc (col) values (6);
insert into t_jastroc (col) values (7);
insert into t_jastroc (col) values (8);
insert into t_jastroc (col) values (9);
insert into t_jastroc (col) values (null);
 
create index i_jastroc1 on t_jastroc (col);

Si on prend une première requête simple et qu'on regarde son plan d'exécution, on voit bien l'utilisation de l'index :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select col
  from t_jastroc
 where col = 0;
 
-----------------------------------------------------------------------------------------
| Id  | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |      1 |        |      1 |00:00:00.01 |       1 |
|*  1 |  INDEX RANGE SCAN| I_JASTROC1 |      1 |      1 |      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):                                      
---------------------------------------------------                                      
 
   1 - access("COL"=0)

La même requête en cherchant le null :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select col
  from t_jastroc
 where col is null;
 
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |      1 |00:00:00.01 |       7 |
|*  1 |  TABLE ACCESS FULL| T_JASTROC |      1 |      1 |      1 |00:00:00.01 |       7 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):                                      
---------------------------------------------------                                      
 
   1 - filter("COL" IS NULL)

L'index est donc inutile ici.

Recréons un index avec une constante, et repassons la requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create index i_jastroc2 on t_jastroc (col, 0);
 
select col
  from t_jastroc
 where col is null;
 
-----------------------------------------------------------------------------------------
| Id  | Operation        | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |            |      1 |        |      1 |00:00:00.01 |       1 |
|*  1 |  INDEX RANGE SCAN| I_JASTROC2 |      1 |      1 |      1 |00:00:00.01 |       1 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):                                      
---------------------------------------------------                                      
 
   1 - access("COL" IS NULL)

Ici le nouvel index a bien été utilisé.

22/02/2013, 12h42
jastroc

Ça dépend ce qu'on appelle "peu" de ligne ? Par rapport aux nombre de ligne dans la table, oui c'est probablement peu en proportions, mais c'est de l'ordre de la centaine (voir du millier ?) de lignes je pense.

Si j'ai bien compris le comportement ne sera pas le même si je cherche les NOT NULL, étant donné que dans ce cas les valeurs sont indexées ?

Sinon je ne connaissais pas cet outil de visualisation du plan d'exécution, comment le lancer ?

En tout cas merci pour les réponses.
22/02/2013, 14h02
Waldar
Citation:

Envoyé par jastroc

Ça dépend ce qu'on appelle "peu" de ligne ? Par rapport aux nombre de ligne dans la table, oui c'est probablement peu en proportions, mais c'est de l'ordre de la centaine (voir du millier ?) de lignes je pense.

Des statistiques à jour ou au moins dans l'ordre du réel permettent à l'optimiser de faire ce choix.
100.000 lignes sur une table de 200.000 n'est pas sélectif, mais sur une table d'un milliard de lignes ça l'est.

Citation:

Envoyé par jastroc

Si j'ai bien compris le comportement ne sera pas le même si je cherche les NOT NULL, étant donné que dans ce cas les valeurs sont indexées ?

Exactement.

Citation:

Envoyé par jastroc

Sinon je ne connaissais pas cet outil de visualisation du plan d'exécution, comment le lancer ?

Ça dépend ce que vous utilisez comme outil pour vos requêtes.
Là j'ai utilisé des commandes Oracle qui sortent une mise en page texte facilement lisible sur un forum.

Plan estimé :
la requête n'est pas exécutée
Code:

1 2 3 4 5 6 set linesize 150; -- Ajustable explain plan for <votre_requête>; SELECT * FROM TABLE(dbms_xplan.display);
Plan réel :
la requête est exécutée
Code:

1 2 3 4 5 6 set linesize 150; -- Ajustable select /*+ gather_plan_statistics */ <le_reste_de_votre_requête>; SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
22/02/2013, 14h10
jastroc

Ok, dans ce cas je vais donc probablement ajouter index.

Merci pour les infos & précisions.

Les valeurs nulles ne sont pas indexées dans un index à une seule colonne. C’est pour cela que Waldar a ajouté une constante à son index. Vous pouvez suivre la suggestion de Waldar ou bien, pour avoir un index beaucoup plus petit en volume et donc plus efficace, vous pouvez faire comme suit:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
 
SQL> drop table t1;
 
Table dropped.
 
SQL> CREATE TABLE T1 (
  2    C1 NUMBER,
  3    C2 VARCHAR2(10),
  4    C3 VARCHAR2(300));
 
Table created.
 
SQL> INSERT INTO
  2    T1
  3  SELECT
  4    *
  5  FROM
  6    (SELECT
  7      ROWNUM C1,
  8      DECODE(MOD(ROWNUM,10),0,null,rownum||'mm') C2,
  9      RPAD('A',300,'A') C3
 10    FROM
 11      DUAL
 12    CONNECT BY
 13      LEVEL <= 100)
 14  ORDER BY
 15    C2;
 
100 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> CREATE INDEX IND_T1_C2 ON T1(C2);
 
Index created.
 
 
SQL> select count(1) from t1;
 
  COUNT(1)
----------
       100
 
SQL> select count(1) from t1 where c2 is null;
 
  COUNT(1)
----------
        10
 
SQL> explain plan for select count(1) from t1 where c2 is null;
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     7 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     7 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |    10 |    70 |     5   (0)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C2" IS NULL)
 
Note
-----
   - dynamic sampling used for this statement
 
 
SQL> create index ind_t1_fbi on t1 (case when c2 is null then 'X' else null end);
 
Index created.
 
SQL> explain plan for select count(1) from t1 where (case when c2 is null then 'X' else null end) = 'X';
 
Explained.
 
SQL> select * from table(dbms_xplan.display);
 
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |            |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IND_T1_FBI |    10 |    70 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access(CASE  WHEN "C2" IS NULL THEN 'X' ELSE NULL END ='X')
 
Note
-----
   - dynamic sampling used for this statement
 
 
SQL> select count(1) from t1 where (case when c2 is null then 'X' else null end) = 'X';
 
  COUNT(1)
----------
        10

Edit : petite correction : else null au lieu de else c2 : sinon l'index ne sera pas plus petit :lol:

22/02/2013, 14h49
Rei Ichido

Combien y a-t-il de colonnes dans la table T ?
- S'il y en a peu (ou surtout si on peut ne pas toutes les requérir),
- Et si la colonne NOT NULL est peu sélective
=> il pourrait être intéressant de créer un index sur (a,b,c, autre colonnes sélectionnées), afin d'avoir directement le tri fait sur a,b.
22/02/2013, 15h06
jastroc

Citation:

Envoyé par Mohamed.Houri

Les valeurs nulles ne sont pas indexées dans un index à une seule colonne. C’est pour cela que Waldar a ajouté une constante à son index. Vous pouvez suivre la suggestion de Waldar ou bien, pour avoir un index beaucoup plus petit en volume et donc plus efficace, vous pouvez faire comme suit:

Merci beaucoup pour toutes ces précisions mais honnêtement je suis un peu largué là :D. Je suis dev et non pas DBA, donc j'ai besoin de choses simples/standard (surtout pour cette histoire d'index qui n'est pas vraiment capitale, donc je vais éviter d'aller dans le trop complexe).

Citation:

Envoyé par Rei Ichido

Combien y a-t-il de colonnes dans la table T ?
- S'il y en a peu (ou surtout si on peut ne pas toutes les requérir),
- Et si la colonne NOT NULL est peu sélective
=> il pourrait être intéressant de créer un index sur (a,b,c, autre colonnes sélectionnées), afin d'avoir directement le tri fait sur a,b.

Le seul tri à faire est sur ma colonne "NOT NULL", je dois parcourir tous les enregistrements je n'ai pas d'autre solution.
22/02/2013, 15h37
Rei Ichido
Citation:

Envoyé par jastroc

Le seul tri à faire est sur ma colonne "NOT NULL", je dois parcourir tous les enregistrements je n'ai pas d'autre solution.

Dans la requête initiale il y a un ordre demandé :
Citation:
Code:

1 2 3 4 SELECT * FROM matable T WHERE T.c IS NOT NULL ORDER BY T.a, T.b;
C'est à cet ordre (=tri) que je faisais allusion. Potentiellement s'il y a dans la table 100 000 éléments, dont 1000 où c IS NULL, alors ce qui prend du temps ce n'est pas de parcourir les 100 000 éléments pour en enlever 1000, mais trier les 99 000 éléments restants. D'où l'index sur le bon ordre ; mais cela nécessite d'avoir toutes les colonnes retournées dans l'index pour être vraiment rentable.
Par exemple si vous avez besoin des colonnes a, b, et d, alors l'index est envisageable :

Code:

CREATE INDEX IDX_TRI ON T (a, b, c, d)

Le but étant d'avoir en premier a, b pour l'ordre, puis c pour la discrimination, puis d pour éviter d'aller chercher la valeur dans la table.
22/02/2013, 16h11
jastroc

Ah ok. Je sélectionne toutes les colonnes, et j'ai déjà un index sur (a,b).
Je pense qu'il vaut mieux ajouter un index sur c qu'en ajouter un sur (a,b,c) non ?
22/02/2013, 16h20
Rei Ichido

Cela dépend de la sélectivité sur c. Si d'ailleurs il y a au contraire très peu de valeurs not null, alors un index efficace deviendrait (c,a,b).