一、 關(guān)于索引的知識(shí)
要寫(xiě)出運(yùn)行效率高的sql，需要對(duì)索引的機(jī)制有一定了解，下面對(duì)索引的基本知識(shí)做一介紹。
1、 索引的優(yōu)點(diǎn)和局限
索引可以提高查詢的效率，但會(huì)降低dml操作的效率。
所以建立索引時(shí)需要權(quán)衡。對(duì)于dml操作比較頻繁的表，索引的個(gè)數(shù)不宜太多。
2、 什么樣的列需要建索引？
經(jīng)常用于查詢、排序和分組的列（即經(jīng)常在where、order或group by子句中出現(xiàn)的列）。
3、 主鍵索引和復(fù)合索引
對(duì)于一張表的主鍵，系統(tǒng)會(huì)自動(dòng)為其建立索引。
如果一張表的幾列經(jīng)常同時(shí)作為查詢條件，可為其建立復(fù)合索引。
4、 建立索引的語(yǔ)句
create index i_staff on staff (empno);
create index i_agent on agent (empno, start_date);
5、 刪除索引的語(yǔ)句
drop index I_staff;
drop index I_agent;
6、 查詢索引的語(yǔ)句
法一：利用數(shù)據(jù)字典
表一：all_indexes 查看一張表有哪些索引以及索引狀態(tài)是否有效
主要字段： index_name, table_name, status
例如：select index_name, status
from all_indexes
where table_name=’STAFF_INFO’;
INDEX_NAME STATUS
--------------------- -----------
I_STAFF VALID
表二：all_ind_columns 查看一張表在哪些字段上建了索引
主要字段： table_name, index_name, column_name, column_position
例如： select index_name, column_name, column_position
from all_ind_columns
where table_name=’AGENT’
INDEX_NAME COLUMN_NAME COLUMN_POSITON
--------------------- ----------------------- --------------------------
I_AGENT EMPNO 1
I_AGENT START_DATE 2
由此可見(jiàn)，agent表中有一個(gè)復(fù)合索引（empno, start_date ）
法二：利用toad工具
toad用戶界面比sql*plus友好，并且功能強(qiáng)大。你可以在toad編輯器中鍵入表名，按F4，便可見(jiàn)到這張表的表結(jié)構(gòu)以及所有索引列等基本信息。
7、 索引的一些特點(diǎn)
1): 不同值較多的列上可建立檢索，不同值少的列上則不要建。比如在雇員表的“性別”列上只有“男”與“女”兩個(gè)不同值，因此就沒(méi)必要建立索引。如果建立索引不但不會(huì)提高查詢效率，反而會(huì)嚴(yán)重降低更新速度。
2): 如果在索引列上加表達(dá)式，則索引不能正常使用
例如：b1,c1分別是表b,c的索引列
select * from b where b1/30< 1000 ;
select * from c where to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
以上都是不正確的寫(xiě)法
3): where子句中如果使用in、or、like、!=，均會(huì)導(dǎo)致索引不能正常使用
例如：select * from b where b1=30 or b1=40;
4): 使用復(fù)合索引進(jìn)行查詢時(shí)必須使用前置列
例如表a上有一個(gè)復(fù)合索引（c1,c2,c3）,則c1為其前置列
如果用c1或c1+c2或c1+c2+c3為條件進(jìn)行查詢，則該復(fù)合索引可以發(fā)揮作用，反之，用c2或c3或c2+c3進(jìn)行查詢，則該索引不能起作用。
二. 書(shū)寫(xiě)sql注意事項(xiàng)：
1、 避免給sql語(yǔ)句中引用的索引列添加表達(dá)式：
典型實(shí)例：
b1,c1分別是表b,c的索引列：
1） select * from b where b1/30< 1000 ;
2） select * from c where to_char(c1,’YYYYMMDD HH24:MI:SS’) = ‘200203 14:01:01’;
替代方案：
1) select * from b where b1 < 30000;
2) select * from c where c1 = to_date(‘20020301 14:01:01’, ‘YYYYMMDD HH24:MI:SS’);
注：在lbs中有兩個(gè)重要字段，pol_info中的undwrt_date和prem_info中的payment_date，這兩個(gè)日期是帶時(shí)分秒的，所以經(jīng)常有同事用to_char 來(lái)查詢某一時(shí)間段的數(shù)據(jù)。
例如：select count(*) from pol_info where to_char(undwrt_date,’YYYYMMDD’)=’20020416’;
select count(*) from prem_info where to_char(undwrt_date,’YYYYMM’)=’200203’;
替代方案：
select count(*) from pol_info
where undwrt_date>=to_date(’20020416’,’YYYYMMDD’) and
undwrt_date<to_date(’20020417’,’YYYYMMDD’);
select count(*) from prem_info
where payment_date>=to_date(’20020301’,’YYYYMMDD’) and
payment_date<to_date(’20020401’,’YYYYMMDD’);
2、 避免在where子句中使用in、or、like、!=
典型實(shí)例：
a1是a表上的索引列：
1) select * from a
where ( a1 = ‘0’ and ...) or (a1 = ‘1’ and ...);
2) select count(*) from a where a1 in (‘0’,’1’) ;
替代方案：
1） select * from a where a1 = ‘0’ and ...
union
select * from a where a1 = ‘1’ and ...
2) select count(*) from a where a1 = ‘0’;
select count(*) from a where a1 = ‘1’;
然后做一次加法運(yùn)算；或者直接用存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)；
小結(jié)：
對(duì)字段使用了 ‘in，or，like’ 做條件、對(duì)字段使用了不等號(hào) ‘!=’，均會(huì)使索引失效；如果不產(chǎn)生大量重復(fù)值，可以考慮把子句拆開(kāi)；拆開(kāi)的子句中應(yīng)該包含索引，或者使用union連結(jié)符代替。另一種方式是使用存儲(chǔ)過(guò)程，它使SQL變得更加靈活和高效。
3、 建立適當(dāng)?shù)乃饕?br />
曾經(jīng)接過(guò)開(kāi)發(fā)的一個(gè)統(tǒng)計(jì)sql， select … from tablea where cola=… and …
運(yùn)行效率非常慢，經(jīng)查tablea數(shù)據(jù)量巨大，再查all_ind_columns，發(fā)現(xiàn)cola是tablea的一個(gè)復(fù)合索引中的一列，但不是前置列。象這種情況，就需要與開(kāi)發(fā)商量，是否針對(duì)cola建一個(gè)索引。
4、 like和substr
對(duì)于‘like’和‘substr’，其效率并沒(méi)有多大分別。但是，當(dāng)所搜索的值不存在時(shí)，使用‘like’的速度明顯大于‘substr’。
所以：select * from a where substr(a1,1,4) = '5378' 可以用like替代
select * from a where a1 like ‘5378%’;
5、 寫(xiě)where條件時(shí)，有索引字段的判斷在前，其它字段的判斷在后；如果where條件中用到復(fù)合索引，按照索引列在復(fù)合索引中出現(xiàn)的順序來(lái)依次寫(xiě)where條件；
6、使用多表連接時(shí)，在from子句中，將記錄數(shù)少的表放在后面，可提高執(zhí)行效率；
7、避免使用not in
not in 是效率極低的寫(xiě)法，盡量使用minus或外連接加以替代
典型實(shí)例：
1) select col1 from tab1 where col1 not in (select col1 from tab2);
2) select sum(col2) from tab1 where col1 not in (select col1 from tab2);
替代方案
select col1 from tab1 minus select col1 from tab2;
select sum(a.col2) from tab1 a, tab2 b
where a.col1=b.col2(+) and b.col1 is null;
8、多表查詢時(shí)，如果其中一個(gè)表的記錄數(shù)量明顯大于其他表，則可以先對(duì)此表進(jìn)行查詢后，再與其他小表進(jìn)行表連接。
典型實(shí)例：
select a.plan_code, b.dno, c,tno, sum(a.tot_modal_prem),
from prem_info a, dept_ref b, plan_type c
where substr(a.deptno,1,7) = substr(b.deptno,1,7)
and a.plan_code = c.plan_code
group by b.dno, c.tno, a.plan_code;
替代方案：
select b.dno, c.tno, a.plan_code, a.tot_amount
from (select plan_code, deptno, sum(tot_modal_prem) tot_amount
from prem_info
group by deptno, plan_code) a
dept_ref b,
plan_type c
where substr(a.deptno,1,7) = substr(b.deptno,1,7)
and a.plan_code = c.plan_code
group by b.dno, c.tno, a.plan_code;
小結(jié)：
由于prem_info表的記錄數(shù)遠(yuǎn)遠(yuǎn)大于dept_ref表和plan_type表中的記錄數(shù)， 所以首先從prem_info表中查詢需要的記錄，此時(shí)記錄數(shù)已經(jīng)被大量縮小，然后再和其他兩個(gè)表連接，速度會(huì)得到很大改善！
9、查詢數(shù)量較大時(shí)，使用表連接代替IN，EXISTS，NOT IN，NOT EXISTS等。
典型實(shí)例：
a、使用IN：
select sum(col2) from tab1 where col1 in (select col1 from tab2);
使用EXISTS:：
select sum(col2) from tab1 a
where exists ( select * from tab2 where col1=a.col1);
b、使用NOT IN：
select sum(col2) from tab1 where col1 not in (select col1 from tab2);
使用NOT EXISTS：
select sum(col2) from tab1 a
where not exists ( select * from tab2 where col1=a.col1);
替代方案：
a、使用連接：
select sum(a.col2) from tab1 a,tab2 b where a.col1=b.col2;
b、使用外連接：
select sum(a.col2) from tab1 a,tab2 b
where a.col1=b.col2(+) and b.col1 is null;