如何强制oracle不使用索引

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

我有一个要求,我必须强制sql不要使用表上存在的特定索引。

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..

我不能删除索引id1,也不能删除它,因为我没有权限。因此,我想添加一些提示,以避免使用它..

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

是否有任何此类提示,或者是否有任何解决方法。

Thanks in advance

提前致谢

3 个解决方案

#1

use NO_INDEX hint

使用NO_INDEX提示

http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements006.htm#BABHJBIB

for instance

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

#2

There's a general principle that for every query for which you want to specify the execution plan, you need something like two or three hints per table.

一般原则是,对于要为其指定执行计划的每个查询,每个表需要两个或三个提示。

In this case, you're probably looking for a hash join resulting from two full table scans, which is fairly simple so the hint block would be something like:

在这种情况下,您可能正在寻找由两个全表扫描产生的散列连接,这非常简单,因此提示块将类似于:

select /*+ full(t1) full(t2) use_hash(t1 t2) */
...

#3

You can prevent the use of an index on a column without hints by applying a function to it. You'll want to use a "no-op" function so the column values aren't changed. For numbers this could be adding zero, for strings appending the empty string:

通过对其应用函数,可以防止在没有提示的列上使用索引。您将需要使用“无操作”功能,因此不会更改列值。对于数字,对于附加空字符串的字符串,这可能会加零:

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

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

#1