oracle自定義函數的執行時間

oracle中的pl/sql代碼會涉及到兩個執行引擎，一個專門處理標准的SQL語句，另外一個處理pl/sql的過程代碼，一般在引擎切換上會帶來cpu額外的開銷。比如foreach循環語句和普通for循環的區別，就是foreach消除了引擎切換，一直駐留在執行標准SQL的引擎中，從而縮短了執行時間。
現在有一個客戶信息表tacustomer， 包含了birthday, certificatetype, certificateno等字段，現在想要獲知客戶的生日信息。由於客戶注冊時生日等字段是可選擇填寫項，故大多數為空，所以要從證件號碼certificateno中提取，certificatetype為證件類型，除身份證之外還有軍官證、士兵證、戶口本等枚舉值，輸入的值也不可靠，本來'0'代表身份證，但由於錄入錯誤，使得非'0'值可能也指身份證，而'0'也可能輸入的不是身份證。結合這些考慮因素，寫了一個函數，輸入參數為上述三個字段，輸出為生日的年份。寫了兩個版本，在筆者的windows2003數據庫服務器上（Intel(R) Xeon(R) CPU 5140 @ 2.33GHZ，4cpu，4.00GB內存），執行語句如下（tacustomer行數為200百萬左右）：

-- 測試簡單字符串連接的執行時間
SELECT COUNT(t.certificatetype || t.certificateno || t.birthday)
FROM tacustomer t
WHERE 1 = 1
AND ROWNUM < 2000000

-- 測試自定義函數的執行時間
SELECT COUNT(f_extract_birthday(t.certificatetype, t.certificateno, t.birthday))
FROM tacustomer t
WHERE 1 = 1
AND ROWNUM < 2000000

執行時間如下（秒）：
8.563
19.844（版本1）
57.953（版本2）

可以看出基本是3倍的關系（將上面的條件改為ROWNUM < 1000000，同樣滿足3倍的關系），版本1和版本2之間的區別是內部實現不同。第一代身份證15位必須全為0-9的數字，第二代18位的前17位全為數字，最後一個為數字或'X'。版本1和2對這個是否數字的校驗采用了不同的方式。版本1逐個檢查，版本2用cast(certno as numeric)並捕捉異常的方式，結果版本2比1快3倍。從中得出的結論，自定義函數一般性能比不上系統內建函數。

附兩個版本的代碼：