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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply