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

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

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

問題描述：

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

SQL：

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

優化過程：

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

我們建立兩張模拟表：

插入一些随機資料：

2. 執行原SQL語句

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

3. 第一次優化

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

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

4. 第二次優化

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

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

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

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

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

再次執行：

執行時間一下僅為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，并授予最簡單的權限：

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

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

此時phibisal登入後執行：

會提示TBL_B不存在，因為使用者沒有該表的任何權限，(注：此處和eygle的示例中回報不同，他提示的是ORA-01031: insufficient privileges)

如果授予phibisal對TBL_B表的讀權限，

此時可以完成更新：

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

即這種子查詢更新會因沒有TBL_B表的UPDATE權限報錯。

但如果使用如下with文法，則可以正常執行：

做得更徹底一些：

撤消了phibisal使用者對TBL_A的更新權限，按理說，phibisal使用者不應該能再更新TBL_A表了。

使用上面兩個調整後的SQL，确實如此：

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

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

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

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