Oracle中建立函數時的Deterministic選項

原文串連：http://blog.sina.com.cn/s/blog_6ff05a2c0100n2qo.html

我們平時在SQL語句的WHERE條件中使用函數是很常見的事情，考慮一下下面兩個SQL在執行機制上有什麼區別：
1. SELECT * FROM T WHERE col1 = UPPER('hello');
2. SELECT * FROM T WHERE col1 = DBMS_RANDOM.VALUE(1,100);
UPPER('hello')經過處理後會變成'HELLO'，它相當於一個常量，因此第1個SQL類似於：
SELECT * FROM T WHERE col1 = 'HELLO'；
而第2個SQL由於DBMS_RANDOM.VALUE(1,100)是不確定的，因此不能進行處理。這就導致T表有多少行，DBMS_RANDOM.VALUE這個函數就要執行多少次。而第1個SQL其實只執行了很少次數的UPPER函數，與表T的行數無關。
如果我們想自己寫一個函數SQUARE，實現平方的功能，那麼在下面的SQL中，SQUARE函數會執行多少次呢？
SELECT * FROM T WHERE col = SQUARE(10);
我們可以通過實驗來測試它，實驗的大致方法是：
1.建立一個包，包裡面定義一個NUMBER類型的變數，初始值設為0。
2.在我們自己定義的函數中增加一個將全域變數+1的賦值操作，這樣每調用一次該函數，全域變數就會增加1。
3.最後輸出全域變數的值，就是該函數被調用過的次數。當然每次測試前要將全域變數清零。
說到這裡我要提一個oracle函數的屬性，就是deterministic。它表示一個函數在輸入不變的情況下輸出是否確定，像oracle的內建函數UPPER,TRUNC等都是deterministic函數，而像DBMS_RANDOM.VALUE就不是deterministic函數，因為同樣的輸入不一定會導致同樣的輸出。
如果我們在定義SQUARE函數時沒有加deterministic屬性，那麼經過我的測試，SELECT * FROM T WHERE col = SQUARE(10);
這個SQL會執行ROWNUMBER次SQUARE函數，也就是如果表T有100行，那就執行100次。
如果我們在定義SQUARE函數時加上了deterministic屬性，那麼經過我的測試，SELECT * FROM T WHERE col = SQUARE(10);
這個SQL只會執行2次SQUARE函數，而不管表T有多少行。
因此給出結論：當我們自己建立函數時，如果能夠確定該函數是確定的，那就一定要加上deterministic屬性，這樣在where條件中使用該函數會提高一大截效能！否則，你寫的函數將來被多少人使用就是害了多少人！