ITPub博客

首页 > IT基础架构 > 网络安全 > FUNCTION BASED INDEXES

FUNCTION BASED INDEXES

原创 网络安全 作者:wilson2006 时间:2019-04-16 21:03:04 0 删除 编辑

前两天查资料看到的,
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.

[@more@]

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.

Requirements
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 8.1.0.0.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.

See Also:
"Function-Based Indexes"

实际中发现设置以下参数后,
Alter Session Set query_rewrite_enabled='FALSE' ;
Alter Session Set query_rewrite_integrity='STALE_TOLERATED' ;
函数索引的创建和使用都没有报任何的错误,查询执行计划Oracle也会自动使用新创建的函数索引。
e.g.
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’

下面摘录来自http://www.dba-oracle.com/t_query_rewrite_integrity_trusted.htm
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.
莫非是10g之后作的修改?compatible=10.2.0.1.0,这个问题似乎还没有这么简单,留待以后进一步的搞明白。

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

请登录后发表评论 登录
全部评论

注册时间:2018-11-04

  • 博文量
    18
  • 访问量
    13059