Function-Based Index Dependencies
Function-based indexes depend on functions used in the expression that defines the index. If a PL/SQL function or package function is changed, then the index is marked as disabled.
This section discusses requirements for function-based indexes and what happens when a function is changed in any manner, such as when it is dropped or privileges to use it are revoked.
To create a function-based index:
The following initialization parameters must be defined:
QUERY_REWRITE_INTEGRITY must be set to TRUSTED(ENFORCED也可以)
QUERY_REWRITE_ENABLED must be set to TRUE
COMPATIBLE must set to 184.108.40.206.0 or a greater value
The user must be granted CREATE INDEX and QUERY REWRITE, or CREATE ANY INDEX and GLOBAL QUERY REWRITE.
To use a function-based index:
The table must be analyzed after the index is created.
The query must be guaranteed not to need any NULL values from the indexed expression, because NULL values are not stored in indexes.
The following sections describe additional requirements.
Alter Session Set query_rewrite_enabled='FALSE' ;
Alter Session Set query_rewrite_integrity='STALE_TOLERATED' ;
create index ind_fnc_test on test (to_char(created,'yyyymmddhh24miss')) ;
select * from dba_indexes where index_name='IND_FNC_TEST' ;
index_type 将是‘FUNCTION-BASED NORMAL’
First, it's important to note that query_rewrite_integrity and query_rewrite_enabled are required to use materialized views and function-based indexes.
There are three acceptable values for query_rewrite_integrity:
enforced (default) - Presents materialized view with fresh data
trusted - Assumes that the materialized view is current
stale_tolerated - Presents materialized view with both stale and fresh data
The problem here is about the "freshness" of the data. One of the great benefits if Materialized Views is that you don't have to choose to constantly keep them synchronized with their base tables. On many systems (especially Business Intelligence), having the last days data is not relevant, and the materialized view can be refreshed nightly via a schedule dbms_scheduler job. In this case query_rewrite_integrity=stale_tolerated is wonderful.
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/768134/viewspace-1004881/，如需转载，请注明出处，否则将追究法律责任。