sqloracle-databasequery-performancehints

How to force oracle to not to use an index

I have a requirement in which i have to force the sql not to use a particular index which exists on a table.

for example, 

create table t1(id varhcar2(10),data1 varchar2(3000));
create table t2(id varhcar2(10),data2 varchar2(3000));

create index id1 on t1(id);

select * from t1,t2 where t1.id=t2.id;

I cannot drop the index id1 and neither drop it as i dont have rights on it. therefore i want to add some kind of hint to avoid using it..

Is there any such hint, or is there any workaround for this.

Thanks in advance

Solution

  • Try using NO_INDEX hint

    For instance

    SELECT /*+ NO_INDEX(t1 id1) */ 
  FROM t1,
       t2  
 WHERE t1.id = t2.id;