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’; /*将使用索引*/
