基礎(chǔ)知識(shí)
1， 關(guān)聯(lián)子查詢和非關(guān)聯(lián)子查詢

在非關(guān)聯(lián)子查詢中，內(nèi)部查詢只執(zhí)行一次并返回它的值給外部查詢，然后外部查詢?cè)谒奶幚碇惺褂脙?nèi)部查詢返回給它的值。而在關(guān)聯(lián)子查詢中，對(duì)于外部查詢返回的每一行數(shù)據(jù)，內(nèi)部查詢都要執(zhí)行一次。另外，在關(guān)聯(lián)子查詢中是信息流是雙向的。外部查詢的每行數(shù)據(jù)傳遞一個(gè)值給子查詢，然后子查詢?yōu)槊恳恍袛?shù)據(jù)執(zhí)行一次并返回它的記錄。然后，外部查詢根據(jù)返回的記錄做出決策。

如：

SELECT o1.CustomerID, o1.OrderID, o1.OrderDate
FROM Orders o1
WHERE o1.OrderDate = (SELECT Max(OrderDate)
FROM Orders o2
WHERE o2.CustomerID = o1.CustomerID)

是一個(gè)關(guān)聯(lián)子查詢

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

是一個(gè)非關(guān)聯(lián)子查詢

2， 提示（HINT）

一般在優(yōu)化時(shí)，無(wú)論采用基于規(guī)則的或是基于代價(jià)的方法，由Oracle系統(tǒng)的優(yōu)化器來(lái)決定語(yǔ)句的執(zhí)行路徑。這樣的選擇的路徑不要見(jiàn)得是最好的。所以，Oracle提供了一種方法叫提示的方法。它可以讓編程人員按照自己的要求來(lái)選擇執(zhí)行路徑，即提示優(yōu)化器該按照什么樣的執(zhí)行規(guī)則來(lái)執(zhí)行當(dāng)前的語(yǔ)句。這樣可以在性能上比起Oracle優(yōu)化自主決定要好些。

通常情況下，編程人員可以利用提示來(lái)進(jìn)行優(yōu)化決策。通過(guò)運(yùn)用提示可以對(duì)下面內(nèi)容進(jìn)行指定：

l SQL語(yǔ)句的優(yōu)化方法；

l 對(duì)于某條SQL語(yǔ)句，基于開銷優(yōu)化程序的目標(biāo)；

l SQL語(yǔ)句訪問(wèn)的訪問(wèn)路徑；
l 連接語(yǔ)句的連接次序；
l 連接語(yǔ)句中的連接操作。

如果希望優(yōu)化器按照編程人員的要求執(zhí)行，則要在語(yǔ)句中給出提示。提示的有效范圍有限制，即有提示的語(yǔ)句塊才能按照提示要求執(zhí)行。下面語(yǔ)句可以指定提示：

l 簡(jiǎn)單的SELECT ,UPDATE ,DELETE 語(yǔ)句；
l 復(fù)合的主語(yǔ)句或子查詢語(yǔ)句；
l 組成查詢（UNION）的一部分。

提示的指定有原來(lái)的注釋語(yǔ)句在加“+”構(gòu)成。語(yǔ)法如下：

[ SELECT | DELETE|UPDATE ] /*+ [hint | text ] */

或

[ SELECT | DELETE|UPDATE ] --+ [hint | text ]

注意在“/*”后不要空就直接加“+”，同樣 “--+”也是連著寫。

警告：如果該提示語(yǔ)句書寫不正確，則Oracle就忽略掉該語(yǔ)句。

常見(jiàn)的提示有：

Ordered 強(qiáng)制按照f(shuō)rom子句中指定的表的順序進(jìn)行連接
Use_NL 強(qiáng)制指定兩個(gè)表間的連接方式為嵌套循環(huán)（Nested Loops）
Use_Hash 強(qiáng)制指定兩個(gè)表間的連接方式為哈希連接（Hash Join）
Use_Merge 強(qiáng)制指定兩個(gè)表間的連接方式為合并排序連接（Merge Join）
Push_Subq 讓非關(guān)聯(lián)子查詢提前執(zhí)行
Index 強(qiáng)制使用某個(gè)索引

3， 執(zhí)行計(jì)劃

在PL/SQL Developer的SQL WINDOWS中用鼠標(biāo)或鍵盤選中SQL語(yǔ)句，然后按F5，就會(huì)出現(xiàn)執(zhí)行計(jì)劃解析的界面：

4， Update的特點(diǎn)

Update的系統(tǒng)內(nèi)部執(zhí)行情況可以參照附文：對(duì)update事務(wù)的內(nèi)部分析.doc

使用Update的基本要點(diǎn)就是，

1） 盡量使用更新表上的索引，減少不必要的更新
2） 更新的數(shù)據(jù)來(lái)源花費(fèi)時(shí)間盡可能短，如果無(wú)法做到就把更新內(nèi)容插入到中間表中，然后給中間表建上索引，再來(lái)更新
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為兩張表的關(guān)鍵字，默認(rèn)情況沒(méi)有建主鍵索引。

二，Update兩種情況

用Update更新某個(gè)表，無(wú)外乎是兩種情況：根據(jù)關(guān)聯(lián)子查詢，更新字段；通過(guò)非關(guān)聯(lián)子查詢，限定更新范圍。如果還有第三種情況，那就是前兩種情況的疊加。

1， 根據(jù)關(guān)聯(lián)子查詢，更新字段

Update tab1 t
Set (val1, val2) = (select val1, val2
from tab2
where workdate = t.workdate
and cino = t.cino);

通過(guò)tab2來(lái)更新tab1的相應(yīng)字段。執(zhí)行SQL語(yǔ)句時(shí)，系統(tǒng)會(huì)從tab1中一行一行讀記錄，然后再通過(guò)關(guān)聯(lián)子查詢，找到相應(yīng)的字段來(lái)更新。關(guān)聯(lián)子查詢能否通過(guò)tab1的條件快速的查找到對(duì)應(yīng)記錄，是優(yōu)化能否實(shí)現(xiàn)的必要條件。所以一般都要求在tab2上建有Unique或者排重性較高的Normal索引。執(zhí)行所用時(shí)間大概為（查詢tab1中一條記錄所用的時(shí)間 + 在tab2中查詢一條記錄所用的時(shí)間）* tab1中的記錄條數(shù)。

如果子查詢條件比較復(fù)雜，如以下語(yǔ)句：

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);

這時(shí)更新tab1中的每條記錄花費(fèi)在子查詢上的時(shí)間將成倍增加，如果tab1中的記錄數(shù)較多，這種更新語(yǔ)句幾乎是不可完成。

解決方式是，把子查詢提取出來(lái)，做到中間表中，然后給中間表建上索引，用中間表來(lái)代替子查詢，這樣速度就能大大提高：

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， 通過(guò)非關(guān)聯(lián)子查詢，限定更新范圍

Update tab1 t
set val1 = 1
where (workdate, cino) in (select workdate, cino from tab2)

根據(jù)tab2提供的數(shù)據(jù)范圍，來(lái)更新tab1中的相應(yīng)記錄的val1字段。

在這種情況下，系統(tǒng)默認(rèn)執(zhí)行方式往往是先執(zhí)行select workdate, cino from tab2子查詢，在系統(tǒng)中形成系統(tǒng)視圖，然后在tab1中選取一條記錄，查詢系統(tǒng)視圖中是否存在相應(yīng)的workdate, cino組合，如果存在，則更新tab1，如果不存在，則選取下一條記錄。這種方式的查詢時(shí)間大致等于：子查詢查詢時(shí)間 + （在tab1中選取一條記錄的時(shí)間 + 在系統(tǒng)視圖中全表掃描尋找一條記錄時(shí)間）* tab1的記錄條數(shù)。其中“在系統(tǒng)視圖中全表掃描尋找一條記錄時(shí)間”會(huì)根據(jù)tab2的大小而有所不同。若tab2記錄數(shù)較小，系統(tǒng)可以直接把表讀到系統(tǒng)區(qū)中；若tab2記錄數(shù)多，系統(tǒng)無(wú)法形成系統(tǒng)視圖，這時(shí)會(huì)每一次更新動(dòng)作，就把子查詢做一次，速度會(huì)非常的慢。

針對(duì)這種情況的優(yōu)化有兩種

1） 在tab1上的workdate, cino字段上加入索引，同時(shí)增加提示。

修改以后的SQL語(yǔ)句如下：

Update /*+ordered use_nl(sys, t)*/ tab1 t
set val1 = 1
where (workdate, cino) in (select workdate, cino from tab2)

其中sys表示系統(tǒng)視圖。如果不加入ordered提示，系統(tǒng)將會(huì)默認(rèn)以tab1表作為驅(qū)動(dòng)表，這時(shí)就要對(duì)tab1作全表掃描。加入提示后，使用系統(tǒng)視圖，即select workdate, cino from tab2，作為驅(qū)動(dòng)表，在正常情況下，速度能提高很多。

2） 在tab2表上的workdate, cino字段加入索引，同時(shí)改寫SQL語(yǔ)句：

Update tab1 t
set val1 = 1
where exists (select 1
from tab2
where workdate = t.workdate
and cino = t.cino)

三，索引問(wèn)題

update索引的使用比較特殊，有時(shí)看起來(lái)能用全索引，但實(shí)際上卻只用到一部分，所以建議把復(fù)合索引的各字段寫在一起。

例如：

Update /*+ordered use_nl(sys, t)*/ tab1 t
set val1 = 1
where cino in (select cino from tab2)
and workdate = '200506'

這條SQL語(yǔ)句是不能完全用到tab1上的復(fù)合索引workdate + cino的。能用到的只是workdate='200506'的約束。

如果寫成這樣，就沒(méi)問(wèn)題：

Update /*+ordered use_nl(sys, t)*/ tab1 t
set val1 = 1
where (workdate, cino) in (select workdate, cino from tab2)

最新評(píng)論