ORA-30553

创建自定义函数索引报如下错误

报找个错误的原因是：在自定义函数中缺少DETERMINISTIC

联机文档如下说明:

Tells the optimizer that the function returns the same value whenever it is invoked with the same parameter values

 (if this is not true, then specifying DETERMINISTIC causes unpredictable results). If the function was invoked previously with

 the same parameter values, the optimizer can use the previous result instead of invoking the function again.

Do not specify DETERMINISTIC for a function whose result depends on the state of session variables or schema objects,

because results might vary across invocations. Instead, consider making the function result-cached (see "Making Result-Cached Functions

  Handle Session-Specific Settings" and "Making Result-Cached Functions Handle Session-Specific Application Contexts").

Only DETERMINISTIC functions can be invoked from a function-based index or a materialized view that has query-rewrite enabled.

For more information and possible limitations of the DETERMINISTIC option, see "CREATE FUNCTION Statement".

Restriction on DETERMINISTIC

You cannot specify DETERMINISTIC for a nested function.

根据官网说明修改函数和包头说明:

包体修改如下 ：

 FUNCTION Get_Is_Allow_Contract(

pm_no_         IN NUMBER,

pm_revision_   IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC

IS

包头修改如下 ：

 FUNCTION Get_Is_Allow_Contract(

 pm_no_         IN NUMBER,

 pm_revision_   IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;

 创建基于函数的索引:

 create index pm_test_1 on PM_ACTION_CALENDAR_PLAN_TAB(PM_ACTION_CALENDAR_PLAN_API.Get_Is_Allow_Contract(pm_no,pm_revision)) parallel 16;

 alter index pm_test_1 noparallel;

测试性能语句:

select /*+ noparallel*/

 OBJID,

 OBJVERSION,

 OBJSTATE,

 OBJEVENTS,

 IFSAPP.PM_ACTION_CALENDAR_PLAN_API.Get_Site(PM_NO, PM_REVISION),

 PM_NO,

 PM_REVISION,

 IFSAPP.PM_ACTION_API.GET_DESCRIPTION(PM_NO, PM_REVISION),

 IFSAPP.PM_ACTION_API.Get_Rounddef_Id(pm_no, PM_REVISION),

 IFSAPP.PM_ACTION_API.Get_Description(pm_no, PM_REVISION),

 IFSAPP.PM_ACTION_API.Get_Action_Code_Id(pm_no, PM_REVISION),

 IFSAPP.PM_ACTION_API.Get_Action_Descr(pm_no, PM_REVISION),

 SEQ_NO,

 PLANNED_DATE,

 PLANNED_WEEK,

 MCH_CODE_CONTRACT,

 MCH_CODE,

 IFSAPP.Maintenance_Object_Api.Get_Mch_Name(MCH_CODE_CONTRACT, MCH_CODE),

 COMPLETION_DATE,

 REMARK,

 SIGNATURE,

  IFSAPP.PERSON_INFO_API.Get_Name(SIGNATURE),

 STATE,

 IFSAPP.PM_ACTION_CALENDAR_PLAN_API.Get_Mch_Code(PM_NO, PM_REVISION),

 IFSAPP.PM_ACTION_CALENDAR_PLAN_API.Get_Mch_Desc(PM_NO, PM_REVISION),

 COMPANY,

 IFSAPP.PM_ACTION_CALENDAR_PLAN_API.Get_PM_TYPE(PM_NO, PM_REVISION),

 IFSAPP.WORK_ORDER_API.Get_Wo_Status_Id(WO_NO),

 PM_GENERATEABLE_DB

 from IFSAPP.REGULAR_WORK;

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31134212/viewspace-2100704/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31134212/viewspace-2100704/