基礎知識
1, 關聯子查詢和非關聯子查詢
在非關聯子查詢中,內部查詢只執行一次並返回它的值給外部查詢,然後外部查詢在它的處理中使用內部查詢返回給它的值。而在關聯子查詢中,對於外部查詢返回的每一行數據,內部查詢都要執行一次。另外,在關聯子查詢中是信息流是雙向的。外部查詢的每行數據傳遞一個值給子查詢,然後子查詢為每一行數據執行一次並返回它的記錄。然後,外部查詢根據返回的記錄做出決策。
如:
SELECT o1.CustomerID, o1.OrderID, o1.OrderDate FROM Orders o1 WHERE o1.OrderDate = (SELECT Max(OrderDate) FROM Orders o2 WHERE o2.CustomerID = o1.CustomerID)
是一個關聯子查詢
SELECT o1.CustomerID, o1.OrderID, o1.OrderDate FROM Orders o1 WHERE o1.OrderDate IN (SELECT TOP 2 o2.OrderDate FROM Orders o2 WHERE o2.CustomerID = o1.CustomerID) ORDER BY CustomerID
是一個非關聯子查詢
2, 提示(HINT)
一般在優化時,無論采用基於規則的或是基於代價的方法,由Oracle系統的優化器來決定語句的執行路徑。這樣的選擇的路徑不要見得是最好的。所以,Oracle提供了一種方法叫提示的方法。它可以讓編程人員按照自己的要求來選擇執行路徑,即提示優化器該按照什麼樣的執行規則來執行當前的語句。這樣可以在性能上比起Oracle優化自主決定要好些。
通常情況下,編程人員可以利用提示來進行優化決策。通過運用提示可以對下面內容進行指定:
l SQL語句的優化方法;
l 對於某條SQL語句,基於開銷優化程序的目標;
l SQL語句訪問的訪問路徑;
l 連接語句的連接次序;
l 連接語句中的連接操作。
如果希望優化器按照編程人員的要求執行,則要在語句中給出提示。提示的有效范圍有限制,即有提示的語句塊才能按照提示要求執行。下面語句可以指定提示:
l 簡單的SELECT ,UPDATE ,DELETE 語句;
l 復合的主語句或子查詢語句;
l 組成查詢(UNION)的一部分。
提示的指定有原來的注釋語句在加“+”構成。語法如下:
[ SELECT | DELETE|UPDATE ] /*+ [hint | text ] */
或
[ SELECT | DELETE|UPDATE ] --+ [hint | text ]
注意在“/*”後不要空就直接加“+”,同樣 “--+”也是連著寫。
警告:如果該提示語句書寫不正確,則Oracle就忽略掉該語句。
常見的提示有:
Ordered 強制按照from子句中指定的表的順序進行連接
Use_NL 強制指定兩個表間的連接方式為嵌套循環(Nested Loops)
Use_Hash 強制指定兩個表間的連接方式為哈希連接(Hash Join)
Use_Merge 強制指定兩個表間的連接方式為合並排序連接(Merge Join)
Push_Subq 讓非關聯子查詢提前執行
Index 強制使用某個索引
3, 執行計劃
在PL/SQL Developer的SQL WINDOWS中用鼠標或鍵盤選中SQL語句,然後按F5,就會出現執行計劃解析的界面:
4, Update的特點
Update的系統內部執行情況可以參照附文:對update事務的內部分析.doc
使用Update的基本要點就是,
1) 盡量使用更新表上的索引,減少不必要的更新
2) 更新的數據來源花費時間盡可能短,如果無法做到就把更新內容插入到中間表中,然後給中間表建上索引,再來更新
3) 如果更新的是主鍵,建議刪除再插入。
5, 示例用表
後面的闡述將圍繞以下兩張表展開:
Create table tab1 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab2 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab3 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
Create table tab4 (workdate varchar2(8), cino varchar2(15), val1 number, val2 number);
workdate, cino為兩張表的關鍵字,默認情況沒有建主鍵索引。
二,Update兩種情況
用Update更新某個表,無外乎是兩種情況:根據關聯子查詢,更新字段;通過非關聯子查詢,限定更新范圍。如果還有第三種情況,那就是前兩種情況的疊加。
1, 根據關聯子查詢,更新字段
Update tab1 t Set (val1, val2) = (select val1, val2 from tab2 where workdate = t.workdate and cino = t.cino);
通過tab2來更新tab1的相應字段。執行SQL語句時,系統會從tab1中一行一行讀記錄,然後再通過關聯子查詢,找到相應的字段來更新。關聯子查詢能否通過tab1的條件快速的查找到對應記錄,是優化能否實現的必要條件。所以一般都要求在tab2上建有Unique或者排重性較高的Normal索引。執行所用時間大概為(查詢tab1中一條記錄所用的時間 + 在tab2中查詢一條記錄所用的時間)* tab1中的記錄條數。
如果子查詢條件比較復雜,如以下語句:
Update tab1 t Set (val1, val2) = (select val1, val2 from tab2 tt where exists (select 1 from tab3 where workdate = tt.workdate and cino = tt.cino) and workdate = t.workdate and cino = t.cino);
這時更新tab1中的每條記錄花費在子查詢上的時間將成倍增加,如果tab1中的記錄數較多,這種更新語句幾乎是不可完成。
解決方式是,把子查詢提取出來,做到中間表中,然後給中間表建上索引,用中間表來代替子查詢,這樣速度就能大大提高:
Insert into tab4 select workdate, cino, val1, val2 from tab2 tt where exists (select 1 from tab3 where workdate = tt.workdate and cino = tt.cino); create index tab4_ind01 on tab4(workdate, cino); Update tab1 t Set (val1, val2) = (select val1, val2 from tab4 tt where workdate = t.workdate and cino = t.cino);
2, 通過非關聯子查詢,限定更新范圍
Update tab1 t set val1 = 1 where (workdate, cino) in (select workdate, cino from tab2)
根據tab2提供的數據范圍,來更新tab1中的相應記錄的val1字段。
在這種情況下,系統默認執行方式往往是先執行select workdate, cino from tab2子查詢,在系統中形成系統視圖,然後在tab1中選取一條記錄,查詢系統視圖中是否存在相應的workdate, cino組合,如果存在,則更新tab1,如果不存在,則選取下一條記錄。這種方式的查詢時間大致等於:子查詢查詢時間 + (在tab1中選取一條記錄的時間 + 在系統視圖中全表掃描尋找一條記錄時間)* tab1的記錄條數。其中“在系統視圖中全表掃描尋找一條記錄時間”會根據tab2的大小而有所不同。若tab2記錄數較小,系統可以直接把表讀到系統區中;若tab2記錄數多,系統無法形成系統視圖,這時會每一次更新動作,就把子查詢做一次,速度會非常的慢。
針對這種情況的優化有兩種
1) 在tab1上的workdate, cino字段上加入索引,同時增加提示。
修改以後的SQL語句如下:
Update /*+ordered use_nl(sys, t)*/ tab1 t set val1 = 1 where (workdate, cino) in (select workdate, cino from tab2)
其中sys表示系統視圖。如果不加入ordered提示,系統將會默認以tab1表作為驅動表,這時就要對tab1作全表掃描。加入提示後,使用系統視圖,即select workdate, cino from tab2,作為驅動表,在正常情況下,速度能提高很多。
2) 在tab2表上的workdate, cino字段加入索引,同時改寫SQL語句:
Update tab1 t set val1 = 1 where exists (select 1 from tab2 where workdate = t.workdate and cino = t.cino)
三,索引問題
update索引的使用比較特殊,有時看起來能用全索引,但實際上卻只用到一部分,所以建議把復合索引的各字段寫在一起。
例如:
Update /*+ordered use_nl(sys, t)*/ tab1 t set val1 = 1 where cino in (select cino from tab2) and workdate = '200506'
這條SQL語句是不能完全用到tab1上的復合索引workdate + cino的。能用到的只是workdate='200506'的約束。
如果寫成這樣,就沒問題:
Update /*+ordered use_nl(sys, t)*/ tab1 t set val1 = 1 where (workdate, cino) in (select workdate, cino from tab2)