oracle pl/sql 實戰學習，oraclepl

oracle pl/sql 實戰學習，oraclepl

這幾天看了下 oracle pl/sql 實戰 這本書，出於對sql語句效能的研究，先研究下遊標對代碼的影響。

1.逐行處理

程式聲明了一個遊標c1，然後用遊標for迴圈隱式地開啟了這個遊標，對從遊標c1取出的每一行，程式查詢customers表，並把first_name和last_name的值填充到變數，隨後插入一行資料到top_sales_customers表。

問題1：

代碼清單1-1的編程方法很有問題。即使在迴圈中調用的SQL語句是高度最佳化的，程式的執行還是會消耗大量時間。假設查詢customers表的SQL語句消耗0.1秒，INSERT語句也消耗0.1秒，那麼在迴圈中每次執行就要0.2秒。如果遊標c1取出了100 000行，那麼總時間就是100 000乘以0.2秒，即20 000秒，也就是大約5.5小時。很難去最佳化這個程式的結構。基於顯而易見的理由，TomKyte把這種處理方式定義為慢之又慢的處理（slow-by-slow processing）。

問題2：

代碼清單1-1的代碼還有一個固有的問題。從PL/SQL的迴圈中調用的SQL語句會反覆在PL/SQL引擎和SQL引擎之間切換執行，這種兩個環境之間的切換稱作環境切換。環境切換增加了程式啟動並執行時間，並增加不必要的CPU開銷。你應當通過消除或減少這種兩個環境之間的切換來減少環境切換的次數。一般應當禁止逐行處理，更好的編程實踐是把代碼清單1-1的程式轉換成一個SQL語句。代碼清單1-2重寫了代碼，完全避免了PL/SQL

代碼清單1-2除瞭解決逐行處理的缺陷以外，還有更多的優勢。重寫後的SQL語句可以使用並存執行來調優，使用多個並存執行進程可以大幅地減少執行時間。並且，代碼變得簡明且可讀性強。

2.嵌套的逐行處理

在代碼清單1-3中，c1、c2和c3是嵌套遊標。遊標c1是頂級遊標，從表t1取得資料，c2是開放遊標，傳遞從遊標c1取得的值，c3也是開放遊標，傳遞遊標c2取得的值。有一個UPDATE語句對遊標c3返回的每一行執行一次。儘管UPDATE語句已經最佳化為執行一次只要0.01秒，但程式的效能還是會由於深度嵌套遊標而難以忍受的。假設遊標c1、c2和c3分別返回20、50和100行，那麼上述代碼需要迴圈100 000行，程式的總執行時間超過了1000秒。對這類程式的調優通常需要完全重寫它。

代碼清單1-3中代碼的另一個問題在於先執行一個UPDATE語句。如果UPDATE 語句產生了
no_data_found異常④，那麼再執行一個INSERT語句。這種類型的問題可以利用MERGE語句從PL/SQL
轉到SQL引擎處理。
從概念上講，代碼清單1-3中的三重迴圈表示表t1、t2和t3之間的等值串連。代碼清單1-4展示
了根據上述邏輯改寫的使用表別名t的SQL語句。UPDATE和INSERT邏輯的結合用MERGE語句代替，
MERGE文法提供了更新存在的行和插入不存在的行的功能。

不要在PL/SQL語言中編寫深度嵌套遊標的代碼。審查這類代碼的邏輯，看是否能用SQL語句來代替。

3.尋找式查詢

尋找式查詢（lookup query）一般用於填充某些變數或執行資料的合法驗證。但在迴圈中執行尋找式查詢會導致效能問題。在代碼清單1-5中，高亮顯示的部分就是使用尋找式查詢來得到country_name值。程式對遊標c1中的每一行都要執行一個查詢來取得country_name的值。當從遊標c1中取得的行數增加時，執行尋找式查詢的次數也增加了，這導致代碼的效率低下。

代碼清單1-5的代碼是過分簡化的，對country_name的尋找式查詢實際上可以重寫為主遊標c1本身中的一個串連。第一步，應將尋找式查詢修改為串連，可是在實際的應用程式中，並不一定可以實現這種改寫。如果無法利用改寫代碼來減少尋找式查詢的執行次數，那麼還有另一個選擇。你可以定義一個關聯陣列來緩衝尋找式查詢的結果，以便在隨後的執行中重用這個數組，這樣也能有效地減少尋找式查詢的執行。代碼清單1-6示範了數組緩衝技術。不必再在遊標c1返回的每一行中執行查詢來得到country_name，而是用一個名為l_country_names的關聯陣列來儲存本例中的country_id 和country_name鍵—值對。關聯陣列和索引類似，任意給定的值都可以通過一個鍵值來訪問。在執行尋找式查詢前，通過EXISTS操作對一個數組中是否存在一個匹配country_id鍵值的元素做一個存在性驗證，如果數組中存在這麼一個元素，那麼country_name就從數組中擷取而不需
要執行尋找式查詢。如果沒有這樣的元素，那麼就執行尋找式查詢，並且把查到的結果作為一個新元素存入數組。你還需要理解，這種技術非常適用於不同的鍵值很少的語句，在本例中，當country_id列的唯一值個數越少時，尋找式查詢的執行次數可能也越少。如使用樣本模式，執行尋找式查詢的次數最多是23，因為country_id列只有23個不同的值。

注意 關聯陣列所需記憶體是在資料庫伺服器中專用伺服器處理序的PGA（Program Global Area，程式全域區）中分配的，如果數千個串連都要把程式的中間結果緩衝到數組中，那麼記憶體的佔用將會大幅增加。你應當掌握每個進程的記憶體使用量增加情況，並設計資料庫伺服器以適應記憶體的增長。