Découvrez ignore_optim_embedded_hints, le HINT qui force à ignorer les HINTS… Voici une requête avec un HINT:

set autotrace on explain

select /*+ no_index(t t_idx) */ count(copy.c)
  from t, t copy
 where t.a=copy.a;

COUNT(COPY.C)
-------------
       100000


Execution Plan
---------------------------------------------------
Plan hash value: 791582492

---------------------------------------------------
| Id  | Operation     | Name | Rows  | Cost |
---------------------------------------------------
|   0 | SELECT STATEMENT    |      |     1 |  394 |
|   1 |  SORT AGGREGATE     |      |     1 |   15 |
|*  2 |   HASH JOIN         |      |   100K| 1464K|
|   3 |    TABLE ACCESS FULL| T    |   100K|  488K|
|   4 |    TABLE ACCESS FULL| T    |   100K|  976K|
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."A"="COPY"."A")

Et maintenant, la même requête avec ce hint:

select 
  /*+ no_index(t t_idx) ignore_optim_embedded_hints */ 
       count(copy.c)
  from t, t copy
 where t.a=copy.a;


COUNT(COPY.C)
-------------
       100000

Execution Plan
----------------------------------------------------------
Plan hash value: 3403003441

-------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Cost |
-------------------------------------------------------
|   0 | SELECT STATEMENT       |       |     1 |  354 |
|   1 |  SORT AGGREGATE        |       |     1 |      |
|*  2 |   HASH JOIN            |       |   100K| 1464K|
|   3 |    INDEX FAST FULL SCAN| T_IDX |   100K|  488K|
|   4 |    TABLE ACCESS FULL   | T     |   100K|  976K|
-------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("T"."A"="COPY"."A")

Et dans la prochaine version, un hint devrait permettre d’ignorer celui-là :o).

Plus sérieusement, ce hint peut être trouvé dans un SQL Profile.