Why doesn’t /*+ NO_INDEX*/ hint , affect the “execution plan”

execution-planoracleoracle-11g-r2performanceplsql

I have a sample table with below structure :

create table mi_dimcustomer
(customer_num    number(10),
 <other columns> <data types>)

And there is a unique index on column customer_num. I'm trying to hint the optimizer not to use this index (just for the sake of practice) like this :

select /*+gather_plan_statistics*/ /*+ no_index(t idx1_dimcustomer) */
 *
  from mi_dimcustomer t
 where t.customer_num = 12;

But in the execution plan , I can see that the optimizer is still using the index! This is the way I'm capturing the execution plan :

Step-1)

    select sql_id, child_number, sql_text
      from v$sql
     where sql_text like '%where t.CUSTOMER_NUM = 12%';

Step-2)    

select *
  from table(dbms_xplan.display_cursor('2qataxp9mahpj',
                                       '0',
                                       'ALLSTATS LAST +COST +OUTLINE'))

And you can see the execution plan below:

SQL_ID  2qataxp9mahpj, child number 0
-------------------------------------
select /*+gather_plan_statistics*//*+ NO_INDEX(t idx1_dimcustomer) */ * 
from mi_dimcustomer t where t.CUSTOMER_NUM = 12
 
Plan hash value: 3784660444
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |        |     2 (100)|      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MI_DIMCUSTOMER   |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | IDX1_DIMCUSTOMER |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------

Why is this happening?

Thanks in advance

Best Answer

Overview of Optimizer Hints

... A statement block can have only one comment containing hints, ...

select /*+gather_plan_statistics no_index(t idx1_dimcustomer) */
 *
  from mi_dimcustomer t
 where t.customer_num = 12;
Related Question