43. 用where替代order by
order by 子句只在兩種嚴(yán)格的條件下使用索引.
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 也能使用索引! 這的確是個(gè)容易被忽視的知識(shí)點(diǎn). 我們來(lái)驗(yàn)證一下:
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. 避免改變索引列的類(lèi)型.
當(dāng)比較不同數(shù)據(jù)類(lèi)型的數(shù)據(jù)時(shí), oracle自動(dòng)對(duì)列進(jìn)行簡(jiǎn)單的類(lèi)型轉(zhuǎn)換.
假設(shè) empno是一個(gè)數(shù)值類(lèi)型的索引列.
select …
from emp
where empno = ‘123’
實(shí)際上,經(jīng)過(guò)oracle類(lèi)型轉(zhuǎn)換, 語(yǔ)句轉(zhuǎn)化為:
select …
from emp
where empno = to_number(‘123’)
幸運(yùn)的是,類(lèi)型轉(zhuǎn)換沒(méi)有發(fā)生在索引列上,索引的用途沒(méi)有被改變.
現(xiàn)在,假設(shè)emp_type是一個(gè)字符類(lèi)型的索引列.
select …
from emp
where emp_type = 123
這個(gè)語(yǔ)句被oracle轉(zhuǎn)換為:
select …
from emp
where to_number(emp_type)=123
因?yàn)閮?nèi)部發(fā)生的類(lèi)型轉(zhuǎn)換, 這個(gè)索引將不會(huì)被用到!
譯者按:
為了避免oracle對(duì)你的sql進(jìn)行隱式的類(lèi)型轉(zhuǎn)換, 最好把類(lèi)型轉(zhuǎn)換用顯式表現(xiàn)出來(lái). 注意當(dāng)字符和數(shù)值比較時(shí), oracle會(huì)優(yōu)先轉(zhuǎn)換數(shù)值類(lèi)型到字符類(lèi)型.
45. 需要當(dāng)心的where子句
某些select 語(yǔ)句中的where子句不使用索引. 這里有一些例子.
在下面的例子里, ‘!=’ 將不使用索引. 記住, 索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中.
不使用索引:
select account_name
from transaction
where amount !=0;
使用索引:
select account_name
from transaction
where amount >0;
下面的例子中, ‘||’是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引.
不使用索引:
select account_name,amount
from transaction
where account_name||account_type=’amexa’;
使用索引:
select account_name,amount
from transaction
where account_name = ‘a(chǎn)mex’
and account_type=’ a’;
下面的例子中, ‘+’是數(shù)學(xué)函數(shù). 就象其他數(shù)學(xué)函數(shù)那樣, 停用了索引.
不使用索引:
select account_name, amount
from transaction
where amount + 3000 >5000;
使用索引:
select account_name, amount
from transaction
where amount > 2000 ;
下面的例子中,相同的索引列不能互相比較,這將會(huì)啟用全表掃描.
不使用索引:
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,’%’);
譯者按:
如果一定要對(duì)使用函數(shù)的列啟用索引, oracle新的功能: 基于函數(shù)的索引(function-based index) 也許是一個(gè)較好的方案.
create index emp_i on emp (upper(ename)); /*建立基于函數(shù)的索引*/
select * from emp where upper(ename) = ‘blacksnail’; /*將使用索引*/
