Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    19

    Unanswered: dilemma regarding function based indexes

    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...: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)

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    May 2004
    Posts
    19
    Got an answer from Oracle support:

    19-DEC-06 18:04:31 GMT

    (Update for record id(s): 101017780,101017796)

    QUESTION
    ========
    Questions about the options required in 10g related to Function Based Indexes, and the correct
    behaviors associated with them.



    ANSWER
    ======
    For 10g:
    These requirements are no longer true in 10g. This has already clarified by
    development in the Bug 3999326 which is available on metalink.

    For 9I:
    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
    function-based index:

    QUERY_REWRITE_INTEGRITY set to TRUSTED
    QUERY_REWRITE_ENABLED set to TRUE
    COMPATIBLE set to 8.1.0.0.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 9.2.0.4 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 9.2.0.3, 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.

    Thank You,
    Best Regards,
    Mina Anes

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •