Oracle 自訂函式查詢加速密技–Scalar Subquery Caching

在 SELECT 指令對欄位執行自訂函式行運算通常很傷效能,但實務上無法完全避免。查詢一萬筆資料代表要呼叫自訂函式一萬次,若函式包含資料表查詢,如同在迴圈裡跑 SQL,是典型的效能殺手,經驗裡也是許多複雜查詢逾時的主因。

見識到同事露了一手,簡單加幾個字元一口氣將內含自訂函式的 Oracle SELECT 加速數十倍! 瞠目結舌之餘,立馬實驗證明效果驚人,特筆記並分享如下。

以下為實驗環境,JeffTest 資料表有 IDX, N 兩個 NUMBER 欄位,先塞入 256 筆資料,IDX 由 0 - 255,N 則是 IDX 除 4 的餘數,依序為 0, 1, 2, 3, 0, 1, 2, 3, ...。

我寫了一個無聊的自訂函式 FN_SQR 計算傳入數值的平方,為了讓效能差異明顯一點,還加了一個 dbms_lock.sleep(0.01) 延遲 0.01 秒,順便加上 dbms_output.put_line(n) 方便觀察執行次數:

create or replace function FN_SQR(n number) return number is
  FunctionResult number;
begin
  FunctionResult := n * n;
  dbms_lock.sleep(0.01);
  dbms_output.put_line(n);
  return(FunctionResult);
end FN_SQR;

以下是兩個結果相同的查詢,第一個查詢是最直覺的寫法,SELECT 256 筆資料的 IDX 及 FN_SQR(N);第二個查詢動點手腳,將 FN_SQR(N) 改成子查詢 (SELECT FN_SQR(N) FROM DUAL),猜猜二者效能差多少?

2.655 秒 vs 0.145 秒! 快了近 20 倍,為什麼?

看一下 dbms_output.put_line() 的輸出結果就知道差異何在,SELECT IDX, FN_SQR(N) 呼叫了 FN_SQR() 256 次,而 SELECT IDX, (SELECT FN_SQR(N) FROM DUAL) 只執行了四次,0,1,2,3 各一次,參數相同的函式呼叫可使用 Cache 結果,不必重複執行。

這裡的效能提升來自 Scalar Subquery Caching – Oracle 會為 SELECT 出現的 Scalar Subquery (只傳回單一值的子查詢) 在記憶體建立一個 Hashtable,整理不同參數與查詢結果的對應表。若參數欄位值先前出現過,即可直接由 Hashtable 取值不用重新計算。在這個案例中,N 欄位只有 0,1,2,3 四種變化,故只有前四筆資料需要計算,之後的 252 筆全由 Hashtable 取值。

換句話說,使用 SELECT 自訂函數(參數欄位) FROM DUAL 技巧將自訂函式呼叫包成子查詢,就可享受 Oracle Scalar Subquery Caching 的加速效果,資料筆數愈多、參數欄位值重複性愈高、自訂函式運算愈耗時,就愈能感受到明顯的效能提升。使用 Oracle 的同學們別錯過這招加速技。

歡迎推文分享:
Published 30 October 2017 07:51 AM 由 Jeffrey
Filed under:
Views: 7,150



意見

沒有意見

你的看法呢?

(必要的) 
(必要的) 
(選擇性的)
(必要的) 
(提醒: 因快取機制,您的留言幾分鐘後才會顯示在網站,請耐心稍候)

5 + 3 =

搜尋

Go

<October 2017>
SunMonTueWedThuFriSat
24252627282930
1234567
891011121314
15161718192021
22232425262728
2930311234
 
RSS
創用 CC 授權條款
【廣告】
twMVC

Tags 分類檢視
關於作者

一個醉心技術又酷愛分享的Coding魔人,十年的IT職場生涯,寫過系統、管過專案, 也帶過團隊,最後還是無怨無悔地選擇了技術鑽研這條路,近年來則以做一個"有為的中年人"自許。

文章典藏
其他功能

這個部落格


Syndication