** 43. ** ** 用 WHERE ** ** 替代 ORDER BY **
ORDER BY 子句只在两种严格的条件下使用索引 .
ORDER BY 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序 .
ORDER BY 中所有的列必须定义为非空 .
WHERE 子句使用的索引和 ORDER BY 子句中所使用的索引不能并列 .
例如 :
表 DEPT 包含以下列 :
DEPT_CODE PK NOT NULL
DEPT_DESC NOT NULL
DEPT_TYPE NULL
非唯一性的索引 (DEPT_TYPE)
低效 : ( 索引不被使用 )
SELECT DEPT_CODE
FROM DEPT
ORDER BY DEPT_TYPE
EXPLAIN PLAN:
SORT ORDER BY
TABLE ACCESS FULL
高效 : ( 使用索引 )
SELECT DEPT_CODE
FROM DEPT
WHERE DEPT_TYPE > 0
EXPLAIN PLAN:
TABLE ACCESS BY ROWID ON EMP
INDEX RANGE SCAN ON DEPT_IDX
** 译者按 : **
** ORDER BY ** ** 也能使用索引 ! ** ** 这的确是个容易被忽视的知识点 . ** ** 我们来验证一下 : **
** SQL> select * from emp order by empno; **
** Execution Plan **
** ---------------------------------------------------------- **
** 0 SELECT STATEMENT Optimizer=CHOOSE **
** 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' **
** 2 1 INDEX (FULL SCAN) OF 'EMPNO' (UNIQUE) **
** 44. ** ** 避免改变索引列的类型 . **
当比较不同数据类型的数据时 , 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 ** ** 进行隐式的类型转换 , ** ** 最好把类型转换用显式表现出来 . ** ** 注意当字符和数值比较时 , ORACLE ** ** 会优先转换数值类型到字符类型 . **
** 45. ** ** 需要当心的 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’;
使用索引 :
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,’%’);
** 译者按 : **
** 如果一定要对使用函数的列启用索引 , ORACLE ** ** 新的功能 : ** ** 基于函数的索引 (Function-Based Index) ** ** 也许是一个较好的方案 . **
** CREATE INDEX EMP_I ON EMP (UPPER(ename)); /* ** ** 建立基于函数的索引 */ **
** SELECT * FROM emp WHERE UPPER(ename) = ‘BLACKSNAIL’; /* ** ** 将使用索引 */ **