索引失效

在此仅讨论是否使用INDEX问题, 不涉及执行时间长短问题

IS NULL 或IS NOT NULL操作（判断字段是否为空）

判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。

SELECT *

FROM   mtl_material_transactions mmt

WHERE  mmt.shipment_number IS NOT NULL

 

> 及 < 操作符（大于或小于操作符）

大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。

 

like '%xx'将不使用索引，但like 'xx%'可以使用索引

不使用索引

SELECT *

FROM   mtl_material_transactions mmt

WHERE  mmt.shipment_number LIKE  '%12806557'

‘!=’ 将不使用索引. 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中. 

不使用索引:

SELECT ACCOUNT_NAME

FROM TRANSACTION

WHERE AMOUNT !=0;

使用索引:

SELECT ACCOUNT_NAME

FROM TRANSACTION

WHERE AMOUNT >0;

 

‘||’是字符连接函数. 就象其他函数那样, 停用了索引.

不使用索引:

SELECT ACCOUNT_NAME,AMOUNT

FROM TRANSACTION

WHERE ACCOUNT_NAME||ACCOUNT_TYPE=’AMEXA’;

在这边测试时有使用索引

使用索引:

SELECT ACCOUNT_NAME,AMOUNT

FROM TRANSACTION

WHERE ACCOUNT_NAME = ‘AMEX’

AND  ACCOUNT_TYPE=’ A’;

 

‘+’是数学函数. 就象其他数学函数那样, 停用了索引.

不使用索引:

SELECT ACCOUNT_NAME, AMOUNT

FROM TRANSACTION

WHERE AMOUNT + 3000 >5000;

使用索引:

SELECT ACCOUNT_NAME, AMOUNT

FROM TRANSACTION

WHERE AMOUNT > 2000 ;

 

相同的索引列不能互相比较,这将会启用全表扫描.

不使用索引:

SELECT ACCOUNT_NAME, AMOUNT

FROM TRANSACTION

WHERE ACCOUNT_NAME = NVL(:ACC_NAME,ACCOUNT_NAME);

使用索引:

SELECT ACCOUNT_NAME, AMOUNT

FROM TRANSACTION

WHERE ACCOUNT_NAME LIKE NVL(:ACC_NAME,’%’);

使用UPPER,TO_NUMBER也会导致索引失效

不使用索引

SELECT *

FROM   mtl_material_transactions mmt

WHERE  UPPER(mmt.shipment_number) ='12806557'

 

NOT IN将会导致索引失效

不使用索引

SELECT *

FROM   mtl_material_transactions mmt

WHERE  mmt.shipment_number IN ('12806557')

 

sunfruit

    说明了Oracle数据库中的索引隐式失效的问题

1、隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

    由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.

    错误的例子：select * from test where tu_mdn=13333333333;

    正确的例子：select * from test where tu_mdn='13333333333';

   

2、对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

    错误的例子：select * from test where id-1=9;

    正确的例子：select * from test where id=10;

   

3、使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

    错误的例子：select * from test where round(id)=10; 说明，此时id的索引已经不起作用了

    正确的例子：首先建立函数索引，create index test_id_fbi_idx on test(round(id));

                     然后 select * from test where round(id)=10; 这时函数索引起作用了