SQL最佳化技巧(Oracle)，sql最佳化技巧oracle

SQL最佳化技巧(Oracle)，sql最佳化技巧oracle

SQL最佳化技巧(1):Where子句中的串連順序：oracle採用自下而 上的順序解析where子句，根據這個原理，表 之間的串連必須寫在其他where條件之前，那些可以過濾掉大量記錄的條件必須寫在where子句的末尾。例如低效：select * from report_sale_account ewhere hsje>5000and dzxl = ‘000001’and 25<(select count(*) from report_sale_accountwhere code=e.code);高效：select * from report_sale_account ewhere 25<(select count (*)     from report_sale_account      where code=e.code)and hsje>5000and 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>=90UNION ALLSelect name,score,'良' 成績等級 From score Where score>=80 and score<90UNION ALLSelect name,score,'中' 成績等級 From score  Where score>=60 and score<80UNION ALLSelect name,score,'差' 成績等級 From score   Where score<60;高效的做法：Select name,       score,       decode(sign(score - 90),              -1,              decode(sign(score - 80),                     -1,                     decode(sign(score - 60), -1, '差', '中'),                     '良'),              '優') as 成績等級  from score;from table;索引的引用（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)返回-1SQL最佳化方法第一個方法：利用串連符串連多個欄位。第二個方法：取消重複的行。第三個方法：勤用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的記錄. 

 

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。