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

Source: Internet
Author: User
Tags create index

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.