Discussion :

[jastroc]

Bonjour tout le monde,

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[Waldar]

La décision dépend de deux choses :

1. votre SGBD
2. la distribution des données dans votre colonne

[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.

[Waldar]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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é.

[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.

[Waldar]

Ç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.

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.

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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'));

[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:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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