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 !