Oracle Technology: Function-based index creation based on custom functions

An index was created in Oralce for the constructor, and the result was unsuccessful.

Source:create Index idx_t_sp_twotypestat_0_f on T_sp_twotypestat_0 (F_dateadd (yearmonth,12,2));

The Err:the function is not deterministic.

Let's take a look at this why?

A random test can reproduce the problem, and we create a function (this example function is used to make a 16 to 10 conversion):

At this point, create the index and get the following error message:

More Wonderful content: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

If you need to create an index based on a custom function, then we need to specify the deterministic parameter:

The index can be created at this time:

Oracle interprets this argument in this way:

The hint deterministic helps the optimizer avoid redundant function calls. If a stored function is called previously with the same arguments, the optimizer can elect to use the previous result. The function result is should not depend to the state's session variables or schema objects. Otherwise, results might vary across calls. Only deterministic functions can is called from a function-based index or a materialized view that has Query-rewrite Ed.

Author: 51cto Oracle small-mixed son