SQL優化筆記

1．索引的使用：
(1).當插入的資料為資料表中的記錄數量的10%以上，首先需要刪除該表的索引來提高資料的插入效率，當資料插入後，再建立索引。
(2).避免在索引列上使用函式或計算，在where子句中，如果索引是函式的一部分，優化器將不再使用索引而使用全表掃描。如：
低效：select * from dept where sal*12 >2500;
高效：select * from dept where sal>2500/12;
(3).避免在索引列上使用not和“!=”，索引只能告訴什麼存在於表中，而不能告訴什麼不存在於表中，當資料庫遇到not 和“!=”時，就會停止使用索引而去執行全表掃描。
(4).索引列上>=代替>
低效：select * from emp where deptno > 3
高效：select * from emp where deptno >=4
兩者的區別在於，前者dbms將直接跳到第一個deptno等於4的記錄，而後者將首先定位到deptno等於3的記錄並且向前掃描到第一個deptno大於3的。
(5).非要對一個使用函式的列啟用索引，基於函式的索引是一個較好的方案。

2. 遊標的使用：
當在海量的資料表中進行資料的刪除、更新、插入操作時，用遊標處理的效率是最慢的，但是遊標又是必不可少的，所以正確使用遊標十分重要：
(1). 在資料抽取的源表中使用時間戳，這樣每天的維表資料維護只針對更新日期為最新時間的資料來進行，大大減少需要維護的資料記錄數。
(2). 在insert和update維表時都加上一個條件來過濾維表中已經存在的記錄，例如：
insert into dim_customer select * from ods_customer where ods_customer.code not exists (dim_customer.code)
ods_customer為資料來源表。dim_customer為維表。
(3). 使用顯式的遊標，因為隱式的遊標將會執行兩次操作，第一次檢索記錄，第二次檢查too many rows這個exception,而顯式遊標不執行第二次操作。

3． 據抽取和上載時的sql優化：
(1). Where 子句中的連線順序：
oracle採用自下而上的順序解析where子句，根據這個原理，表之間的連線必須寫在其他where條件之前，那些可以過濾掉大量記錄的條件必須寫在where子句的末尾。如：
低效：select * from emp e where sal>5000 and job = ‘manager’ and 25 高效：select * from emp e where 255000 and job=’manager’;
(2). 刪除全表時，用truncate 替代 delete,同時注意truncate只能在刪除全表時適用，因為truncate是ddl而不是dml。
(3). 儘量多使用commit
只要有可能就在程式中對每個delete,insert,update操作儘量多使用commit,這樣系統效能會因為commit所釋放的資源而大大提高。
(4). 用exists替代in ，可以提高查詢的效率。
(5). 用not exists 替代 not in
(6). 優化group by
提高group by語句的效率，可以將不需要的記錄在group by之前過濾掉。如：
低效：select job, avg(sal) from emp group by job having job = ‘president’ or job=’manager’;
高效： select job, avg(sal) from emp having job=’president’ or job=’manager’ group by job;
(7). 有條件的使用union-all 替代 union：這樣做排序就不必要了，效率會提高3到5倍。
(8). 分離表和索引
總是將你的表和索引建立在不同的表空間內，決不要將不屬於oracle內部系統的物件存放到system表空間內。同時確保資料表空間和索引表空間置於不同的硬碟控制卡控制的硬碟上。
更多詳情

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29212814/viewspace-1108211/，如需轉載，請註明出處，否則將追究法律責任。