Oracle Database Discussions

Hello,

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 9.2.0.6 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=4950:8:::::F4950_P8_DISPLAYID: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
varchar2 DETERMINISTIC
as
begin
return upper(p_str);
end;
/

=>
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?
Other comments?


Regards,

Tal Olier (tal.olier@gmail.com)