Je croyais avoir déjà illustré cet exemple dans ce blog; il semble que non! Alors voici un cas où le plan choisi par l’optimiser Oracle n’est pas celui avec le coût le plus faible; Si vous voulez savoir pourquoi, vous pouvez regarder la trace 10053. C’est au delà du sujet de ce post. Ce que vous devez retenir c’est que tout ne fonctionne pas toujours comme attendu.

Pour commencer vous allez créer la table exemple pour notre exemple:

create table demo(id number, text varchar2(4000));

begin
  for i in 1..5000 loop
     insert into demo values (i, rpad('X',1000,'X'));
  end loop;
end;
/

create index demo_idx on demo(id) reverse;

exec dbms_stats.gather_table_stats(user,'DEMO');

Ensuite, vous pouvez regarder le coût et les statistiques du plan qui utilise l’index crée sur la table.

set autotrace on

select /*+ index(demo demo_idx) */ count(text)
  from demo
 where id between 1 and 2;

COUNT(TEXT)
-----------
          2

Execution Plan
----------------------------------------------------------
Plan hash value: 3190157379

---------------------------------------------------------
| Id  | Operation                    | Name     | Cost  |
---------------------------------------------------------
|   0 | SELECT STATEMENT             |          |    26 |
|   1 |  SORT AGGREGATE              |          |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEMO     |    26 |
|*  3 |    INDEX FULL SCAN           | DEMO_IDX |    13 |
---------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("ID"<=2 AND "ID">=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Maintenant si vous regardez le plan et les statistiques de la requête sans le hint, vous remarquerez que le résultat est différent de celui auquel on s’attend a priori:

set autotrace on

select  count(text)
  from demo
 where id between 1 and 2;


COUNT(TEXT)
-----------
          2

Execution Plan
---------------------------
Plan hash value: 2180342005

--------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost |
--------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |  206 |
|   1 |  SORT AGGREGATE    |      |     1 |      |
|*  2 |   TABLE ACCESS FULL| DEMO |     2 |  206 |
--------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID"<=2 AND "ID">=1)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        753  consistent gets
          0  physical reads
          0  redo size
        421  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Bon, vous me direz, il s’agit d’un cas très particulier! Mais au final le métier du DBA n’est-il pas un peu de gérer les cas particuliers? Sinon, on aurait plus besoin de nous!

Et si vous voulez en voir un autre de ces cas particuliers, avec l’explication, cette fois, regardez le dernier post d’Alex.