複盤eygle在甲骨文大會上演講中的樣本，看看什麼是大師的由點及面，複盤eygle

複盤eygle在甲骨文大會上演講中的樣本，看看什麼是大師的由點及面，複盤eygle

蓋總(eygle)在剛結束的甲骨文大會的演講中，通過一個簡單的UPDATE語句，為我們展示了什麼叫由點及面的最佳化，什麼叫由點及面的知識覆蓋度，不在於這個案具體如何操作，更應關注或更值得我們借鑒的是這種學習態度和方法思路，大師是如何煉成的？我想這個案例可以帶給我們一些啟迪。

 

下面就複盤一下這個案例的整個過程，註：著作權歸蓋總(eygle)所有~

 

問題描述：

問題的標題是：“並行更新成為系統瓶頸”

SQL：

UPDATE /*+ parallel(a, 8) */ tbl_a aSET name = (SELECT name FROM tbl_b WHERE id = a.id),        class = (SELECT class FROM tbl_b WHERE id = a.id)WHERE a.id IN (SELECT /*+ parallel(b, 8) */ id FROM tbl_b b);

現象是這條SQL執行時間非常長，從介紹看是有2.5分鐘。

 

最佳化過程：

1. 為了以下可以更清楚地說明問題，對這個SQL做了簡化處理，我們需要最佳化的是這條SQL：

UPDATE tbl_a aSET name = (SELECT name FROM tbl_b WHERE id = a.id),        class = (SELECT class FROM tbl_b WHERE id = a.id)WHERE a.id IN (SELECT id FROM tbl_b b);

我們建立兩張類比表：

SQL> create table tbl_a(          id number,          name varchar2(5),          class varchar2(5));Table created.SQL> create table tbl_b(          id number,          name varchar2(5),          class varchar2(5));Table created.SQL> create sequence seq_a cache 1000;Sequence created.SQL> create sequence seq_b cache 1000;Sequence created.

插入一些隨機資料：

begin  for i in 1 .. 100000 loop    insert into tbl_a values (seq_a.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));  end loop;  commit;end;/PL/SQL procedure successfully completed.SQL> select count(*) from tbl_a;  COUNT(*)------------     100000begin  for i in 1 .. 10000 loop    insert into tbl_b values (seq_b.nextval, dbms_random.string('U', 5), dbms_random.string('U', 5));  end loop;  commit;end;/PL/SQL procedure successfully completed.SQL> select count(*) from tbl_b;  COUNT(*)------------      10000

2. 執行原SQL語句

SQL> set timing onSQL> UPDATE tbl_a a          SET name = (SELECT name FROM tbl_b WHERE id = a.id),                 class = (SELECT class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.Elapsed: 00:00:07.42

需要7秒多的時間(雖然和樣本中2.5分鐘有差距，但僅為了說明最佳化的問題，時間上的差距可以忽略)。

3. 第一次最佳化

我們從這個SQL中可以看到，更新TBL_A表的ID列，但TBL_B表的SELECT有三次，即三次的全表掃描，那麼要是能減少TBL_B表檢索的次數，執行時間肯定可以減少。

SQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.Elapsed: 00:00:04.04

這樣的調整是符合SQL文法的，執行時間變為了4秒多，效果顯著。

 

4. 第二次最佳化

雖然執行時間減少了接近一半，但SQL中還是對TBL_B執行了兩次掃描，是否還可以減少一次？

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;SET name = b_name, class = b_class    *ERROR at line 4:ORA-01779: cannot modify a column which maps to a non key-preserved tableElapsed: 00:00:00.01

這樣就做到了只掃描一次TBL_B表，直接對子查詢更新，但此時報了一個錯誤，ORA-01779，

這就引出了non key-preserved table的概念。非索引值儲存表，楊長老的部落格(http://blog.itpub.net/4227/viewspace-195889/)中提到過這個錯誤：

“造成這個錯誤的原因是更新的列不是事實表的列，而是維度資料表的列。換句話說，如果兩張表關聯，其中一張表的關聯列是主鍵，那麼另一張表就是事實表，也就是說另一張表中的列就是可更新的；除非另一張表的關聯列也是主鍵，否則這張表就是不可更新的，如果更新語句涉及到了這張表，就會出現ORA-1799錯誤。如果是兩張表主鍵關聯，那麼無論更新那個表的欄位都可以。

其實這個限制的真正原因是Oracle要確保串連後更新的內容可以寫到一張表中，而這就要求串連方式必須是1對N或者1對1的串連。這樣才能確保串連後的結果集數量和事實表一致。從而使得Oracle對串連後子查詢的更新可以順利的更新到事實表中。”

a.id=b.id，我們是用TBL_B的id列作為條件更新，需要確保這列只會對應到TBL_B表的一行記錄，可以為表TBL_B的id列設定主鍵、唯一索引或唯一約束，三種操作，這裡選擇設定唯一約束：

SQL> alter table tbl_b add constraint uq_b_id unique(id);Table altered.

再次執行：

SQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;10000 rows updated.Elapsed: 00:00:00.12

執行時間一下僅為0.12秒。

上面如果TBL_A的ID列設定為主鍵，則為1對1的串連，如果僅是TBL_B的ID列為唯一約束，則為1對N的串連。

 

總結：

通過兩次最佳化，執行時間從7秒降到了0.12秒，雖然這裡的樣本資料未必和實際情況一致，但成比例的縮放足以說明這個問題，從這個案例可以看出，最佳化的本質就是少做事，原始SQL執行三次全表掃描，那目標就是減少全表掃描的次數，第一次最佳化的操作可能相對容易想到，但第二次最佳化的操作，就需要知道可以有這種文法，而且出現了ORA-01799的錯誤，還需要知道這種錯誤的根本原因是什麼，才能有可行的解決方案。

 

問題還沒完，以上說明了SQL語句的最佳化，下面就是針對這條SQL展開的知識。

假設上面的TBL_A和TBL_B表是屬於使用者bisal的，此時建立一個使用者phibisal，並授予最簡單的許可權：

SQL> create user phibisal identified by phibisal;User created.SQL> grant create session to phibisal;Grant succeeded.

bisal使用者建立這兩張表的public同義字：

SQL> create public synonym tbl_a for bisal.tbl_a;Synonym created.SQL> create public synonym tbl_b for bisal.tbl_b;Synonym created.

然後授予phibisal使用者對TBL_A表的讀和更新許可權：

SQL> grant select, update on tbl_a to phibisal;Grant succeeded.

此時phibisal登入後執行：

sqlplus phibisal/phibisalSQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-00942: table or view does not exist

會提示TBL_B不存在，因為使用者沒有該表的任何許可權，(註：此處和eygle的樣本中反饋不同，他提示的是ORA-01031: insufficient privileges)
如果授予phibisal對TBL_B表的讀許可權，

SQL> grant select on tbl_b to phibisal;Grant succeeded.

此時可以完成更新：

sqlplus phibisal/phibisalSQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);10000 rows updated.

但用如下SQL會提示許可權錯誤：

UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-01031: insufficient privileges

即這種子查詢更新會因沒有TBL_B表的UPDATE許可權報錯。
但如果使用如下with文法，則可以正常執行：

SQL> UPDATE(WITH tmp AS (              SELECT b.name b_name, b.class b_class, a.name, a.class              FROM tbl_a a, tbl_b b              WHERE a.id = b.id)             )SET name = b_name, class = b_class;10000 rows updated.

做得更徹底一些：

SQL> revoke update on tbl_a from phibisal;Revoke succeeded.

撤消了phibisal使用者對TBL_A的更新許可權，按理說，phibisal使用者不應該能再更新TBL_A表了。
使用上面兩個調整後的SQL，確實如此：

sqlplus phibisal/phibisalSQL> UPDATE (SELECT b.name b_name, b.class b_class, a.name, a.class                      FROM tbl_a a, tbl_b b                      WHERE a.id = b.id)          SET name = b_name, class = b_class;                FROM tbl_a a, tbl_b b                                    *ERROR at line 2:ORA-01031: insufficient privileges     SQL> UPDATE tbl_a a          SET (name, class) = (SELECT name, class FROM tbl_b WHERE id = a.id)          WHERE a.id IN (SELECT id FROM tbl_b b);UPDATE tbl_a a       *ERROR at line 1:ORA-01031: insufficient privileges

但是，奇怪的是如下SQL可以執行：

SQL> UPDATE(WITH tmp AS (              SELECT b.name b_name, b.class b_class, a.name, a.class              FROM tbl_a a, tbl_b b              WHERE a.id = b.id)              SELECT * FROM tmp             )SET name = b_name, class = b_class;10000 rows updated.

這就從原理規則上，違背了許可權控制，看下版本：

SQL> select banner from v$version where rownum=1;BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

這就是2014年7月提出的一個bug，在11.2.0.3、11.2.0.4、12.1等版本中都存在的一個問題，需要修正這個bug，相當於使用with文法，可以繞過使用者權限，對沒有許可權的表進行DML操作。

 

總結：

精髓不在於這個bug，而是在於從一條簡單的UPDATE語句，可以派生出如此豐富的知識，可謂舉一反三，受益匪淺。一方面需要我們能夠從原理上理解每一個概念，另一方面也要培養自己舉一反三，知識點由點及面的想法，做到真正的觸類旁通，這樣才能逐漸向大師靠攏，向大師學習。

著作權聲明：本文為博主原創文章，未經博主允許不得轉載。