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