Oracle技術：基於自訂函數的Function-Based索引建立

在oralce中給自建函數建立索引，結果不成功。

source:Create Index IDX_T_SP_TWOTYPESTAT_0_f On T_SP_TWOTYPESTAT_0(f_dateadd(yearmonth,12,2));

err:the function is not deterministic.

我們看一下這是為什麼?

隨便一個測試可以再現這個問題,我門建立一個函數(本範例函數用於進行16進位向10進位轉換):

此時建立索引,獲得如下錯誤資訊:

更多精彩內容：http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

如果需要建立基於自訂函數的索引,那麼我們需要指定deterministic參數:

此時建立索引即可:

Oracle這樣解釋這個參數:

The hint DETERMINISTIC helps the optimizer avoid redundant function calls. If a stored function was called previously with the same arguments, the optimizer can elect to use the previous result. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled.

作者：51cto Oracle小混子