高效率Oracle SQL語句

1、Where子句中的連線順序：
ORACLE採用自下而上的順序解析WHERE子句。
根據這個原理，表之間的連線必須寫在其他WHERE條件之前， 那些可以過濾掉最大數量記錄的條件必須寫在WHERE子句的末尾。

舉例：
(低效)
select ... from table1 t1 where t1.sal > 300 and t1.jobtype = '0001' and 20 < (select count(*) from table1 t2 where t2.pno = t1.tno;

(高效)
select ... from table1 t1 where 20 < (select count(*) from table1 t2 where t2.pno = t1.tno and t1.sal > 300 and t1.jobtype = '0001';

2、Select子句中避免使用 “ * ”：
當你想在select子句中列出所有的column時,使用動態SQL列引用 ‘*' 是一個方便的方法。
不幸的是，這是一個非常低效的方法。
實際上，ORACLE在解析的過程中，會將 '*' 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間。


3、減少訪問資料庫的次數：
當執行每條SQL語句時，ORACLE在內部執行了許多工作:
解析SQL語句、估算索引的利用率、繫結變數、讀資料塊等等。
由此可見，減少訪問資料庫的次數，就能實際上減少ORACLE的工作量。

舉例：
題目——我要查詢編號為0001、0002學生的資訊。
（低效）
select name,age,gender,address from t_student where id = '0001';
select name,age,gender,address from t_student where id = '0002';
（高效）
select a.name,a.age,a.gender,a.address,b.name,b.age,b.gender,b.address from t_student a,t_student b where a.id = '0001' and b.id = '0002';

4、使用Decode函式來減少處理時間：
使用DECODE函式可以避免重複掃描相同記錄或重複連線相同的表。

舉例：
（低效）
select count(*), sum(banace) from table1 where dept_id = '0001' and name like 'anger%';
select count(*), sum(banace) from table1 where dept_id = '0002' and name like 'anger%';
（高效）
select  count(decode(dept_id,'0001','XYZ',null)) count_01,count(decode(dept_id,'0002','XYZ',null)) count_02,
sum(decode(dept_id,'0001',dept_id,null)) sum_01,sum(decode(dept_id,'0002',dept_id,null)) sum_02
  from table1
  where name like 'anger%';

5、整合簡單,無關聯的資料庫訪問：
如果你有幾個簡單的資料庫查詢語句，你可以把它們整合到一個查詢中(即使它們之間沒有關係)

舉例：
（低效）
select name from table1 where id = '0001';
select name from table2 where id = '0001';
select name from table3 where id = '0001';
（高效）
select t1.name, t2.name, t3.name
    from table1 t1, table2 t2, table3 t3
    where t1.id(+) = '0001' and t2.id(+) = '0001' and t3.id(+) = '0001'
【注：上面例子雖然高效，但是可讀性差，需要量情而定啊！】

6、刪除重複記錄：
最高效的刪除重複記錄方法 ( 因為使用了ROWID)

舉例：
delete from table1 t1
  where t1.rowid > (select min(t2.rowid) from table1 t2 where t1.id = t2.id);

7、儘量不要使用having子句，可以考慮用where替換。
having只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序，總計等操作。
如果能通過where子句限制記錄的數目，那就能減少這方面的開銷。

8、儘量用表的別名：
當在SQL語句中連線多個表時，請使用表的別名並把別名字首於每個Column上。
這樣一來，就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤。

9、用exists替代in（發現好多程式設計師不知道這個怎麼用）：
在許多基於基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯接。
在這種情況下，使用exists(或not exists)通常將提高查詢的效率。

舉例：
（低效）
select ... from table1 t1 where t1.id > 10 and pno in (select no from table2 where name like 'www%');
（高效）
select ... from table1 t1 where t1.id > 10 and exists (select 1 from table2 t2 where t1.pno = t2.no and name like 'www%');

10、用not exists替代not in：
在子查詢中，not in子句將執行一個內部的排序和合並。
無論在哪種情況下，not in都是最低效的 (因為它對子查詢中的表執行了一個全表遍歷)。
為了避免使用not in，我們可以把它改寫成外連線(Outer Joins)或not exists。


11、用exists替換distinct：
當提交一個包含一對多表資訊的查詢時,避免在select子句中使用distinct. 一般可以考慮用exists替換

舉例：
（低效）
select distinct d.dept_no, d.dept_name from t_dept d, t_emp e where d.dept_no = e.dept_no;
（高效）
select d.dept_no, d.dept_name from t_dept d where exists (select 1 from t_emp where d.dept_no = e.dept_no);

exists使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回結果.

12、用表連線替換exists：
通常來說，採用表連線的方式比exists更有效率。

舉例：
（低效）
select ename from emp e where exists (select 1 from dept where dept_no = e.dept_no and dept_cat = 'W');
SELECT ENAME
（高效）
select ename from dept d, emp e where e.dept_no = d.dept_no and dept_cat = 'W';

13、避免在索引列上使用is null和is not null
避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引。
對於單列索引，如果列包含空值，索引中將不存在此記錄；
對於複合索引，如果每個列都為空，索引中同樣不存在此記錄；
如果至少有一個列不為空，則記錄存在於索引中。

舉例:
如果唯一性索引建立在表的A列和B列上, 並且表中存在一條記錄的A,B值為(123,null)，
ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入)，
然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等於空。
因此你可以插入1000 條具有相同鍵值的記錄,當然它們都是空!
因為空值不存在於索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引。

14、最好把複雜的sql，去看下它的執行計劃，這樣有利於你分析知道自己的sql效率如何。

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