Introduction
Une idée courante sous Oracle est qu'une fonction utilisée sur un champ indexé empêche l'utilisation de cet index. Nous allons prouver que c'est plus complexe que cela et que même avec le fameux LIKE '%texte%' ou la sélection de 100% des données, Oracle peut utiliser un index. Il faut néanmoins distinguer comment Oracle traite les fonctions de groupes telles que MIN, MAX, COUNT des fonctions comme SUBSTR, UPPER. Il faut aussi tenir compte si le SELECT se fait uniquement sur la colonne indexée ou non, que la fonction est utilisée dans la clause SELECT ou le WHERE... etc etc. Bref, la relation entre les index et les fonctions est bien plus intéressante que ce que l'on croit de prime abord.



Points d'attention
Aucun.



Base de tests
N'importe quelle base Oracle.



Exemples
============================================================================================
Création de notre base de test

============================================================================================
Comme table de tests nous prenons des données de la table DBA_OBJECTS et les dupliquons jusqu'à obtenir plus de 600 000 lignes.
          SQL> create table test_obj01 as select OWNER, OBJECT_NAME, SUBOBJECT_NAME from dba_objects;
          SQL> insert into test_obj01 select * from test_obj01;
          78417 rows created.

          SQL> /
          ...
          ...

          SQL> select count(*) from test_obj01;
          COUNT(*)
          ----------
          628184

Nous créons un identifiant dans la table avec la pseudo colonne ROWNUM puis on génère les stats.
La colonne OWNER est l'id de la table.
          SQL> update test_obj01 set OWNER = OWNER || to_char(rownum);
          628184 rows updated.

          SQL> commit;

         SQL> select * from test_obj01 where rownum < 11;
         OWNER        OBJECT_NAME    SUBOBJECT_NAME
         --------------- --------------- ---------------
         SYS1        I_FILE#_BLOCK#
         SYS2        I_OBJ3
         SYS3        I_TS1
         SYS4        I_CON1
         SYS5        IND$
         SYS6        CDEF$
         SYS7        C_TS#
         SYS8        I_CCOL2
         SYS9        I_PROXY_DATA$
         SYS10        I_CDEF4
         10 rows selected.

          SQL> exec dbms_stats.gather_schema_stats('HR');
          PL/SQL procedure successfully completed.
      
Ajout d'une contrainte PK sur OWNER pour créer l'index.
         SQL>  alter table test_obj01 add constraint CT_PK_OWNER PRIMARY KEY (OWNER);
         Table altered.
          
         SQL> select INDEX_NAME from user_indexes where table_name = 'TEST_OBJ01';
         INDEX_NAME
         --------------------------------------------------------------------------------
         CT_PK_OWNER

Génération de stats pour l'index.
         SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'HR', indname => 'CT_PK_OWNER');
         PL/SQL procedure successfully completed.


============================================================================================
Fonctions MIN, MAX, COUNT : l'index est utilisé 

============================================================================================
Nous allons maintenant tester une partie des fonctions Oracle sur une colonne indexée et voir si l'index est utilisé, contrairement aux idées reçues.
Je vous le dis de suite, c'est OK avec les fonctions MIN et MAX, pour la simple raison que l'index étant trié, Oracle n'a qu'à parcourir l'index de la racine à la feuille la plus à gauche pour lire la valeur minimale et la plus à droite pour le max. C'eut été dommage de faire un FULL TABLE SCAN alors qu'il suffit de lire seulement 3 ou 4 blocs d'index :-) Vous noterez le mot clé MIN/MAX dans l'opération d'accès à l'index; en revanche le FULL me laisse dubitatif...

Attention, ces fonctions sont dans le SELECT, pas dans le WHERE (impossible car ce sont des fonctions de groupe).
         SQL> set autotrace on

         SQL> select min(OWNER) from test_obj01;
         MIN(OWNER)
         --------------------------------------------------------------------------------
         APEX_050100141925

         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 2103054379
         --------------------------------------------------------------------------------
         | Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)|Time     |
         --------------------------------------------------------------------------------
         |   0 | SELECT STATEMENT       |         |     1 |    12 |     3   (0)|00:00:01 |
         |   1 |  SORT AGGREGATE        |         |     1 |    12 |          |     |
         |   2 |   INDEX FULL SCAN (MIN/MAX)| CT_PK_OWNER |     1 |    12 |     3   (0)|00:00:01 |
         --------------------------------------------------------------------------------

         SQL> select max(OWNER) from test_obj01;
         MAX(OWNER)
         --------------------------------------------------------------------------------
         XFILES77801

         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 2103054379
         --------------------------------------------------------------------------------
         | Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)|Time     |
         --------------------------------------------------------------------------------
         |   0 | SELECT STATEMENT       |         |     1 |    12 |     3   (0)|00:00:01 |
         |   1 |  SORT AGGREGATE        |         |     1 |    12 |          |     |
         |   2 |   INDEX FULL SCAN (MIN/MAX)| CT_PK_OWNER |     1 |    12 |     3   (0)|00:00:01 |
         --------------------------------------------------------------------------------

Pour la fonction COUNT, même résultat : si la colonne est indexée, celui-ci sera utilisé, que la colonne soit NOT NULL ou non! Pourquoi? Rappelez-vous que COUNT(*) calcule le nombre de rows de la table mais que COUNT(col1) ne calcule que le nombre de rows pour lesquelles col1 est différent de NULL : et c'est justement ce qu'il y a dans un index, uniquement les valeurs NOT NULL. Donc pour un COUNT(*) où on veut le nombre total de rows de la table, Oracle utilise l'index de la PK. Et pour les autres colonnes indexées, quelles soient NULL ou NOT NULL, le nombre de rows est toujours dans l'index de la colonne. Le seul cas où Oracle ferait pour, un COUNT(*), un FULL TABLE SCAN, est le cas d'une table sans PK et sans colonne NOT NULL indexée, ce qui est très rare en production.

L'accès à l'index se fait via un INDEX FAST FULL SCAN, ce qui signifie qu'Oracle lit tous les blocs de l'index, dans leur ordre sur le disque dur et non pas dans l'ordre de l'index, ce qui permet de bénéficier du Multiple Bloc Read Count mais pas de bypasser l'opération SORT dans le cas d'un ORDER BY .
         SQL> select count(*) from test_OBJ01;
         COUNT(*)
         ----------
         628184

         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 360070595

         -----------------------------------------------------------------------------
         | Id  | Operation          | Name        | Rows  | Cost (%CPU)| Time     |
         -----------------------------------------------------------------------------
         |   0 | SELECT STATEMENT      |         |      1 |    505   (1)| 00:00:01 |
         |   1 |  SORT AGGREGATE       |         |      1 |         |        |
         |   2 |   INDEX FAST FULL SCAN| CT_PK_OWNER |    628K|    505   (1)| 00:00:01 |
         -----------------------------------------------------------------------------

Voyons COUNT sur une colonne qui n'est pas NOT NULL mais ne contient aucune valeur NULL.
         SQL> select count(*) from test_obj01;
         COUNT(*)
         ----------
         628184

         SQL> alter table test_obj01 add  OBJECT_NAME02 VARCHAR2(128);

         SQL> update test_obj01 set OBJECT_NAME02 = OBJECT_NAME;
         628184 rows updated.

         SQL> commit;
         Commit complete.

         SQL> create index idx_test_obj01_object_name02 on test_obj01(object_name02);
         Index created.

         SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'HR', indname => 'IDX_TEST_OBJ01_OBJECT_NAME02', estimate_percent => 10);
         PL/SQL procedure successfully completed.

Oracle va parcourir l'index pour récupérer le nombre d'enregistrements.
         SQL> select count(OBJECT_NAME02) from test_obj01;
         COUNT(OBJECT_NAME02)
         --------------------
         628184

         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 404222077
         --------------------------------------------------------------------------------
         | Id  | Operation          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
         --------------------------------------------------------------------------------
         |   0 | SELECT STATEMENT      |                  |       1 |      46 |1059   (1)| 00:00:01 |
         |   1 |  SORT AGGREGATE       |                  |       1 |      46 |      |         |
         |   2 |   INDEX FAST FULL SCAN| IDX_TEST_OBJ01_OBJECT_NAME02 |     628K|      27M|1059   (1)| 00:00:01 |
         --------------------------------------------------------------------------------

On utilise cette fois une colonne qui a des valeurs NULL. Et là encore, l'index est utilisé puisque le COUNT(col1) ne va compter que les rows NOT NULL de col1, donc ceux de l'index.
         SQL> create index idx_test_obj01_subobject_name on test_obj01(subobject_name);
         Index created.

         SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'HR', indname => 'IDX_TEST_OBJ01_SUBOBJECT_NAME', estimate_percent => 10);
         PL/SQL procedure successfully completed.

         SQL> select count(SUBOBJECT_NAME) from test_obj01;
         COUNT(SUBOBJECT_NAME)
         ---------------------
         5328

         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 1354457693
         --------------------------------------------------------------------------------
         | Id  | Operation          | Name                  | Rows  | Bytes |Cost (%CPU)| Time     |
         --------------------------------------------------------------------------------
         |   0 | SELECT STATEMENT      |                   |     1 |     2 |    7    (0)| 00:00:01 |
         |   1 |  SORT AGGREGATE       |                   |     1 |     2 |       |          |
         |   2 |   INDEX FAST FULL SCAN| IDX_TEST_OBJ01_SUBOBJECT_NAME |   628K|  1226K|    7    (0)| 00:00:01 |
         --------------------------------------------------------------------------------

Et si maintenant on supprimait l'index de la PK pour voir comment Oracle s'en sort?
Attention, on ne peut pas dropper un index utilisé pour gérer une PK ou contrainte UNIQUE; vous noterez que Oracle vous dit que c'est l'index qui est utilisé pour assurer l'unicité des colonnes PK ou UNIQUE. OK, alors on supprime la PK...
         SQL> drop index CT_PK_OWNER;     
         drop index CT_PK_OWNER
                    *
         ERROR at line 1:
         ORA-02429: cannot drop index used for enforcement of unique/primary key

         SQL> alter table test_obj01 drop constraint ct_pk_owner;
         Table altered.

BINGO!!!!! Même si un index existe avec le bon nombre de rows (à savoir le nombre de rows total de la table), Oracle va faire un FULL TABLE SCAN car il n'a pas d'index sur un champ NOT NULL (implicite comme une PK ou explicite) dans cette table : il ne sait donc pas si dans les index existants, le nombre de rows est équivalent à celui de la table.
         SQL> select count(*) from test_obj01;
         COUNT(*)
         ----------
         628184

         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 1655282428

         -------------------------------------------------------------------------
         | Id  | Operation       | Name    | Rows    | Cost (%CPU)| Time    |
         -------------------------------------------------------------------------
         |   0 | SELECT STATEMENT   |        |     1 |  1134   (1)| 00:00:01 |
         |   1 |  SORT AGGREGATE    |        |     1 |         |        |
         |   2 |   TABLE ACCESS FULL| TEST_OBJ01 |   628K|  1134   (1)| 00:00:01 |
         -------------------------------------------------------------------------


============================================================================================
Autres fonctions que MIN, MAX et COUNT : l'index est utilisé dans certains cas

============================================================================================
On recrée la PK droppée juste avant pour régénérer l'index avec lequel on teste.
         SQL> alter table test_obj01 add constraint ct_pk_owner PRIMARY KEY (OWNER);
         Table altered.

         SQL> exec DBMS_STATS.GATHER_INDEX_STATS (ownname => 'HR', indname => 'CT_PK_OWNER');
         PL/SQL procedure successfully completed.

On utilise maintenant une fonction différente de MIN, MAX ou COUNT, avec comme résultat un pourcentage si faible que l'index devrait être utilisé; dans notre exemple 628 représente 0.1% du total de la table. Attention à l'astuce, on ne fait un SELECT que sur la colonne indexée et donc Oracle va accéder uniquement à l'index et pas à la table car dans l'index se trouve les données recherchées; ceci est valable aussi pour un SELECT de colonnes se trouvant dans N index, Oracle fera alors une fusion d'index pour éviter l'accès à la table.
         SQL> select owner from test_obj01 where substr(owner, -3, 3) = '999';
         628 rows selected.

         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 3303567328

         ------------------------------------------------------------------------------------
         | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time       |
         ------------------------------------------------------------------------------------
         |   0 | SELECT STATEMENT     |           |  6282 | 75384 |   507   (1)| 00:00:01 |
         |*  1 |  INDEX FAST FULL SCAN| CT_PK_OWNER |  6282 | 75384 |   507   (1)| 00:00:01 |
         ------------------------------------------------------------------------------------

         Predicate Information (identified by operation id):
         ---------------------------------------------------
            1 - filter(SUBSTR("OWNER",-3,3)='999')

Si dans le SELECT on met *, cette fois Oracle doit faire un FULL TABLE SCAN car les données recherchées ne sont pas toutes dans l'index .
         SQL> select * from test_obj01 where substr(owner, -3, 3) = '999'
         628 rows selected.

         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 2410895595

         --------------------------------------------------------------------------------
         | Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
         --------------------------------------------------------------------------------
         |   0 | SELECT STATEMENT  |           |  6282 |   490K|  2471     (1)| 00:00:01 |
         |*  1 |  TABLE ACCESS FULL| TEST_OBJ01 |  6282 |   490K|  2471     (1)| 00:00:01 |
         --------------------------------------------------------------------------------

         Predicate Information (identified by operation id):
         ---------------------------------------------------
            1 - filter(SUBSTR("OWNER",-3,3)='999')   
   

============================================================================================
Les fonctions UPPER et LIKE : surprise surprise!

============================================================================================
La fonction UPPER
Voyons maintenant le cas de la fonction UPPER, bien connue des développeurs, et qui ruine les plans d'exécutions en empêchant l'utilisation des index. Premier test avec un seul enregistrement retourné : pas d'index utilisé, ce qui est le résultat attendu.
         SQL> select count(*) from test_obj01 where UPPER(owner)='SYS1';
         COUNT(*)
         ----------
         1

         SQL> select * from test_obj01 where UPPER(owner)='SYS1';
         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 2410895595

         --------------------------------------------------------------------------------
         | Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
         --------------------------------------------------------------------------------
         |   0 | SELECT STATEMENT  |           |  6282 |   490K|  2471     (1)| 00:00:01 |
         |*  1 |  TABLE ACCESS FULL| TEST_OBJ01 |  6282 |   490K|  2471     (1)| 00:00:01 |
         --------------------------------------------------------------------------------

         Predicate Information (identified by operation id):
         ---------------------------------------------------
            1 - filter(UPPER("OWNER")='SYS1')

Comment éviter un FULL TABLE SCAN aussi onéreux?
Deux solutions : soit créer un index sur fonction soit utiliser une contrainte d'intégrité.

Première solution : créer un index sur fonction.
Avantage : l'index est bien utilisé avec la fonction mais c'est un INDEX RANGE SCAN au lieu d'un UNIQUE INDEX SCAN si on ramène une seule donnée.  Inconvénients : un deuxième index à gérer pour Oracle.

         SQL> create index IDX_TEST_OBJ01_UPPER_OWNER on TEST_OBJ01(UPPER(owner));
         Index created.   
   
         SQL> select * from test_obj01 where UPPER(owner)='SYS1';
         Execution Plan
         ----------------------------------------------------------
         Plan hash value: 1763528983

        ------------------------------------------------------------------------------------------------------------------
        | Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
        ------------------------------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT            |                 |     1 |    91 |     4   (0)| 00:00:01 |
        |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_OBJ01         |     1 |    91 |     4   (0)| 00:00:01 |
        |*  2 |   INDEX RANGE SCAN            | IDX_TEST_OBJ01_UPPER_OWNER |     1 |     |     3   (0)| 00:00:01 |
        ------------------------------------------------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------
           2 - access(UPPER("OWNER")='SYS1')

On droppe l'index sur fonction : l'index qui reste n'est pas utilisé car il est sur Owner et pas sur UPPER(owner).
        SQL> drop index IDX_TEST_OBJ01_UPPER_OWNER;
        Index dropped.
   
        SQL> select * from test_obj01 where UPPER(owner)='SYS1';
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 2410895595

        --------------------------------------------------------------------------------
        | Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
        --------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT  |           |  6282 |   503K|  2471     (1)| 00:00:01 |
        |*  1 |  TABLE ACCESS FULL| TEST_OBJ01 |  6282 |   503K|  2471     (1)| 00:00:01 |
        --------------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------
           1 - filter(UPPER("OWNER")='SYS1')

Deuxième solution : triggers et contrainte CHECK
Si on est sur que la colonne OWNER ne contient que des majuscules (soit on force la saisie en majuscules soit on utilise des triggers before ou after INSERT/UPDATE pour modifier les valeurs), alors on peut utiliser une contrainte CHECK pour dire à Oracle que UPPER(owner) et owner sont identiques. De la sorte on évite de créer un deuxième index puisque Oracle utilisera celui existant.
        SQL> alter table test_obj01 add constraint ctx_test_obj01_owner_ck_upp CHECK (owner=UPPER(owner))
        Table altered.
   
BINGO!!!!!!!!!!!! Et là on a un INDEX UNIQUE SCAN au lieu d'un INDEX RANGE SCAN avec la solution de l'index sur fonction.
        SQL> select * from test_obj01 where UPPER(owner)='SYS1';
        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 1842543907

        -------------------------------------------------------------------------------------------
        | Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
        -------------------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT        |          |    1 |    82 |    3   (0)| 00:00:01 |
        |   1 |  TABLE ACCESS BY INDEX ROWID| TEST_OBJ01  |    1 |    82 |    3   (0)| 00:00:01 |
        |*  2 |   INDEX UNIQUE SCAN        | CT_PK_OWNER |    1 |      |    2   (0)| 00:00:01 |
        -------------------------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------
           2 - access("OWNER"='SYS1')
               filter(UPPER("OWNER")='SYS1')


La fonction LIKE  
Et maintenant la fameuse fonction LIKE '%texte%' : parait-il qu'Oracle n'utilise pas d'index dans ce cas.
Hé bien c'est encore faux! Dans certains cas Oracle lira l'intégralité de l'index; l'intérêt est de lire sur disque dur moins de blocs que si on fait un FULL TABLE SCAN, 1852 physical reads dans un cas, 8841 dans un autre (voir plus bas). Bien sur, il ne faut faire un SELECT que sur la donnée de l'index :-)
        SQL> select owner from test_obj01 where owner like '%S%';
        480424 rows selected.

        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 3303567328
        
        --------------------------------------------------------------------------------
        | Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time   |
        --------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT     |           | 31409 |   368K|   506   (1)| 00:00:01 |
        |*  1 |  INDEX FAST FULL SCAN| CT_PK_OWNER | 31409 |   368K|   506   (1)| 00:00:01 |
        --------------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------
           1 - filter("OWNER" LIKE '%S%')

        Statistics
        ----------------------------------------------------------
             96  recursive calls
              0  db block gets
              34000  consistent gets
               1852  physical reads
              0  redo size
           11635478  bytes sent via SQL*Net to client
             352915  bytes received via SQL*Net from client
              32030  SQL*Net roundtrips to/from client
              9  sorts (memory)
              0  sorts (disk)
             480424  rows processed

Maintenant voyons voir si on n'utilise pas l'index. On droppe la contrainte PK et donc l'index associé. On vide aussi le contenu du buffer cache pour obliger Oracle à relire les données sur le disque dur et voir ainsi combien de blocs il doit lire en plus. On passe de 1852 à 8841, preuve que le parcourt complet d'un index est bien moins coûteux que le FULL TABLE SCAN de la table associée.
        SQL> alter system flush buffer_cache;
        System altered.

        SQL> alter table test_obj01 drop constraint CT_PK_OWNER;
        Table altered.

        SQL> select owner from test_obj01 where owner like '%S%';
        480424 rows selected.

        Execution Plan
        ----------------------------------------------------------
        Plan hash value: 2410895595

        --------------------------------------------------------------------------------
        | Id  | Operation      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
        --------------------------------------------------------------------------------
        |   0 | SELECT STATEMENT  |           | 31409 |   368K|  2470     (1)| 00:00:01 |
        |*  1 |  TABLE ACCESS FULL| TEST_OBJ01 | 31409 |   368K|  2470     (1)| 00:00:01 |
        --------------------------------------------------------------------------------

        Predicate Information (identified by operation id):
        ---------------------------------------------------
           1 - filter("OWNER" LIKE '%S%' AND "OWNER" IS NOT NULL)

        Statistics
        ----------------------------------------------------------
              5  recursive calls
              0  db block gets
              40538  consistent gets
               8841  physical reads
              0  redo size
           11635478  bytes sent via SQL*Net to client
             352915  bytes received via SQL*Net from client
              32030  SQL*Net roundtrips to/from client
              2  sorts (memory)
              0  sorts (disk)
             480424  rows processed