4 Replies Latest reply: Jan 14, 2010 7:12 PM by 746649 RSS

    Function Based Index And Query Rewrite Enabled

    717568
      Hi,

      I have a question regarding oracle's use of function based indexes i saw a blog post namely:


      The list of steps we needs to be done to use function based indexes:

      You must have the system privelege query rewrite to create function based indexes on tables in your own schema.
      You must have the system privelege global query rewrite to create function based indexes on tables in other schemas
      For the optimizer to use function based indexes, the following session or system variables must be set:
      For Instance,
      QUERY_REWRITE_ENABLED=TRUE
      QUERY_REWRITE_INTEGRITY=TRUSTED
      This can be fixed using the ALTER SESSION or ALTER SYSTEM or in init.ora param file

      alter session set query_rewrite_enabled = true;
      alter session set query_rewrite_integrity = trusted;

      It says that I have to enable query rewrite to be able to use the function index,
      do i really have to enable it?

      we've used it before and we didn't do any settings change.

      Also, what is the purpose of this configuration?

      we're using oracle 9i and cost based optimizer.

      Thanks
      Carlo
        • 1. Re: Function Based Index And Query Rewrite Enabled
          JustinCave
          I know when function-based indexes were initially introduced, this was necessary. Shortly thereafter, however, the restrictions were simplified so that you no longer had to enable query rewrite. I don't recall exactly when that restriction was removed. I'm not sure what version of Oracle you're using (9i could mean anything from 9.0.1 to 9.2.0.4), but I would suspect that you're on a version where the optimizer could more easily use a function-based index while the blog post is referring to an earlier version.

          Justin
          • 2. Re: Function Based Index And Query Rewrite Enabled
            717568
            According to the stats pack, the oracle is compatible with 9.2.0.0.

            Do you think I would still need to do this steps?
            • 3. Re: Function Based Index And Query Rewrite Enabled
              717568
              The oracle version is 9.2.0.8.0 as per checking,
              do i still need to do this?

              thanks
              • 4. Re: Function Based Index And Query Rewrite Enabled
                746649
                Carlo,

                You do not need to set the QUERY_REWRITE parameters in any version of 9i.

                Here I'm setting the values to something other than you asked about in version 9.2.0.8 ...

                sys@orcl> select version from V$instance;

                VERSION
                -----------------
                9.2.0.8.0

                1 row selected.

                sys@orcl> alter session set QUERY_REWRITE_ENABLED=FALSE;

                Session altered.

                sys@orcl> alter session set QUERY_REWRITE_INTEGRITY=ENFORCED;

                Session altered.

                sys@orcl>
                sys@orcl> create table t
                2 as
                3 select * from all_objects;

                Table created.

                sys@orcl>
                sys@orcl> create index fbi on t(lower(object_name));

                Index created.


                Collecting stats ...
                sys@orcl>
                sys@orcl> BEGIN
                2 dbms_stats.gather_table_stats(
                3 ownname => 'SYSTEM',
                4 tabname => 'T',
                5 cascade => true,
                6 method_opt=> 'for all indexed columns'
                7 );
                8 END;
                9 /

                PL/SQL procedure successfully completed.


                sys@orcl> explain plan for
                2 select *
                3 from t
                4 where lower(object_name) = 'adjappvl_seq' ;

                Explained.

                sys@orcl> -- explain plan for 9i
                sys@orcl> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

                PLAN_TABLE_OUTPUT
                ---------------------------------------------------------------------------

                ---------------------------------------------------------------------------
                | Id | Operation | Name | Rows | Bytes | Cost |
                ---------------------------------------------------------------------------
                | 0 | SELECT STATEMENT | | 2 | 232 | 2 |
                | 1 | TABLE ACCESS BY INDEX ROWID| T | 2 | 232 | 2 |
                |* 2 | INDEX RANGE SCAN | FBI | 2 | | 1 |
                ---------------------------------------------------------------------------

                Predicate Information (identified by operation id):
                ---------------------------------------------------

                2 - access(LOWER("T"."OBJECT_NAME")='adjappvl_seq')

                15 rows selected.

                --Note: the query uses the Function based index (FBI) here

                sys@orcl> -- now to verify the parameter's values ...
                sys@orcl> @ckparmi query_rewrite


                NAME TYPE VALUE
                ------------------------------ ---------- ---------------------------------------------
                query_rewrite_enabled 2 FALSE
                query_rewrite_integrity 2 ENFORCED

                2 rows selected.

                Hope this helps,

                Jeff