sql最佳化（oracle）- 第一部分 知識準備

標籤：des   order by   images   share   efi   卡爾   sha   編譯問題   接收   

第一部分 知識準備                                              
　　　　1.  sql執行過程　　　　　　　　　　　                   
　　　　2.  sql 共用　　　　　　　　　　　　　                 
　　　　3.  綁定變數                      　　
　　　　4.  資料表訪問方式　　　　　　　　　
　　　　5.  sql 執行順序　　　　　　　　　　　
　　　　6.  索引使用　　　　　　　　　　　　　
　　　　　　　　　　　　　　　　　　　　　　

1. sql執行過程
1）執行過程
　　當一個oracle執行個體接收到一條sql後，執行過程如下：

    

　　1） create  a cursor  建立遊標
　　2） parse the statement 分析語句
　　3)  describe results of a query 描述查詢的結果集
　　4）define output of a query 定義查詢的輸出資料
　　5）bind any variables 綁定變數
　　6）parallelize the statement 並存執行語句
　　7）run the statement 運行語句
　　8）fetch rows of a query 取查詢結果
　　9）close the cursor 關閉遊標

 

2.SQL 共用
　　1. 為不重複解析相同的SQL語句，oracle 將執行過的SQL語句存放在記憶體的共用池（shared buffer pool）中，可以被所有資料庫使用者共用
　　2. 當執行一個SQL語句時，如果它和之前執行過的語句完全相同（注意同義字和表是不同對象），oracle就能獲得已經被解析的語句；

 

3.bind variables綁定變數
1）解決重編譯問題
eg1：
 insert into tab1(col1,col2) values (val1,val2); --普通方式
 insert into tab1(col1,col2) values (:v1,:v2); --綁定變數，只需編譯一次

eg2：使用PreparedStatement
 PreparedStatement ps = con.prepareStatement("insert into tab1 (col1, col2) values (?,?)");

2）共用遊標
好處：減少解析；提高記憶體使用量率；動態記憶體調整
假如輸入如下兩個sql：
 select * from tab1 where id = :c;
 select * from tab1 where id = :d;
這兩句sql會被轉化為：
 select * from tab1 where id = :b;
 

4.訪問資料表方式
1）全表掃描——順序訪問表中每條記錄
　　oracle採用一個讀入多個資料區塊的方式最佳化全表掃描
2）通過rowid訪問表——rowid包含了表中記錄的物理位置資訊， 基於rowid訪問方式可以提高訪問表的效率
　　oracle通過索引實現了資料和存放資料位元置rowid之間的聯絡，通常索引提供了快速存取rowid的方法

 

5. select sql執行順序
1）select子句

 　　(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>
 　　(1)FROM <left_table>
 　　(3)<join_type> JOIN <right_table>
 　　(2)ON <join_condition>
 　　(4)WHERE <where_condition>
 　　(5)GROUP BY <group_by_list>
 　　(6)WITH <CUBE | RollUP>
 　　(7)HAVING <having_condition>
 　　(10)ORDER BY <order_by_list>

2）執行順序說明
　　1）FROM [left table]——from前的表做笛卡爾集 ——虛擬表VT1
　　2) ON <join condition>——篩選——VT2
　　3) [join type] JOIN [right table]——串連——VT3
　　4) WHERE ——where篩選——VT4
　　5）GROUP BY ——按照GROUP BY子句中的列對VT4中行分組——VT5
　　6）CUBE|ROLLUP——分組，eg：ROLLUP(A, B)，首先會對(A、B)進行GROUP BY，然後對(A)進行GROUP BY，最後對全表GROUP BY
　　　　　　　　　　　　　　　　　   CUBE(A,B), 首先對（A、B）GROUP BY, 然後(A)、(B) GROUP BY, 最後全表GROUP BY;
　　　　　　　　　　　　　　          　——VT6

　　7) HAVING——HAVING篩選——VT7
　　8）SELECT——VT8
　　9) DISTINCT——移除重複的行——VT9
　　10）ORDER BY——按照order by子句中的列將VT9中列表排序，產生遊標——VC10
　　11) TOP ——從VC10的開始處選擇一定數量或者比例的行——VT11，返回結果

3）注意事項
　　1. 只有ORDER BY 子句中可以使用select列表中列的別名
　　   如果要在其他地方使用需要使用如下方式：
  　　　　SELECT * FROM (SELECT NAME, SALARY AS s FROM EMP ) vt WHERE vt.s<5000;
　　2.  使用了ORDER BY子句的查詢不能用作表運算式（視圖、內聯資料表值函式、子查詢、派生表和共用運算式），如下的語句都會產生錯誤
  　　create table tab1 as select * from student order by score;
  　　select * from (select * from student order by score);

 

sql最佳化（oracle）- 第一部分 知識準備