標籤：
SQL最佳化技巧(1):
Where子句中的串連順序：oracle採用自下而 上的順序解析where子句，根據這個原理，表 之間的串連必須寫在其他where條件之前，那些可以過濾掉大量記錄的條件
必須寫在where子句的末尾。
例如
低效：select * from report_sale_account e
where hsje>5000
and dzxl = ‘000001’
and 25<(select count(*)
from report_sale_account
where code=e.code);
高效：select * from report_sale_account e
where 25<(select count (*)
from report_sale_account
where code=e.code)
and hsje>5000
and dzxl = ‘000001’;
SQL的最佳化技巧 （2）
刪除全表時，用truncate 替代 delete,同時注意truncate只能在刪除全表時適用，因為truncate是ddl而不是dml。
例如刪除掉一個100萬行的資料。
Truncate table report_sale_account;
比delete from report_sale_account;至少快1000倍。
SQL的最佳化技巧 （3）
盡量多使用commit：
只要有可能就在程式中對每個delete、insert、update操作盡量多使用commit,這樣系統效能會因為commit所釋放的資源而大大提高。
SQL的最佳化技巧 （4）
用exists替代in ，可以提高查詢的效率。
低效
SELECT * FROM REPORT_SALE_ACCOUNT
WHERE COM_CODE
NOT IN (SELECT CODE
FROM BASEINFO_GOODS
WHERE DZXL = ‘000001’)
高效
SELECT * FROM REPORT_SALE_ACCOUNT
WHERE NOT EXISTS
(SELECT CODE FROM BASEINFO_GOODS
WHERE CODE = REPORT_SALE_ACCOUNT.COM_CODE
AND DZXL = ‘000001‘)
SQL的最佳化技巧 （5）
最佳化group by
提高group by語句的效率，可以將不需要的記錄在group by之前過濾掉。
SQL的最佳化技巧 （5）
例如：
低效：select dzxl,
avg(hsje)
from report_sale_account
group by dzxl
having dzxl = ‘000001’
or dzxl =’000002’;
高效： select dzxl,
avg(hsje)
from report_sale_account
where dzxl = ‘000001’
or dzxl =’000002’
group by dzxl;
避免使用HAVING子句，HAVING只會在檢索出所有記錄之後才對結果集進行過濾，這個處理需要排序、統計等操作。如果能通過WHERE子句限制記錄的數目，那就能減少這方面的開銷。
SQL的最佳化技巧 （6）
有條件的使用union-all 替代 union：這樣做效率會提高3到5倍。
SQL的最佳化技巧 （7）
在含有子查詢的SQL語句中，要特別注意減少對錶的查詢
例如：
低效
SELECT SUM(HSJE) FROM REPORT_SALE_ACCOUNT
WHERE DZXL = (SELECT DZXL
FROM BASEINFO_GOODS
WHERE CODE = ‘0001’)
AND PP =(SELECT PP
FROM BASEINFO_GOODS
WHERE CODE = ‘0001’)
高效
SELECT SUM(HSJE) FROM REPORT_SALE_ACCOUNT
WHERE (DZXL, PP) = (SELECT DZXL,PP
FROM BASEINFO_GOODS
WHERE CODE = ‘0001’)
Update多個Column同樣適用於以上的例子。
SQL的最佳化技巧 （8）
SELECT子句中避免使用’*’
當你想在SELECT子句中列出所有的COLUMN時，使用動態SQL列引用‘*‘是一個方便的方法，不幸的是，這是一個非常低效的方法，實際上，ORACLE在解析的過程中，會將‘*‘依次轉換成所有的列
名，這個工作是通過查詢資料字典完成的，這意味著將耗費更多的時間。
一些函數的提示
雖然在SQL中盲目引用函數會使效能降低，但如果正確使用合適的函數不僅會使SQL可讀性加強，並且能對SQL效能得到提高，使複雜的查詢能很方便地實現
低效的做法：
Select name,score,‘優‘ 成績等級 From score Where score>=90
UNION ALL
Select name,score,‘良‘ 成績等級 From score Where score>=80 and score<90
UNION ALL
Select name,score,‘中‘ 成績等級 From score Where score>=60 and score<80
UNION ALL
Select name,score,‘差‘ 成績等級 From score Where score<60;
高效的做法1_decode函數：
Select name,
score,
decode(sign(score - 90),
-1,
decode(sign(score - 80),
-1,
decode(sign(score - 60), -1, ‘差‘, ‘中‘),
‘良‘),
‘優‘) as 成績等級
from score;
高效做法2_case wehn:
SELECT stuname,
score,
(CASE sign(score - 90)
WHEN -1 THEN
CASE sign(score - 80)
WHEN -1 THEN
CASE sign(score - 60)
WHEN -1 THEN
‘差‘
ELSE
‘中‘
END ELSE ‘良‘ END
ELSE ‘優‘ END) grade
FROM student;
索引的引用（1）
當插入的資料為資料表中的記錄數量的10%以上，首先需要刪除該表的索引來提高資料的插入效率，當資料插入後，再建立索引
索引的引用（2）
避免在索引列上使用函數或計算，在where子句中，如果索引是函數的一部分，最佳化器將不再使用索引而使用全表掃描。如：
低效：
select * from report_sale_account
where hsjj*10 >1000;
高效：
select * from report_sale_account
where hsjj >1000/10;
索引的引用（3）
盡量避免在索引列上使用not和 “!=”和“<>”，索引只能告訴什麼存在於表中，而不能告訴什麼不存在於表中，當資料庫遇到not 和 “!=” 和“<>”時，就會停止使用索引而去執行全表掃描。
索引的引用（4）
請務必注意，檢索中不要對索引列進行處理，如：TRIM，TO_DATE，類型轉換等操作，破壞索引，使用全表掃描，影響SQL執行效率
索引的引用（5）
避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以為空白的列，ORACLE將無法使用該索引
對於單列索引，如果列包含空值，索引中將不存在此記錄；
對於複合索引，如果每個列都為空白，索引中同樣不存在此記錄。如果至少有一個列不為空白，則記錄存在於索引中
因為空白值不存在於索引列中，所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引
索引的引用（6）
索引列上“>=”代替“>”
低效：select * from report_sale_account where hsjj > 10;
高效：select * from report_sale_account where hajj >=10.000000000000001;
比較大小函數 sign
函數文法：
sign(n)
函數說明：
取數字n的符號,大於0返回1,小於0返回-1,等於0返回0
樣本：
一、select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;
SIGN(100) SIGN(-100) SIGN(0)
———- ———- ———-
1 -1 0
二、a=10，b=20
則sign(a-b)返回-1
SQL最佳化方法
第一個方法：利用串連符串連多個欄位。
第二個方法：取消重複的行。
第三個方法：勤用WHERE語句。
第四個方法：靈活使用COUNT函數
第五個方法：只查詢時必須的欄位。
第六個方法：合理處理NULL欄位。
第七個方法：多多利用模糊查詢。
第八個方法：慎用Like等萬用字元。
第九個方法：利用注釋提高查詢語句的可讀性。
第十個方法：必要的時候，限制使用者所使用的行。
1. SELECT子句中避免使用 “*”
當你想在SELECT子句中列出所有的COLUMN時，使用動態SQL列引用‘*’是一個方便的方法。不幸的是，這是一個非常低效的方法。 實際上，ORACLE在解析的過程中, 會將“*” 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味著將耗費更多的時間。
2.使用DECODE函數來減少處理時間
使用DECODE函數可以避免重複掃描相同記錄或重複串連相同的表. 例如:
Sql代碼
SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND ENAME LIKE ‘SMITH%’;
SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%’;
你可以用DECODE函數高效地得到相同結果:
Sql代碼
SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,
COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,
SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,
SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL
FROM EMP WHERE ENAME LIKE ‘SMITH%’;
類似的，DECODE函數也可以運用於GROUP BY 和ORDER BY子句中。
decode含義解釋：
decode(條件,值1,翻譯值1,值2,翻譯值2,...值n,翻譯值n,預設值)
3.重複資料刪除記錄
最高效的重複資料刪除記錄方法 ( 因為使用了ROWID)
Sql代碼
DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);
4. 用TRUNCATE替代DELETE
當刪除表中的記錄時，在通常情況下，復原段(rollback segments ) 用來存放可以被恢複的資訊，如果你沒有COMMIT事務，ORACLE會將資料恢複到刪除之前的狀態(準確地說是恢複到執行刪除命令之前的狀況)，而當運用TRUNCATE時, 復原段不再存放任何可被恢複的資訊.當命令運行後,資料不能被恢複.因此很少的資源被調用,執行時間也會很短.
5.計算記錄條數
和一般的觀點相反, count(*) 比count(1)稍快 ，當然如果可以通過索引檢索，對索引列的計數仍舊是最快的. 例如 COUNT(EMPNO)
6.用Where子句替換HAVING子句
避免使用HAVING子句，HAVING 只會在檢索出所有記錄之後才對結果集進行過濾，這個處理需要排序、總計等操作，如果能通過WHERE子句限制記錄的數目，那就能減少這方面的開銷, 例如:
Sql代碼
--低效
SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != ‘SYDNEY’ AND REGION != ‘PERTH’
--高效
SELECT REGION，AVG(LOG_SIZE) FROM LOCATION WHERE REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION
7. 用EXISTS替代IN
在許多基於基礎資料表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯結.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.
Sql代碼
--低效
SELECT * FROM EMP WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB’)
--高效:
SELECT * FROM EMP WHERE EMPNO > 0 AND EXISTS (SELECT ‘X’ FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB’)
8.用NOT EXISTS替代NOT IN
在子查詢中，NOT IN子句將執行一個內部的排序和合并. 無論在哪種情況下，NOT IN都是最低效的 (因為它對子查詢中的表執行了一個全表遍曆). 為了避免使用NOT IN，我們可以把它改寫成外串連(Outer Joins)或NOT EXISTS. 例如:
SELECT …FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);
Sql代碼
--為了提高效率改寫為: (方法一: 高效)
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’
-- (方法二: 最高效)
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’ FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);
9.用EXISTS替換DISTINCT
當提交一個包含一對多表資訊(比如部門表和僱員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換
例如:
Sql代碼
--低效:
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
--高效:
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
--EXISTS 使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足後,立刻返回結果.
10. 用索引提高效率
索引是表的一個概念部分，用來提高檢索資料的效率，實際上ORACLE使用了一個複雜的自平衡B-tree結構，通常通過索引查詢資料比全表掃描要快，當ORACLE找出執行查詢和Update語句的最佳路徑時， ORACLE最佳化器將使用索引， 同樣在連接多個表時使用索引也可以提高效率，另一個使用索引的好處是，它提供了主鍵(primary key)的唯一性驗證，除了那些LONG或LONG RAW資料類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效. 當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率，雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來儲存，也需要定期維護，每當有記錄在表中增減或索引列被修改時，索引本身也會被修改，這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O， 因為索引需要額外的儲存空間和處理，那些不必要的索引反而會使查詢反應時間變慢。
註：週期性重構索引是有必要的.
11. 避免在索引列上使用計算
WHERE子句中，如果索引列是函數的一部分，最佳化器將不使用索引而使用全表掃描. 舉例：
Sql代碼
--低效：
SELECT …FROM DEPT WHERE SAL * 12 > 25000;
--高效:
SELECT … FROM DEPT WHERE SAL > 25000/12;
12. 用>=替代>
Sql代碼
--如果DEPTNO上有一個索引
--高效：
SELECT * FROM EMP WHERE DEPTNO >=4
--低效：
SELECT * FROM EMP WHERE DEPTNO >3
兩者的區別在於, 前者DBMS將直接跳到第一個DEPT等於4的記錄而後者將首先定位到DEPTNO=3的記錄並且向前掃描到第一個DEPT大於3的記錄.
說明:本人於ITEYE建立於2013年,現轉移到CSDN
SQL最佳化技巧(Oracle)
