INDEX FULL SCAN et INDEX FAST FULL SCAN

Par Olivier JOLYMétrologie, SQL, Support Niveau 3, Tuning

Suite à un échange à propos du fonctionnement du CBO et plus particulièrement sur les choix de parcours d’index en full scan ou en fast full scan sur le forum developpez.net qui était de la teneur suivante :

Intervenant 1 – Est-il vrai que si on effectue le requête select ename from emp, oracle n’utilisera pas l’index même si celui-ci existe ?
Intervenant 2 – Vrai sauf si la colonne ename ne peut pas être nulle et ce par contrainte, auquel cas un index full scan sera choisi
Intervenant 3 – Ne vouliez vous pas dire index fast full scan ?
Intervenant 2 – Non :

connect scott/tiger
alter table emp modify ename not null;
select ename from emp ;

select * from table( dbms_xplan.display_cursor() );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
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 |
------------------------------------------------------------------------------

Intervenant 3 – Oui mais sur une table plus grosse ça fait du fast full scan (démo à l’appui) donc si.
Intervenant 2 – Peut être mais la question c’est sur la table emp et pas sur une autre donc relire l’énnoncé et donc re-non
Intervenant 4 – C’est possible aussi sur la table emp 

connect scott/tiger
alter table emp modify ename not null;
select ename from emp ;

select * from table( dbms_xplan.display_cursor() );

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
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 |
--------------------------------------------------------------------------------------

La problématique qui se situe derrière cet échange est pourquoi dans un cas un index full scan est choisi et pas dans l’autre et quelle est au juste la différence entre un index full scan et un index fast full scan ?

La différence profonde entre un index full scan et un index fast full scan réside principalement dans la manière d’aller lire les blocs de données sur disque, le premier effectuera des accès monoblocs (ou db file sequential read) et arborescents alors que le second effectuera des accès multiblocs (ou db file scattered read) et séquentiels sur les seules feuilles de l’index, accès censés être plus rapides dès lors qu’il y a plus d’un bloc à lire.

Le premier plan (celui de l’intervenant 2) utilise un index monobloc tandis que le second plan (celui de l’intervenant 4) utilise un index de deux blocs feuilles.

Comment le choix s’est-il opéré ?

Si on reproduit le premier test : 

drop index IDX_EMP_ENAME ;
create index IDX_EMP_ENAME on emp(ename) ;
alter session set tracefile_identifier=Monobloc_idx ;
alter system flush shared_pool ;
alter session set events='10053 trace name context forever' ;
select ename from emp ;
alter session set events='10053 trace name context off' ;

La lecture de la trace effectuée et plus particulièrement des données liées aux statistiques sur les objets et au mode d’accès aux données présentent les informations suivantes :

***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: EMP  Alias: EMP
    #Rows: 14  #Blks:  5  AvgRowLen:  38.00  ChainCnt:  0.00
Index Stats::
  Index: IDX_EMP_ENAME  Col#: 2
    LVLS: 0  #LB: 1  #DK: 14  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
  Index: PK_EMP  Col#: 1
    LVLS: 0  #LB: 1  #DK: 14  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
Access path analysis for EMP
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for EMP[EMP] 
  Table: EMP  Alias: EMP
    Card: Original: 14.000000  Rounded: 14  Computed: 14.00  Non Adjusted: 14.00
  Access Path: TableScan
    Cost:  3.00  Resp: 3.00  Degree: 0
      Cost_io: 3.00  Cost_cpu: 37987
      Resp_io: 3.00  Resp_cpu: 37987
  Access Path: index (index (FFS))
    Index: IDX_EMP_ENAME
    resc_io: 2.00  resc_cpu: 8801
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  2.00  Resp: 2.00  Degree: 1
      Cost_io: 2.00  Cost_cpu: 8801
      Resp_io: 2.00  Resp_cpu: 8801
  Access Path: index (FullScan)
    Index: IDX_EMP_ENAME
    resc_io: 1.00  resc_cpu: 9921
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 1.00  Resp: 1.00  Degree: 1
  Best:: AccessPath: IndexRange
  Index: IDX_EMP_ENAME
         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 14.00  Bytes: 0

On constate en bleu que les statistiques de l’index montrent que celui-ci est bien constitué d’un unique bloc feuille (#LB pour Number of Leaf-Blocks), l’information se retrouve dans la colonne blocks de la vue user_indexes.

Si on compare les coût d’accès non parallèles (Cost_io)1 en rouge ou vert on s’apperçoit qu’il choisi l’accès le moins coûteux, certes, mais surtout qu’il compte un accès à 2 blocs en cas d’index fast full scan ce qui peut paraitre un peu fort de café alors que de son propre aveux, Oracle ne comptabilise qu’un seul bloc pour l’index. La seule explication raisonnable que je peux voire ici, c’est que pour une entrée/sortie multibloc il lira forcément un minimum de deux blocs ce qui est plus que logique.

Si on reproduit le second test : 

drop index IDX_EMP_ENAME ;
create index IDX_EMP_ENAME on emp(ename) pctfree 96 ;
alter session set tracefile_identifier=Multibloc_idx ;
alter system flush shared_pool ;
alter session set events='10053 trace name context forever' ;
select ename from emp ;
alter session set events='10053 trace name context off' ;

La lecture de la trace effectuée et plus particulièrement des données liées aux statistiques sur les objets et au mode d’accès aux données présentent les informations suivantes :

BASE STATISTICAL INFORMATION
***********************
Table Stats::
  Table: EMP  Alias: EMP
    #Rows: 14  #Blks:  5  AvgRowLen:  38.00  ChainCnt:  0.00
Index Stats::
  Index: IDX_EMP_ENAME  Col#: 2
    LVLS: 1  #LB: 2  #DK: 14  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00
  Index: PK_EMP  Col#: 1
    LVLS: 0  #LB: 1  #DK: 14  LB/K: 1.00  DB/K: 1.00  CLUF: 1.00

Ici le nombre de blocs feuille est de 2 et le nombre total de blocs d’index de 3 (une racine et deux feuilles).

Access path analysis for EMP
***************************************
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for EMP[EMP] 
  Table: EMP  Alias: EMP
    Card: Original: 14.000000  Rounded: 14  Computed: 14.00  Non Adjusted: 14.00
  Access Path: TableScan
    Cost:  3.00  Resp: 3.00  Degree: 0
      Cost_io: 3.00  Cost_cpu: 37987
      Resp_io: 3.00  Resp_cpu: 37987
  Access Path: index (index (FFS))
    Index: IDX_EMP_ENAME
    resc_io: 2.00  resc_cpu: 15923
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000 
  Access Path: index (FFS)
    Cost:  2.00  Resp: 2.00  Degree: 1
      Cost_io: 2.00  Cost_cpu: 15923
      Resp_io: 2.00  Resp_cpu: 15923
  Access Path: index (FullScan)
    Index: IDX_EMP_ENAME
    resc_io: 3.00  resc_cpu: 24164
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 3.00  Resp: 3.00  Degree: 1
******** Begin index join costing ********
  ****** trying bitmap/domain indexes ******
  Access Path: index (FullScan)
    Index: IDX_EMP_ENAME
    resc_io: 3.00  resc_cpu: 24164
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 3.00  Resp: 3.00  Degree: 0
  Access Path: index (FullScan)
    Index: IDX_EMP_ENAME
    resc_io: 3.00  resc_cpu: 24164
    ix_sel: 1.000000  ix_sel_with_filters: 1.000000 
    Cost: 3.00  Resp: 3.00  Degree: 0
  Bitmap nodes:
    Used IDX_EMP_ENAME
      Cost = 3.751463, sel = 1.000000
  ****** finished trying bitmap/domain indexes ******
******** End index join costing ********
  Best:: AccessPath: IndexFFS
  Index: IDX_EMP_ENAME
         Cost: 2.00  Degree: 1  Resp: 2.00  Card: 14.00  Bytes: 0

C’est bien le parcours des feuilles d’index en mode multibloc qui est choisi.

  1. les coûts d’accès parallèles sont donnés par Resp_io