SQL編寫及其最佳化

資料庫查詢操作的內部處理過程

[@more@]

資料庫查詢操作的內部處理過程

資料庫的資料讀取是以塊為單位的

Oracle讀取表中的資料時，是以整個塊為單位的，有可能一次讀多個塊；從索引中得到rowid的物理位置時，也要把含rowid這個位置的塊從磁碟中讀出，然後才處理個別行；

查詢操作內部過程以及索引的作用

舉例：select * from personal_function where employee_code= -200 ;

上述查詢，如果employee_code上沒有索引，則會把personal_function表資料的塊全部讀入記憶體中，同時從第一條記錄查到最後一條記錄，對符合條件的記錄返回給使用者；

如果employee_code有索引，則使用索引檢索出含符合條件的Rowid的塊讀入記憶體，然後定位到rowid指示的位置上把記錄返回給使用者；

執行計劃概念

2.2節中，如果employee_code=-200的記錄數在佔總記錄數超過4%，則有可能就不會用索引查詢，而是直接全表掃描，這是oracle的最佳化器經過各種檢測後會自動選擇的；

Oracle對已存在資料統計特徵會選擇不同的執行路徑（或者全表，或者使用索引等），這就產生了不同的執行計劃，使能達到最大的效能。

執行計劃選擇的模式有RBO和CBO方式，採用CBO方式時(這是oracle公司強烈建議使用的)，sql語句中from後表的先後順序以及where條件中各個條件的先後順序變得不是很重要了，如果RBO方式則不然；

CBO全稱 Cost-based Optimizer，基於代價的最佳化。2.2節提到的SQL語句，Oracle是否使用索引，其內部會做各種比較，然後自動做出選擇；為了使Oracle做出更精確的比較，我們應該週期性地對錶進行統計，使 Oracle瞭解到真實情況後做出更準確的判斷。因為要統計，所以稱之為基於代價；

RBO全稱Rule-based Optimizer，基於規則，就是說Oracle定義的一套執行先後順序，如，有索引則一定會用索引，就如2.2節的SQL語句，但這樣有時並不是最好的執行選擇。

排序處理過程以及各種引起排序的操作

舉例：select * from personal_function where employee_code= -200 order by func_name;

由2.2節可知，如果沒有order by語句時，oracle只要檢索到一條符合條件的記錄就立刻返回給使用者，直到所有資料返回完畢。當有order by時，情況就不一樣了，oracle會把檢索到的每一條記錄先儲存在一個用於排序的記憶體中，當所有符合條件的記錄在那個區域完成排序後，再返回給使用者；

透過排序這個操作，oracle中間多處理了一個步驟。資料量越大，排序時間則更長，當資料量達到所找的記憶體區域無法容納時，將使用磁碟做為臨時排序區，此時，效能會大大降低，使用者將等待更長的時間才能得到返回的結果；

因此，在一個SQL語句中，如果對返回的記錄集沒有順序要求時，應該去除引起排序的語句；

引起排序的操作有：order by，distinct，union，group by

多表join操作的內部過程

舉例說明：

Esm系統中的3張表結構如下：

Employee:

EMPLOYEE_CODE	職員程式碼
EMPLOYEE_NAME	職員名稱
EMPLOYEE_KANA	職員全稱

Emp_detial

EMPLOYEE_CODE	職員程式碼
DEPART_CODE	職員部門程式碼
…	…

Depart

Depart_CODE	部門程式碼
DEPART_NAME	部門名稱
…	…

 

現在要求列出所有職員的名稱和其對應的部門名稱，SQL語句如下：

select aa.employee_name,cc.depart_name

from employee aa,emp_detail bb,depart cc

where  aa.employee_code=bb.employee_code and bb.depart_code=cc.depart_code;

1)        讀取employee的第一條記錄，得到職員程式碼A；

2)        再在emp_detail查詢職員程式碼為A的第一條記錄，得到部門程式碼B;

3)        在表depart中查詢部門程式碼為B的記錄；

4)        在各個表的記錄中中取出需要的資訊返回給使用者；

5)        。。。2步和1步是個迴圈操作，2步巢狀於1步中，直到所有資訊返回給使用者；

Oracle如果有其他的執行記錄更好的話，會選擇其他的；