Index B*Tree, INDEX FULL SCAN et fonctions dans une clause WHERE

Commençons par un peu de sémantique ; quel est d’après vous la différence entre les 2 clauses WHERE ci-dessous ?

  • « X like ‘A%' »
  • « substr(X,1,1)=’A’ and X IS NOT NULL »

Il ne me semble pas qu’il y en ait aucune. Notez que je ne demande qu’à me tromper, mais l’opérateur like se comporte comme l’opérateur = vis à vis des valeurs NULL; en outre le jeux de caractère n’impacte pas la fonction substr.

Et pourtant, il existe une différence de taille, ainsi si X est une colonne de type caractères et qu’il existe un index, la première clause pourra utiliser un algorithme INDEX RANGE SCAN tandis que la seconde pourra uniquement utiliser un algorithme INDEX FULL SCAN. Le premier ne parcourant dans la plupart des cas qu’une partie de l’index tandis que le second comme son nom l’indique parcourt l’ensemble de l’index.

Vous n’y croyez pas ?

Un petit exemple pour vous persuader…

(*) Note:
Oracle n’est pas capable d’anticiper l’équivalence entre ID IS NOT NULL et substr(id,1,1) IS NOT NULL, d’où l’ajout du prédicat « X IS NOT NULL » dans ma condition

create table scott.T(id varchar2(10),
text varchar2(4000))
tablespace users;

insert into scott.T
(select dbms_random.string(null,1)||'A'||dbms_random.string(null,8),
rpad('Z',1000,'Z')
from dual
connect by level<=10000);

commit;

exec dbms_stats.gather_table_stats('SCOTT','T',method_opt=>'for all columns size 254');

create index scott.tidx on scott.t(id) tablespace users;

select /*+ INDEX(T TIDX) */ count(text)
from SCOTT.T
where substr(id,1,1) = 'B' and id is not null;

COUNT(TEXT)
-----------
371

set lines 150 pages 1000
select * from table(dbms_xplan.display_cursor(format=>'LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 2rpx4at6w6z8g, child number 1
-------------------------------------
select /*+ INDEX(T TIDX) */ count(text) from SCOTT.T where
substr(id,1,1) = 'B' and id is not null

Plan hash value: 1193005838

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 132 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1012 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 100 | 98K| 132 (0)| 00:00:02 |
|* 3 | INDEX FULL SCAN | TIDX | 100 | | 32 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter((SUBSTR("ID",1,1)='B' AND "ID" IS NOT NULL))

Et maintenant, observons ce qui se passe avec une clause like 'B%'

select count(text)
from SCOTT.T
where id like 'B%';

COUNT(TEXT)
-----------
371

set lines 150 pages 1000
select * from table(dbms_xplan.display_cursor(format=>'LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 50yxmygfa1xc0, child number 0
-------------------------------------
select count(text) from SCOTT.T where id like 'B%'

Plan hash value: 196377347

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 358 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1012 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 354 | 349K | 358 (0)| 00:00:05 |
|* 3 | INDEX RANGE SCAN | TIDX | 354 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID" LIKE 'B%')
filter("ID" LIKE 'B%')

Vous apprécierez, j’en suis sur, le fait que le coût du 2nd plan est plus élevé alors qu’en réalité les performances sont relativement identiques… Techniquement parlant, le premier plan est même moins bon !

Et maintenant, le 2nd effet f(X)

Si maintenant, vous exécutez la première requête sans hint, surprise; l’index n’est pas utilisé :

select count(text)
from SCOTT.T
where substr(id,1,1) = 'B' and id is not null;

COUNT(TEXT)
-----------
371

set lines 150 pages 1000

select * from table(dbms_xplan.display_cursor(format=>'LAST'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID gb56bxddtsp2p, child number 0
-------------------------------------
select count(text) from SCOTT.T where substr(id,1,1) = 'B' and id is
not null

Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 410 (100)| |
| 1 | SORT AGGREGATE | | 1 | 1012 | | |
|* 2 | TABLE ACCESS FULL| T | 100 | 98K| 410 (1)| 00:00:05 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter((SUBSTR("ID",1,1)='B' AND "ID" IS NOT NULL))

Et ceci malgré le fait que le coût de ce plan est de manière évidente plus élevé que le coût du plan avec l’index. La raison est dans la trace 10053 et identique à celle déjà présenté avec un index reverse et un index range scan dans mon article précédent. Le plan est juste ignoré. Pour vous en persuader, regardez le fichier généré ci-dessous :

alter session set events '10053 trace name context forever, level 1';

select /* TEST */ count(text) from SCOTT.T
where substr(id,1,1) = 'B'
and id is not null;

alter session set events '10053 trace name context off';

select tracefile from v$process p, v$session s
where s.sid=sys_context('USERENV', 'SID')
and p.addr=s.paddr;
TRACEFILE
---------------------------------------------------------------
/u01/app/oracle/diag/rdbms/black/BLACK/trace/BLACK_ora_3998.trc

Conclusion:

Supprimez la table :

drop table scott.t purge;

Et tentez d’expliquer ça à un développeur, VOUS !

3 réflexions sur “Index B*Tree, INDEX FULL SCAN et fonctions dans une clause WHERE”

  1. je n’ai jamais dit que je savais la lire 😉 du moins pas encore
    mais déja pour pouvoir appliquer ce que je lit dans les livres d’oracle il faudrait que je teste sur de vrais problèmes.

  2. 10053 est le seul moyen de savoir pourquoi un plan est choisi plutôt qu’un autre. Il est possible de donner accès à ces traces à quelqu’un qui n’accède pas à la base de données. C’est ce que Method-R utilise dans son profiler pour SQL*Developer

    Cela dit, si tu es développeur et que tu sais lire un 10053, change de job !

  3. C’est effectivement très perturbant,
    n’y a t’il pas d’autre moyen que la 10053 pour savoir quans les plans sont ignoré, car acceder à ses fichiers de trace ou même a tkprof et souvent impossible pour les développeurs qui veulent essayer de comprendre ce qui se passe.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *