Unanswered: dilemma regarding function based indexes
I have a dilemma regarding function based indexes.
I have read Note:66277.1 on the Metalink discussing thoroughly the subject of “Concepts and Usage of Function Based Indexes”.
This doc was revised on 30-May-2006 so I was sure it referred to 9i and 10g.
This doc as well as other docs on the web claim that in order to use FBI (function based indexes) one must set the following parameters (can be done also at session level)
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = TRUSTED
Also the schema that is owner of the FBI should be granted with QUERY REWRITE sys priv and statistics should be collected since FBI is usable only by CBO (cost based optimizer).
I have tested it and it works, my problem was that it worked
(1) Without granting the QUERY REWRITE to the owning schema.
(2) QUERY_REWRITE_ENABLED was set to false.
(3) QUERY_REWRITE_INTEGRITY was set to enforced.
I have conducted my tests on 220.127.116.11 and found no evidence in the docs (10g) saying the above is required or not.
I found at http://asktom.oracle.com/pls/ask/f?p...:1197786003246 the following:
“Oracle9iR2 relaxed this so that the FBI on the *builtin* function may be used.”
so I have tested it with my own function:
create or replace function upper2( p_str in varchar2 ) return
Also (yes you guessed right), without any privilege granted nor parameter setting the optimizer picked my FBI.
Can anyone refer me to a place documenting this behavior as a correct one?
You are not correct. A function based index is used if the same function is referenced in the query. For example if you have an index on UPPER(EMP_NAME) then you would have to reference upper(emp_name) in the query. The parameters that you specify is to force the use of materialized views instead of the actual table.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Questions about the options required in 10g related to Function Based Indexes, and the correct
behaviors associated with them.
These requirements are no longer true in 10g. This has already clarified by
development in the Bug 3999326 which is available on metalink.
For the creation of a function-based index in your own schema, you must be
granted the QUERY REWRITE system privileges. To create the index in another
schema or on another schema's tables, you must have the CREATE ANY INDEX
and GLOBAL QUERY REWRITE privileges.
You must have the following initialization parameters defined to create a
QUERY_REWRITE_INTEGRITY set to TRUSTED
QUERY_REWRITE_ENABLED set to TRUE
COMPATIBLE set to 18.104.22.168.0 or a greater value
Additionally, 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, since NULL values are not stored in indexes.
However, in 22.214.171.124 patchset, these prerequisites do not apply and one can
create function-based indexes without any of the above to be true. This is not
the case in 126.96.36.199, not in 8.1.7.
Reference: Oracle 9i R2 Administrators Guide
So as mentioned above that is why you didnt have any errors
Please back to us if any further information is need, and we will be pleased to
assist you further.