一、 關于索引的知識
要寫出運行效率高的sql，需要對索引的機制有一定了解，下面對索引的基本知識做一介紹。
1、 索引的優點和局限
索引可以提高查詢的效率，但會降低dml操作的效率。
所以建立索引時需要權衡。對于dml操作比較頻繁的表，索引的個數不宜太多。
2、 什么樣的列需要建索引？
經常用于查詢、排序和分組的列（即經常在where、order或group by子句中出現的列）。
3、 主鍵索引和復合索引
對于一張表的主鍵，系統會自動為其建立索引。
如果一張表的幾列經常同時作為查詢條件，可為其建立復合索引。
4、 建立索引的語句
create index i_staff on staff (empno);
create index i_agent on agent (empno, start_date);
5、 刪除索引的語句
drop index I_staff;
drop index I_agent;
6、 查詢索引的語句
法一：利用數據字典
表一：all_indexes 查看一張表有哪些索引以及索引狀態是否有效
主要字段： 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
由此可見，agent表中有一個復合索引（empno, start_date ）
法二：利用toad工具
toad用戶界面比sql*plus友好，并且功能強大。你可以在toad編輯器中鍵入表名，按F4，便可見到這張表的表結構以及所有索引列等基本信息。
7、 索引的一些特點
1): 不同值較多的列上可建立檢索，不同值少的列上則不要建。比如在雇員表的“性別”列上只有“男”與“女”兩個不同值，因此就沒必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。
2): 如果在索引列上加表達式，則索引不能正常使用
例如：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’;
以上都是不正確的寫法
3): where子句中如果使用in、or、like、!=，均會導致索引不能正常使用
例如：select * from b where b1=30 or b1=40;
4): 使用復合索引進行查詢時必須使用前置列
例如表a上有一個復合索引（c1,c2,c3）,則c1為其前置列
如果用c1或c1+c2或c1+c2+c3為條件進行查詢，則該復合索引可以發揮作用，反之，用c2或c3或c2+c3進行查詢，則該索引不能起作用。
二. 書寫sql注意事項：
1、 避免給sql語句中引用的索引列添加表達式：
典型實例：
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中有兩個重要字段，pol_info中的undwrt_date和prem_info中的payment_date，這兩個日期是帶時分秒的，所以經常有同事用to_char 來查詢某一時間段的數據。
例如：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、!=
典型實例：
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’;
然后做一次加法運算；或者直接用存儲過程來實現；
小結：
對字段使用了 ‘in，or，like’ 做條件、對字段使用了不等號 ‘!=’，均會使索引失效；如果不產生大量重復值，可以考慮把子句拆開；拆開的子句中應該包含索引，或者使用union連結符代替。另一種方式是使用存儲過程，它使SQL變得更加靈活和高效。
3、 建立適當的索引
曾經接過開發的一個統計sql， select … from tablea where cola=… and …
運行效率非常慢，經查tablea數據量巨大，再查all_ind_columns，發現cola是tablea的一個復合索引中的一列，但不是前置列。象這種情況，就需要與開發商量，是否針對cola建一個索引。
4、 like和substr
對于‘like’和‘substr’，其效率并沒有多大分別。但是，當所搜索的值不存在時，使用‘like’的速度明顯大于‘substr’。
所以：select * from a where substr(a1,1,4) = '5378' 可以用like替代
select * from a where a1 like ‘5378%’;
5、 寫where條件時，有索引字段的判斷在前，其它字段的判斷在后；如果where條件中用到復合索引，按照索引列在復合索引中出現的順序來依次寫where條件；
6、使用多表連接時，在from子句中，將記錄數少的表放在后面，可提高執行效率；
7、避免使用not in
not in 是效率極低的寫法，盡量使用minus或外連接加以替代
典型實例：
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、多表查詢時，如果其中一個表的記錄數量明顯大于其他表，則可以先對此表進行查詢后，再與其他小表進行表連接。
典型實例：
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;
小結：
由于prem_info表的記錄數遠遠大于dept_ref表和plan_type表中的記錄數， 所以首先從prem_info表中查詢需要的記錄，此時記錄數已經被大量縮小，然后再和其他兩個表連接，速度會得到很大改善！
9、查詢數量較大時，使用表連接代替IN，EXISTS，NOT IN，NOT EXISTS等。
典型實例：
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;