Utilisation ou non des indexes

Version imprimable

08/05/2013, 21h40
devkais

Utilisation ou non des indexes
Bonjour
1/ Est-il vrai que l'instruction suivante fait toujours du full scan même si un index existe sur la colonne ename :
Code:

1 2 select ename from EMP ;
2/Est-il vrai qu'une clause comme:

Code:

and colonne_alpha = colonne_numerique

empêche l'utilisation d'un index sur la colonne colonne_alpha car conversion implicite ?

3/ L'index sur la colonne empno est-il utilisé dans ce cas :
Code:

1 2 select ename from EMP where empno != 175 ;
4/ Est-ce vrai que la clause IN / NOT IN inhibe l'utilisation de l'index contrairement à la clause Exists / not Exists ? pourquoi ?
5/ Est-ce que la clause IS NOT NULL utilise ou pas l'index ?
merci:ccool:
10/05/2013, 09h54
mnitu
Ca me rappelle les blagues « question à radio Erevan »
Citation:
Question : Est-il vrai qu’on a donné à Ivan Ivanovici une voiture rouge ?
Réponse : Oui mais avec trois petits remarques :

Ce n’était pas rouge mais verte
Ce n’était pas une voiture mais un vélo
On lui a pas donné on lui a pris
1) Vrai, mais ça peut être Table full scan ou Index full scan (mettez ename not null en plus de la présence de l'index)
2) Vrai, mais un index basé sur une fonction peut être utilisé
3) Pour vos autres questions lisez Indexes and NOT Equal (Not Now John) ainsi que les commentaires sur cet article sur le blog de Richard Foote. Et d’une manière générale tout le blog de Richard Foote. :)
10/05/2013, 16h37
pachot

Bonjour,
On ne peut pas répondre à ça.
Ca dépend de la définition de l'indx, de son type, de ce que vous entendez par utilisation de l'index, etc.

Mais vous pouvez tester des cas précis et faire l'explain plan. Et essayer de forçer des utilisations d'index via des hints pour voir si c'est un accès possible ou non.

Cordialement,
Franck.
11/05/2013, 08h26
Mohamed.Houri

Citation:

Envoyé par mnitu

1) Vrai, mais ça peut être Table full scan ou Index full scan (mettez ename not null en plus de la présence de l'index)

Vous vouliez dire peut-être Index Fast full scan ?
En effet, table full scan et Index fast full scan utilisent la même méthode d’accès aux blocks de données à savoir : db file scattered read ou direct path read (multi-block read) alors que Index full scan utilise un ‘’single block read’’ via db file sequential reads en traversant l’index dans un ordre bien précis.

Citation:

Envoyé par Mohamed.Houri

Vous vouliez dire peut-être Index Fast full scan ?

Non! Je veux dire index full scan

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SQL> set autotrace traceonly
SQL> select ename from emp_t
  2  /
 
14 ligne(s) sélectionnée(s).
 
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 1405736511
 
------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |    14 |    84 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | EMP_T_IX1 |    14 |    84 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

@marius

Alors dans ce cas votre réponse est incomplète. Vous semblez limiter l’accès à uniquement deux possibilités : FULL TABLE SCAN et INDEX FULL SCAN. Non seulement vous oubliez deux autres possibilités à savoir INDEX RANGE SCAN et INDEX FAST FULL SCAN, mais vous semblez aussi donner la prépondérance à un accès (INDEX FULL SCAN) qui à mon sens se produit plus en présence d’une opération ORDER BY qui n’est pas le cas ici.

De plus, dans votre très gentil exemple qui est loin de refléter la vraie vie d’une réelle application, est-ce que vous vous êtes posé la question pourquoi le CBO a préféré faire un INDEX FULL SCAN (sur 14 lignes), qui je le rappelle, est une lecture de la totalité de l’index, dans l’ordre, commençant par le premier leaf block à une extrémité de l’index jusqu’à l’autre bout de l’autre extrémité du même index, à un accès INDEX RANGE SCAN ?

Tout simplement parce que son arithmétique lui a dis de le faire (le coût du FULL SCAN est plus avantageux ici pour 14 lignes)

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
 
QUERY BLOCK TEXT
****************
select ename from emp
*********************
SINGLE TABLE ACCESS PATH
  -----------------------------------------
  BEGIN Single Table Cardinality Estimation
  -----------------------------------------
  Table: EMP  Alias: EMP     
    Card: Original: 15  Rounded: 15  Computed: 15.00  Non Adjusted: 15.00
  -----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan           ----> Cost 5
    Cost:  5.01  Resp: 5.01  Degree: 0
      Cost_io: 5.00  Cost_cpu: 95129
      Resp_io: 5.00  Resp_cpu: 95129
 
  Access Path: index (index (FFS))   ----> FAST FULL SCAN Cost 2
    Index: EMP_ENAME
    resc_io: 2.00  resc_cpu: 8921
    ix_sel: 0.0000e+00  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  2.00  Resp: 2.00  Degree: 1
      Cost_io: 2.00  Cost_cpu: 8921
      Resp_io: 2.00  Resp_cpu: 8921
 
  Access Path: index (FullScan)    ----> INDEX FULL SCAN Cost 1
    Index: EMP_ENAME
    resc_io: 1.00  resc_cpu: 10121
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 1.00  Resp: 1.00  Degree: 1
 
  Best:: AccessPath: IndexRange  Index: EMP_ENAME  SCAN   --> remarquez bien INDEX RANGE SCAN!!!
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 15.00   --> alors que le plan choisi est un INDEX FULL SCAN
 
sql_id=3frqmfujukdts.
Current SQL statement for this session:
select ename from emp
 
============
Plan Table
============
-------------------------------------+-----------------------------------+
| Id  | Operation         | Name     | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT  |          |       |       |     1 |           |
| 1   |  INDEX FULL SCAN  | EMP_ENAME|    15 |   105 |     1 |  00:00:01 |
-------------------------------------+-----------------------------------+

Laissez moi maintenant travailler sur un exemple qui s’approche plus de la réalité que celui que vous nous avez montré

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
 
SQL> create table t1
  2      as
  3      with generator as (
  4          select  --+ materialize
  5              rownum id
  6          from dual
  7          connect by
  8              level <= 10000)
  9      select
 10         rownum                  id,
 11         trunc(dbms_random.value(1,1000))    n1,
 12         lpad(rownum,10,'0') small_vc,
 13         rpad('x',100)       padding
 14     from
 15         generator   v1,
 16         generator   v2
 17     where
 18         rownum <= 1000000;
 
Table created.
 
SQL> alter table t1 modify n1 not null;
 
Table altered.
 
SQL> create index ind_n1 on t1(n1);
 
Index created.
 
SQL> alter session set statistics_level=ALL;
 
Session altered.

1)Première méthode : accès brut à un grand volume de données

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
select n1 from t1
 
1000000 rows selected.
 
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------
SQL_ID  904bd6ah64ska, child number 0
-------------------------------------
select n1 from t1
 
Plan hash value: 1099807730
 
--------------------------------------------------------------------------------------------------
| Id  | Operation            | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------
|   1 |  INDEX FAST FULL SCAN| IND_N1 |      1 |    997K|   1000K|00:00:00.01 |    4109 |      3 |
--------------------------------------------------------------------------------------------------

Normal, car un INDEX FAST FULL SCAN n’est qu’une autre variante d’un TABLE FULL SCAN. D’où viendrait le besoin de faire un très couteux INDEX FULL SCAN pour lire 1000K lignes (100%) dans un ordre que je n’ai pas demandé? Par contre, l’index étant plus petit (n1+rowid) il est donc plus avantageux de l’utiliser au lieu et place de la table (4 colonnes). Ceci est confirmé par le fichier trace 10053

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
 
-----------------------------------------
  END   Single Table Cardinality Estimation
  -----------------------------------------
  Access Path: TableScan
    Cost:  4221.49  Resp: 4221.49  Degree: 0  ----> Cost FULL TABLE SCAN = 4221
      Cost_io: 4185.00  Cost_cpu: 295811475
      Resp_io: 4185.00  Resp_cpu: 295811475
 
  Access Path: index (index (FFS))            ----> Cost IFFS  = 492
    Index: IND_N1
    resc_io: 492.00  resc_cpu: 134798352
    ix_sel: 0.0000e+00  ix_sel_with_filters: 1
  Access Path: index (FFS)
    Cost:  508.63  Resp: 508.63  Degree: 1
      Cost_io: 492.00  Cost_cpu: 134798352
      Resp_io: 492.00  Resp_cpu: 134798352
 
  Access Path: index (FullScan)      ----> Cost IFS  = 2080
    Index: IND_N1
    resc_io: 2080.00  resc_cpu: 214812595
    ix_sel: 1  ix_sel_with_filters: 1
    Cost: 2106.50  Resp: 2106.50  Degree: 1
 
  Best:: AccessPath: IndexFFS  Index: IND_N1
         Cost: 508.63  Degree: 1  Resp: 508.63  Card: 997804.00  Bytes: 0

2) deuxième méthode : accès à un petit volume de données

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
 
SQL> select n1 from t1 where n1 = 318
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  gwb3f6apzynuu, child number 0
-------------------------------------
select n1 from t1 where n1 = 318
 
Plan hash value: 3839779617
 
-------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| IND_N1 |      1 |    998 |   1015 |00:00:00.01 |       8 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("N1"=318)

Normal pourquoi faire un segment (table ou index) full scan pour accéder uniquement à 1% des données

3) troisième méthode : accès à un grand volume de données avec un order by

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
SQL> select n1 from t1 order by n1
 
1000000 rows selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  76xqyrjzsb3a3, child number 0
-------------------------------------
select n1 from t1 order by n1
 
Plan hash value: 2056464276
 
------------------------------------------------------------------------------------
| Id  | Operation       | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------
|   1 |  INDEX FULL SCAN| IND_N1 |      1 |    997K|   1000K|00:00:00.01 |    4076 |
------------------------------------------------------------------------------------

Normal, on voudrait accéder à la totalité du volume dans un ordre bien précis. Il existe un index approprié pour cela et l’accéder en FULL SCAN permet d’éviter un order by sur la table

4) quatrième méthode : accès à un petit volume de données avec un order by

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
 
SQL> select n1 from t1 where n1 between 318 and 400 order by n1
 
82818 rows selected.
 
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
SQL_ID  7514h9ywkzmmc, child number 0
-------------------------------------
select n1 from t1 where n1 between 318 and 400 order by n1
 
Plan hash value: 3839779617
 
-------------------------------------------------------------------------------------
| Id  | Operation        | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|*  1 |  INDEX RANGE SCAN| IND_N1 |      1 |  83980 |  82818 |00:00:00.01 |     342 |
-------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("N1">=318 AND "N1"<=400)

L’arithmétique du CBO a certainement évalué un coup d’un INDEX RANGE SCAN plus avantageux que celui d’un FULL SCAN.

Etc.. etc.. etc..

L’arithmétique du CBO est un modèle mathématique très compliqué pour venir le réduire à un seul type d'accès à un index.

13/05/2013, 14h45
mnitu
Citation:

Envoyé par Mohamed.Houri

@marius

...
De plus, dans votre très gentil exemple qui est loin de refléter la vraie vie d’une réelle application, est-ce que vous vous êtes posé la question pourquoi le CBO a préféré faire un INDEX FULL SCAN (sur 14 lignes), qui je le rappelle, est une lecture de la totalité de l’index, dans l’ordre, commençant par le premier leaf block à une extrémité de l’index jusqu’à l’autre bout de l’autre extrémité du même index, à un accès INDEX RANGE SCAN ?
...

@Mohamed
1. Ce n’est pas mon gentil exemple c’était [dans] la question.
2. Dans ce cas combien de blocs y a-t-il à lire entre l’« extrémité de l’index jusqu’à l’autre bout de l’autre extrémité du même index » d’après vous ?
3. Quand vous changez la requête vous changez le problème

Juste pour jouer, c'est faisable aussi sur emp

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
SQL> select ename from emp;
 
12 ligne(s) sélectionnée(s).
 
SQL> select * from table(dbms_xplan.display_cursor()) ;
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
SQL_ID  3frqmfujukdts, child number 0
-------------------------------------
select ename from emp
 
Plan hash value: 3610277708
 
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |       |       |     2 (100)|          |
|   1 |  INDEX FAST FULL SCAN| IDX_EMP_ENAME |    12 |    72 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
 
13 ligne(s) sélectionnée(s).
 
NAME                           VALUE                          ISDEFAULT
------------------------------ ------------------------------ ---------
db_file_multiblock_read_count  128                            TRUE
optimizer_index_cost_adj       100                            TRUE

Mais bon forcément j'ai joué avec les extrémités. mais pas autant qu'oracle lorsqu'il crée la table emp.

13/05/2013, 15h56
mnitu

@ojo77
Juste une petit remarque: il vous manque deux enregistrements dans votre table emp.

En effet, il convient d'être rigoureux :)

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
SQL> select ename from emp;
 
14 ligne(s) sélectionnée(s).
 
SQL> select * from table(dbms_xplan.display_cursor())
  2 /
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  3frqmfujukdts, child number 0
-------------------------------------
select ename from emp
 
Plan hash value: 3610277708
 
--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |       |       |     2 (100)|          |
|   1 |  INDEX FAST FULL SCAN| IDX_EMP_ENAME |    14 |    84 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
 
13 ligne(s) sélectionnée(s).
 
SQL> select NAME, VALUE, ISDEFAULT from v$parameter where name like '%cost_adj' or name like 'db_file_multi%'
  2  /
 
NAME                           VALUE                          ISDEFAULT
------------------------------ ------------------------------ ---------
db_file_multiblock_read_count  122                            TRUE
optimizer_index_cost_adj       100                            TRUE