oracle sql 語句性能優化總結

Sql 優化:

當Oracle資料庫拿到SQL語句時，其會根據查詢優化器分析該語句，并根據分析結果生成查詢執行計劃。

也就是說，資料庫是執行的查詢計劃，而不是Sql語句。

查詢優化器有rule-based-optimizer(基于規則的查詢優化器) 和Cost-Based-optimizer(基于成本的查詢優化器)。

其中基于規則的查詢優化器在10g版本中消失。

對于規則查詢，其最後查詢的是全表掃描。而CBO則會根據統計資訊進行最後的選擇。

1、先執行From ->Where ->Group By->Order By

2、執行From 字句是從右往左進行執行。是以必須選擇記錄條數最少的表放在右邊。這是為什麼呢？　　

3、對于Where字句其執行順序是從後向前執行、是以可以過濾最大數量記錄的條件必須寫在Where子句的末尾，而對于多表之間的連接配接，則寫在之前。

因為這樣進行連接配接時，可以去掉大多不重複的項。　　

1. SELECT子句中避免使用()ORACLE在解析的過程中, 會将’’ 依次轉換成所有的列名, 這個工作是通過查詢資料字典完成的, 這意味着将耗費更多的時間

5、索引失效的情況:

　① Not Null/Null 如果某列建立索引,當進行Select * from emp where depto is not null/is null。 則會是索引失效。

　② 索引列上不要使用函數,SELECT Col FROM tbl WHERE substr(name ,1 ,3 ) = ‘ABC’

或者SELECT Col FROM tbl WHERE name LIKE ‘%ABC%’ 而SELECT Col FROM tbl WHERE name LIKE ‘ABC%’ 會使用索引。

　③ 索引列上不能進行計算SELECT Col FROM tbl WHERE col / 10 > 10 則會使索引失效，應該改成

SELECT Col FROM tbl WHERE col > 10 * 10

　④ 索引列上不要使用NOT （ != 、 <> ）如:SELECT Col FROM tbl WHERE col ! = 10

應該 改成：SELECT Col FROM tbl WHERE col > 10 OR col < 10 。

6、用UNION替換OR(适用于索引列)

　 union:是将兩個查詢的結果集進行追加在一起，它不會引起列的變化。 由于是追加操作，需要兩個結果集的列數應該是相關的，

并且相應列的資料類型也應該相當的。union 傳回兩個結果集，同時将兩個結果集重複的項進行消除。 如果不進行消除，用UNOIN ALL.

通常情況下, 用UNION替換WHERE子句中的OR将會起到較好的效果. 對索引列使用OR将造成全表掃描. 注意, 以上規則隻針對多個索引列有效.

如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.

高效:

　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE LOC_ID = 
　　UNION
　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE REGION = “MELBOURNE”
           

　　低效:

　　SELECT LOC_ID , LOC_DESC , REGION
　　FROM LOCATION
　　WHERE LOC_ID =  OR REGION = “MELBOURNE”
　　//如果你堅持要用OR, 那就需要傳回記錄最少的索引列寫在最前面.
           

1. 用EXISTS替代IN、用NOT EXISTS替代NOT IN

   在許多基于基礎表的查詢中, 為了滿足一個條件, 往往需要對另一個表進行聯接. 在這種情況下, 使用EXISTS(或NOT EXISTS)通常将提高查詢的效率.

   在子查詢中, NOT IN子句将執行一個内部的排序和合并. 無論在哪種情況下, NOT IN都是最低效的(因為它對子查詢中的表執行了一個全表周遊).

   為了避免使用NOT IN, 我們可以把它改寫成外連接配接(Outer Joins)或NOT EXISTS.

例子：

高效:

低效:

8，優化GROUP BY:

提高GROUP BY 語句的效率,可以通過将不需要的記錄在GROUP BY之前過濾掉。

下面兩個查詢傳回相同結果但明顯第二個效率更高。

低效:

SELECT JOB,AVG(AGE) FROM TEMP 
GROUP BY JOB HAVING JOB = 'STUDENT' OR JOB = 'MANAGER';
           

高效:

SELECT JOB,AVG(AGE) FROM EMP 
WHERE JOB = 'STUDENT' OR JOB = 'MANAGER' GROUP BY JOB;
           

9、删除重複記錄：

DELETE FROM music m WHERE ROWID > 
(SELECT MIN(ROWID) FROM music u WHERE u.music_id = m.music_id)//(有待測試)