oracle查詢效率積累

我們要做到不但會寫 SQL,還要做到寫出效能優良的SQL,以下為筆者學習、摘錄、並匯總部分資料與大家分享！

（1）      選擇最有效率的表名順序(只在基於規則的最佳化器中有效)：

ORACLE的解析器按照從右至左的順序處理FROM子句中的表名，FROM子句中寫在最後的表(基礎資料表driving table)將 被最先處理，在FROM子句中包含多個表的情況下,你必須選擇記錄條數最少的表作為基礎資料表。如果有3個以上的表串連查詢, 那就需要選擇交叉表(intersection table)作為基礎資料表, 交叉表是指那個被其他表所引用的表.

（2）      WHERE子句中的串連順序．：

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

（3）      SELECT子句中避免使用 ‘ * ‘：

ORACLE在解析的過程中, 會將'*' 依次轉換成所有的列名, 這個工作是通過查詢資料字典完 成的, 這 意味著將耗費更多的時間

（4）      減少訪問資料庫的次數：

ORACLE在內部執行了許多工作: 解析SQL語句, 估算索引的利用率, 綁定變數 , 讀資料區塊等；

（5）      在SQL*Plus , SQL*Forms和Pro*C中重新設定ARRAYSIZE參數, 可以增加每次資料庫訪問的檢索 資料量 ,建 議值為200

（6）      使用DECODE函數來減少處理時間：

使用DECODE函數可以避免重複掃描相同記錄或重複串連相同的表.

（7）      整合簡單,無關聯的資料庫訪問：

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

（8）      重複資料刪除記錄：

最高效的重複資料刪除記錄方法 ( 因為使用了ROWID)例子：

DELETEFROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

（9）      用TRUNCATE替代DELETE：

當刪除表中的記錄時,在通常情況下, 復原段(rollbacksegments ) 用來存放可以被恢複的資訊. 如果你沒有COMMIT事務,ORACLE會將資料恢複到刪除 之前的狀態(準確地說是恢複到執行刪除命令之前的狀況) 而當運用TRUNCATE時, 復原段不再存放任何可被恢複的資訊.當 命令運行後,資料不能被恢複.因此很少的資源被調用,執行時間也會很短.(譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)

（10） 盡量多使用COMMIT：

只要有可能,在程式中盡量多使用COMMIT, 這樣程式的效能得到提高,需求也會因為COMMIT所釋放的資源而減少:
COMMIT所釋放的資源:
a. 復原段上用於恢複資料的資訊.
b. 被程式語句獲得的鎖
c. redo log buffer 中的空間
d. ORACLE為管 理上述3種 資源中的內部花費

（11） 用Where子句替換HAVING子句：

避免使用HAVING子句, HAVING 只會在檢索出所有記錄之後才對結果集進行過濾. 這個處理需要排序,總計等操作. 如果能通過WHERE子句限制記錄的數目,那就能減少這方面的開銷. (非oracle中)on、where、having這三個都可以加條件的 子句中，on是最先執行，where次之，having最後，因為on是先把不合格記錄過濾後才進行統計，它就可以減少中間運算要處理的資料，按理說應該速度是最快的，where也應該比having快點的，因為它過濾數 據後才進行sum，在兩個表聯結時才用on的，所以在一個表的時候，就剩下where跟having比較了。在這單表查詢統計的情況下，如果要過濾的條件沒有涉及到要計算欄位，那它們的結果是一樣的，只是where可以使用rushmore技術，而having就不能，在速度上後者 要慢如果要涉及到計算的欄位，就表示在沒計算之前，這個欄位的值是不確定的，根據上篇寫的工作流程，where的作用時間是在計算之前就 完成的，而having就是在計算後才起作用的，所以在這種情況下，兩者的結果會不同。在多表聯結查詢時，on比where更早起作用。系統首先根據各個表之間的聯結條件，把多個 表合成一個暫存資料表後，再由where進行過濾，然後再計算，計算完後再由having進行過濾。由此可見，要想過濾條件起到正確的作用，首先要明白這個條件應該在什麼時候起作用，然後再決定放在那裡

（12） 減少對錶的查詢：

在含有子查詢的SQL語句中,要特別注意減少對錶的查詢.例子：

    SELECT TAB_NAME FROM TABLES WHERE(TAB_NAME,DB_VER) = ( SELECT

TAB_NAME,DB_VERFROM TAB_COLUMNS WHERE VERSION = 604)

（13） 通過內建函式提高SQL效率.：

複雜的SQL往往犧牲了執行效率. 能夠掌握上面的運用函數解決問題的方法在實際工作中是非常有意義的

（14） 使用表的別名(Alias)：

當在SQL語句中串連多個表時, 請使用表的別名並把別名首碼於每個Column上.這樣一來,就可以減少解析的時間並減少那些由Column歧義引起的語法錯誤.

（15） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在許多基於基礎資料表的查詢中,為了滿足一個條件,往往需要對另一個表進行聯結.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率. 在子查詢中,NOT IN子句將執行一個內部的排序和合并. 無論在哪種情況下,NOT IN都是最低效的 (因為它對子查詢中的表執行了一個 全表遍曆). 為了避免使用NOT IN ,我們可以把它改寫成外串連(Outer Joins)或NOT EXISTS.

例子：

（高效）SELECT * FROM EMP (基礎資料表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC =‘MELB')

(低效)SELECT * FROM EMP (基礎資料表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

（16） 識別'低效執行'的SQL語句：

雖然目前各種關於SQL最佳化的圖形化工具層出不窮,但是寫出自己的SQL工具來解決問題始終是一個最好的方法：

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

（17） 用索引提高效率：

索引是表的一個概念部分,用來提高檢索資料的效率，ORACLE使用了一個複雜的自平衡B-tree結構. 通常,通過索引查詢資料比全表掃描要快. 當ORACLE找出執行查詢和Update語句的最佳路徑時, ORACLE最佳化器將使用索引. 同樣在連接多個表時使用索引也可以提高效率. 另一個使用索引的好處是,它提供了主鍵(primarykey)的唯一 性驗證.。 那些LONG或LONG RAW資料類型, 你可以索引幾乎所有的列. 通常, 在大型表中使用索引特別有效.當然,你也會發現, 在掃描小表時,使用索引同樣能提高效率. 雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價. 索引需要空間來儲存,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改. 這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁碟I/O . 因為索引需要額外的儲存空間和處理,那些不必要的索引反而會使查詢反應時間變慢.。週期性重構索引是有必要的.：

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

（18） 用EXISTS替換DISTINCT：

當提交一個包含一對多表資訊(比如部門表和僱員表)的查詢時,避免在SELECT子句中使用DISTINCT. 一般可以考慮用EXIST替換, EXISTS 使查詢更為迅速,因為RDBMS核心模組將在子查詢的條件一旦滿足 後,立刻 返回結果. 例子：

      (低效):
SELECT DISTINCTDEPT_NO,DEPT_NAME FROM DEPT D , EMP E

WHERE D.DEPT_NO = E.DEPT_NO
(高效):
SELECT DEPT_NO,DEPT_NAME FROMDEPT D WHERE EXISTS ( SELECT ‘X'
FROM EMP E WHEREE.DEPT_NO = D.DEPT_NO);

（19） sql語句用大寫的；因為oracle總是先解析sql語句，把小寫字母轉換成大寫的再執行

（20） 在java代碼中盡量少用串連符“＋”連接字串！

in 與 exist 的文法比較：
          select × from 資料表 t where t.x in (...)
      括弧內可以是符合t.x欄位類型的值集合，如('1','2','3')，但如果t.x是number類型的時候，似乎這樣的寫法會出問題；也可以是通過 另外的select語句查詢出來的值集合，如(select y from 資料表2 where ...)。

          select * from 資料表 t where [...] and exist (...)
      方括弧內為其它的查詢條件，可以沒有。exist後面的括弧內可以是任意的條件，這個條件可以與外面的查詢沒有任何關係，也可以與外面的條件結合。如： (select * from 資料表2 where 1=1) 或 (select * from 資料表2 where y=t.x)

例子：

in的SQL語句
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) aspubtime
FROM tab_oa_pub WHERE is_check=1 and
category_id in (select id from tab_oa_pub_cate where no='1')
order by begintime desc

exists的SQL語句
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) aspubtime
FROM tab_oa_pub WHERE is_check=1 and
exists (select id from tab_oa_pub_cate wheretab_oa_pub.category_id=convert(int,no) and no='1')
order by begintime desc

效率比較：

       先討論IN和EXISTS。
            select *from t1 where x in ( select y from t2 )
        事實上可以理解為：
            select *from t1, ( select distinct y from t2 ) t2 where t1.x = t2.y
         如果你有一定的SQL最佳化經驗，從這句很自然的可以想到t2絕對不能是個大表，因為需要對t2進行全表的“唯一排序”，如果t2很大這個排序的效能是不可 忍受的。但是t1可以很大，為什麼呢？最通俗的理解就是因為t1.x=t2.y可以走索引。但這並不是一個很好的解釋。試想，如果t1.x和t2.y都有 索引，我們知道索引是種有序的結構，因此t1和t2之間最佳的方案是走merge join。另外，如果t2.y上有索引，對t2的排序效能也有很大提高。

            select *from t1 where exists ( select null from t2 where y = x )
        可以理解為：
            for x in (select * from t1 )
            loop
               if ( exists ( select null from t2 where y = x.x ) then
                  OUTPUT THE RECORD!
               end if
            end loop
        這個更容易理解，t1永遠是個表掃描！因此t1絕對不能是個大表，而t2可以很大，因為y=x.x可以走t2.y的索引。

        綜合以上對IN/EXISTS的討論，我們可以得出一個基本通用的結論：IN適合於外表大而內表小的情況；EXISTS適合於外表小而內表大的情況。

　有兩個簡單例子，以說明 “exists”和“in”的效率問題

　　1) select * from T1 where exists(select 1 from T2 where T1.a=T2.a) ;

　　T1資料量小而T2資料量非常大時，T1<<T2時，1) 的查詢效率高。

　　2) select * from T1 where T1.a in (select T2.a from T2) ;

　　T1資料量非常大而T2資料量小時，T1>>T2時，2) 的查詢效率高。

　　exists 用法：

　　請注意 1）句中的有顏色字型的部分 ，理解其含義；

　　其中 “select 1 from T2 where T1.a=T2.a” 相當於一個關聯表查詢，相當於

　　“select 1 from T1,T2 where T1.a=T2.a”

　　但是，如果你噹噹執行 1） 句括弧裡的語句，是會報語法錯誤的，這也是使用exists需要注意的地方。

　　“exists（xxx）”就表示括弧裡的語句能不能查出記錄，它要查的記錄是否存在。

　　因此“select 1”這裡的 “1”其實是無關緊要的，換成“*”也沒問題，它只在乎括弧裡的資料能不能尋找出來，是否存在這樣的記錄，如果存在，這 1）句的where 條件成立。

　　in 的用法：

　　繼續引用上面的例子

　　“2) select * from T1 where T1.a in (select T2.a from T2) ”

　　這裡的“in”後面括弧裡的語句搜尋出來的欄位的內容一定要相對應，一般來說，T1和T2這兩個表的a欄位表達的意義應該是一樣的，否則這樣查 沒什麼意義。

　　打個比方：T1，T2表都有一個欄位，表示工單號，但是T1表示工單號的欄位名叫“ticketid”，T2則為“id”，但是其表達的意義是 一樣的，而且資料格式也是一樣的。這時，用 2）的寫法就可以這樣：

　　“select * from T1 where T1.ticketid in (select T2.id from T2) ”

　　Select name from employee where name not in (select name fromstudent);

　　Select name from employee where not exists (select name fromstudent);

　　第一句SQL語句的執行效率不如第二句。

　　通過使用EXISTS，Oracle會首先檢查主查詢，然後運行子查詢直到它找到第一個匹配項，這就節省了時間。Oracle在執行IN子查詢 時，首先執行子查詢，並將獲得的結果清單存放在一個加了索引的暫存資料表中。在執行子查詢之前，系統先將主查詢掛起，待子查詢執行完畢，存放在暫存資料表中以後再執行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因

IN和EXISTS區別

1）後面的表大用exists效率高,後面的表小時用in效率高

2）非關聯子查詢指定exists子句是不適當的

3）無論哪個表大，用not exists都比not in要快。

4）盡量不要使用not in子句。使用minus 子句都比not in 子句快，

in 是把外表和內表作hash join，而exists是對外表作loop，每次loop再對內表進行查詢。

一直以來認為exists比in效率高的說法是不準確的。
如果查詢的兩個表大小相當，那麼用in和exists差別不大。

如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in：後面的表大用exists,後面的表小時用in

例如：表A（小表），表B（大表）
1：
select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；
select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。
相反的
2：
select * from B where cc in (select cc from A)
效率高，用到了B表上cc列的索引；
select * from B where exists(select cc from A where cc=B.cc)
效率低，用到了A表上cc列的索引。
帶in的關聯子查詢是多餘的，因為in子句和子查詢中相關的操作的功能是一樣的。如：
select staff_name from staff_member where staff_id in
(select staff_id from staff_func wherestaff_member.staff_id=staff_func.staff_id);

為非關聯子查詢指定exists子句是不適當的，因為這樣會產生笛卡乘積。如：
select staff_name from staff_member where staff_id
exists (select staff_id from staff_func);

not in 和not exists

如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；
而not extsts 的子查詢依然能用到表上的索引。
所以無論哪個表大，用not exists都比not in要快。

盡量不要使用not in子句。使用minus 子句都比not in 子句快，雖然使用minus子句要進行兩次查詢：
select staff_name from staff_member where staff_id in (select staff_id fromstaff_member minus select staff_id from staff_func where func_id like '81%');

in 與 "=" 的區別

select name from student where name in('zhang','wang','li','zhao');

與

select name from student where name='zhang' or name='li' orname='wang' or name='zhao'

的結果是相同的。