0

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

2

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;
1
  • That was correct , now the query takes 7 seconds and it does not use the index , I can see "TABLE ACCESS FULL" in the execution plan. Thanks a lot , you're awesome.
    – Pantea
    Jul 25 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.