Where子句

需要当心的WHERE子句

某些SELECT 语句中的WHERE子句不使用索引。 这里有一些例子。

在下面的例子里, ‘!=’ 将不使用索引。 记住, 索引只能告诉你什么存在于表中, 而不能告诉你什么不存在于表中。

不使用索引:

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’;

 

避免在索引列上使用NOT

我们要避免在索引列上使用NOT, NOT会产生在和在索引列上使用函数相同的影响。当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描。

避免在索引列上使用计算

WHERE子句中，如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。

低效：

SELECT … FROM  DEPT  WHERE SAL * 12 > 25000;

高效:

SELECT … FROM DEPT WHERE SAL > 25000/12;

 

避免在索引列上使用IS NULL和IS NOT NULL

避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引。

低效: (索引失效)

SELECT … FROM DEPARTMENT

WHERE DEPT_CODE IS NOT NULL;

高效: (索引有效)

SELECT … FROM DEPARTMENT

WHERE DEPT_CODE >=0;

 

避免改变索引列的类型

当比较不同数据类型的数据时, ORACLE自动对列进行简单的类型转换。

假设 EMPNO是一个数值类型的索引列。

SELECT …

FROM EMP

WHERE EMPNO = ‘123’

实际上,经过ORACLE类型转换, 语句转化为:

SELECT …

FROM EMP

WHERE EMPNO = TO_NUMBER(‘123’)

幸运的是,类型转换没有发生在索引列上,索引的用途没有被改变。

 

现在,假设EMP_TYPE是一个字符类型的索引列。

SELECT …

FROM EMP

WHERE EMP_TYPE = 123

这个语句被ORACLE转换为:

SELECT …

FROM EMP

WHERE TO_NUMBER(EMP_TYPE)=123

 

因为内部发生的类型转换, 这个索引将不会被用到!

为了避免ORACLE对你的SQL进行隐式的类型转换, 最好把类型转换用显式表现出来。