Oracle大資料查詢最佳化

標籤：使用   strong   資料   問題   工作   時間   資料庫   sql   

1、對於像狀態之類的列，不是很多的，就可以加位元影像索引，對於唯一的列，就加唯一索引，其餘的建立普通索引。

2、盡量不要使用select * 這樣的查詢,指定需要查詢的列。

3、使用hits  select /*+index(索引名稱) index(索引名稱)*/ supply_id from CSS_SUPPLY  where ...

4、將表table線上重定義為分區表，在name列上使用hash分區或者range分區

5、如果是exedata平台，不要用索引，盡量少用hint；

6、用並行；

     ---------------------

    |   Oracle的索引      |

     ---------------------

    Oracle提供大量的索引選項，使用索引的目的是為了加快查詢速度、減少I/O操作和消除磁碟排序，那麼在何種情況下建立索引才能發揮索引的作用呢？通常情況下建立索引的規則[2]可描述如下： (1) 表的主鍵和外鍵必須有索引； (2) 對經常與其他表進行串連的表的串連欄位應該建立索引； (3) 經常出現在Where子句中的欄位應該建立索引(尤其是資料量較大的表的欄位)； (4) 選擇性高的欄位應該建立索引； 索引的選擇性是指索引列裡不同值的數目與表中記錄數的比。如果表中有1000個記錄，表索引列有950個不同的值，那麼這個索引的選擇性就是0.95。最好的可能性選擇是1.0，依據非空值列的唯一索引，通常其選擇性為1.0。 (5) 小欄位應該建立索引，對於較長的文字欄位甚至超長欄位，不要建索引； (6) 複合索引的建立需要經過仔細分析，盡量考慮用單欄位索引代替；      ① 正確選擇複合索引中主欄欄位，一般是選擇“選擇性”較好 的欄位；     ② 複合索引的幾個欄位是否經常同時以AND方式出現在Where子句中？單欄位的查詢是否極少甚至沒有？如果以上兩種情況或兩者之一，則應該建立複合索引，否則考慮單欄位索引；     ③ 如果複合索引中包含的欄位經常單獨出現在Where子句中，則分解為多個單欄位索引；     ④ 複合索引所包含的欄位一般不要超過3個，否則需要仔細考慮其必要性；     ⑤ 如果既有單欄位索引，又有包含這幾個欄位的複合索引，一般可以刪除複合索引；     ⑥ 建立複合索引的特殊情況：如果表中的資料相當穩定且欄位不多，可以考慮充分索引一個表，即建立一個複合索引，它包括所有在查詢期間通常會被選擇的列，查詢所要求的所有資料可以通過索引訪問提供，避免了索引掃描隨後的任何錶訪問。

(7) 頻繁進行插入、刪除操作的表，不要建立過多索引； (8) 刪除無用索引，避免對執行計畫造成負面影響；

    以上是一些建立索引時較為普遍的判斷依據。太多的索引與不充分、不正確的索引對效能都毫無益處，因為在表上建立的每個索引都會增加儲存開銷，索引對於插入、刪除、更新操作也會增加處理上的開銷。另外，過多的複合索引，在有單欄位索引的情況下，一般都是沒有存在價值的；相反，還會降低資料增加刪除時的效能，特別是對頻繁更新的表來說，負面影響更大。

     ---------------------

    |    SQL語句的最佳化    |

     ---------------------

    正確地使用索引，可以理使用資源，使得資料的查詢速度加快。但是，建立索引之後，並非就意味著查詢時的速度得到了提高，這時還需要有良好的SQL語句進行支援，才能使得查詢中利用索引從而提高查詢的速度。下面將對如何在SQL語句中利用索引來提高查詢速度進行一下分析。

(1) IS NULL 與 IS NOT NULL      不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存在空值，即使對該列建索引也不會提高效能。任何在where子句中使用is null或is not null的語句最佳化器是不允許使用索引的。 

(2) 聯結列     對於有聯結的列，即使最後的聯結值為一個靜態值，最佳化器是不會使用索引的。比如下面的sql語句 select * from oil where oil_name||‘ ‘||oil_id =‘勝坨 shengtuo‘;

要查詢油田為“勝坨 shengtuo”的油田的資料，假設在oil_id列已經建了索引，此sql語句雖然也能實現正確的查詢，但系統最佳化器對基於oil_id建立的索引卻沒有使用。如果改為以下的sql語句 select * from oil where oil_name =‘勝坨’and oil_id= ‘shengtuo‘;

Oracle系統就可以採用基於oil_id建立的索引。

(3) 帶萬用字元（%）的like語句 

    同樣以上面的例子來看這種情況。目前的需求是這樣的，要求在oil表中查詢名稱中包含勝坨的油。可以採用如下的查詢SQL語句：select * from employee where oil_name like ‘%勝坨%‘;     在以上sql語句中萬用字元（%）在搜尋詞首出現，所以Oracle系統不使用oil_id的索引。不過在很多情況下可能無法避免這種情況，但是需要瞭解的是如此使用萬用字元會降低查詢速度。不過當萬用字元出現在字串的其它位置時，最佳化器就可以利用索引了，例如以下的查詢就可以利用索引： select * from employee where oil_name like ‘勝坨%‘;

(4) Order by語句     order by語句決定了Oracle如何將返回的查詢結果排序。order by語句對要排序的列沒有什麼特別的限制，也可以將函數加入列中（像聯結或者附加等）。任何在order by語句的非索引項目或者有計算運算式都將降低查詢速度。 仔細檢查order by語句以找出非索引項目或者運算式，它們會降低效能。解決這個問題的辦法就是重寫order by語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在order by子句中使用運算式。

(5) NOT     在查詢中，我們經常在where子句使用一些邏輯運算式，如大於、小於、等於以及不等於等等，也會用到and（與）、or（或）以及not（非）。NOT可用來對任何邏輯運算子號取反，比如... where not (oil_id=‘shengtuo‘)  如果要使用NOT，則應在取反的短語前面加上括弧，並在短語前面加上not運算子。not運算子包含在另外一個邏輯運算子中，這就是不等於（<>）運算子。也可以說，即使不在查詢where子句中顯式地加入not詞，not仍在運算子中。 

看下面兩條sql語句： 

select * from oil where productoil <>3000;  select * from oil where productoil <3000 or productoil >3000;

    雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle對productoil列使用索引，而第一種查詢則不能使用索引。 

(6) IN和EXISTS 

    在查詢中，我們也經常會用到兩列進行比較的情況，最簡單的方法是在where句子中使用子查詢，而這種子查詢一般有兩種形式。 一種是利用in操作符：... where column in (select * from ...where ...); 另一種是使用exist操作符：... where exists (select ‘X‘ from ...where ...);

    對於這兩種格式的子查詢語句，一般都會使用第一種是用in操作符的查詢，因為這種語句比較容易些，也很直觀，但實際上卻是第二種格式的子查詢效率會更高。在Oracle中可以將幾乎所有的in操作符子查詢改寫為使用exists的子查詢。     第二種格式中，子查詢以‘select ‘X‘’開始。運用exists子句不管子查詢從表中抽取什麼資料它只查看where子句的特點，這樣最佳化器就不必遍曆整個表而僅根據索引就可完成工作（這裡假定在where語句中使用的列存在索引）。相對於IN子句來說，exists使用相連子查詢，構造起來要比in子查詢困難一些。     通過使用exists，Oracle系統會首先檢查主查詢，然後運行子查詢直到找到第一個匹配項，這就節省了時間。Oracle系統在執行in子查詢時，首先執行子查詢，並將獲得的結果清單存放在一個加了索引的暫存資料表中。在執行子查詢之前，系統先將主查詢掛起，待子查詢執行完畢，存放在暫存資料表中以後再執行主查詢。這也就是使用exists比使用in通常查詢速度快的原因。 

     ---------------------

    |          暫存資料表        |

     ---------------------    

　　目前大部分使用Oracle作為資料庫的系統資料量都比較龐大，在使用時我們經常會用到有多個表關聯的情況，而且這些表大部分都比較龐大，但是當進行關聯的時候卻發現某一個表或幾個表關聯之後得到的結果集很小而且查詢這個結果集的速度也非常快，那麼這時候我們就可以考慮在Oracle中建立“暫存資料表”。這樣在工程中多次用到這些資料時直接查詢暫存資料表的速度要快很多，而當用完之後表中的資料就沒用了，而且Oracle的暫存資料表建立之後基本不佔用資料表空間。  與使用永久表不同，添加或者更改暫存資料表中的資料並不會產生重做日誌條目，但是，它會產生撤銷日誌條目。永久表與暫存資料表之間的另一項區別是片段的分配。暫存資料表使用臨時片段，並且在表中實際插入資料之前，不會向表分配臨時片段。

    ORACLE資料庫系統的暫存資料表有兩種，一種是事務型暫存資料表，一種是會話型暫存資料表。事物型暫存資料表是當一個事務結束時清空暫存資料表的內容；而會話型暫存資料表就當一個會話中斷或者被重新串連時資料表的內容就會清空了。ORACLE資料庫在清除暫存資料表時，只清除資料，而不清楚暫存資料表的本身。ORACLE還提供了一種更加細分的事務型暫存資料表。一個會話中，可能有多個事務。也就是說，ORACLE清空臨時型資料表的時間更加細膩，可以根據同一個會話中的不同事務來清空暫存資料表。另外還要說明的一點就是，ORACLE的會話型臨時中的內容對於各個使用者來說，內容都是獨立的。具體的說，就是各個使用者在會話的過程中，都可以往一張暫存資料表中儲存資料；但是使用者查詢暫存資料表中的資料的時候，只能夠查詢到自己所建立的內容，而不能看到其他使用者所增加的記錄。這對於暫存資料表的安全性來說，是非常有保障的。     暫存資料表也有局限性，比如：

　　(1) 暫存資料表只在當前串連內有效；

　　(2) 暫存資料表不建立索引，所以如果資料量比較大或進行多次查詢時，不推薦使用；

　　(3) 資料處理比較複雜的時候時較快，反之視圖快點。

     ---------------------

    |          總結           |  

    ---------------------   

  綜上所述，在進行簡單查詢時，充分利用索引，併合理的最佳化SQL語句來有效利用索引，可以減少回應時間。而在工程中利用暫存資料表減少資料的訪問量、提高資料庫操作的效率，從而減少回應時間。但是以上的最佳化方法都是作為參考用的，最佳化必須隨著系統應用情況的變化和資料量的變化而調整，靈活地採取最佳化手段才能有效地提高系統效率。