Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Function-based index invalidation due to recompile

Samo DecmanSep 21 2015 — edited Sep 21 2015

Hello,

one of our customers has two function-based indexes which fall in to "disabled" state in certain situations. After looking closer at the situation there are some things that by my opinion differ from what I would expect from a function-based index. Because I'm unable to find anything on metalink (or I'm not asking the correct question) I would appreciate second opinion from you.

To keep things simple I provided an example to illustrate the behaviour. I'm using Oracle 12.1.0.2, though this can be also reproduced on 10.2 and 11.2 versions.

This is my environment and three parameters I find relevant to the discussion:

SQL> select banner from v$version;

BANNER

----------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production

CORE    12.1.0.2.0      Production

TNS for Linux: Version 12.1.0.2.0 - Production

NLSRTL Version 12.1.0.2.0 - Production

SQL> show parameter remote_dependencies

NAME                                 TYPE        VALUE

------------------------------------ ----------- ----------

remote_dependencies_mode             string      TIMESTAMP

SQL> show parameter query_rewrite

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------------

query_rewrite_enabled                string      TRUE

query_rewrite_integrity              string      enforced

Test case:

SQL> CREATE OR REPLACE FUNCTION f1 (p_string IN VARCHAR2)

  2  RETURN VARCHAR2

  3  DETERMINISTIC

  4  IS

  5  BEGIN

  6    RETURN lower(p_string);

  7  END f1;

  8  /

Function created.

SQL> CREATE TABLE tmp_t1 (a_string VARCHAR2(10));

Table created.

SQL> INSERT INTO tmp_t1 VALUES ('a');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE INDEX x1_tmp_t1 ON tmp_t1(f1(a_string));

Index created.

SQL> set linesize 80;

SQL> column index_name format a10;

SQL> SELECT index_name, index_type, status, funcidx_status

  2    FROM user_indexes;

INDEX_NAME INDEX_TYPE                  STATUS   FUNCIDX_

---------- --------------------------- -------- --------

X1_TMP_T1  FUNCTION-BASED NORMAL       VALID    ENABLED

We have our table and our function-based index that basically converts values into lowercase. From here on things get a little weird. What happens with function-based index if underlying function gets recompiled? I always thought (and what is also stated in Concepts and Usage of Function Based Indexes (Doc ID 66277.1)) that index would change its status to "disabled". Here is a snippet from aforementioned Doc ID:

The index is dependent on the state of PL/SQL function. The index can be

invalidated or made unusable by changes to the function. The index is marked

DISABLED, if there are changes to the function or the function is recreated.

The time-stamp of the function is used to validate the index.

To enable the index after the function is created, if the signature of the

function is same as before:

   ALTER INDEX  ENABLE;

If the signature of the functions is changed, to make the changes effective

in the index, the index need to be revalidated to make it valid and enable.

   ALTER INDEX  REBUILD;

It seems this is not the case as index remains valid and enable.

SQL> alter function f1 compile;

Function altered.

SQL> SELECT index_name, index_type, status, funcidx_status

  2    FROM user_indexes;

INDEX_NAME INDEX_TYPE                  STATUS   FUNCIDX_

---------- --------------------------- -------- --------

X1_TMP_T1  FUNCTION-BASED NORMAL       VALID    ENABLED

OK, by explicitly recompiling function F1 only timestamp changed. What if we replace the function altogether and we change the output of the function - for example let's switch from function LOWER to function UPPER in F1's body. Again, this is the change that I thought would not only disable the function-based index but also force its rebuild. At least, this is how I understand explanation in Doc ID).

SQL> CREATE OR REPLACE FUNCTION f1 (p_string IN VARCHAR2)

  2  RETURN VARCHAR2

  3  DETERMINISTIC

  4  IS

  5  BEGIN

  6    RETURN UPPER(p_string);

  7  END f1;

  8  /

Function created.

SQL> SELECT index_name, index_type, status, funcidx_status

  2    FROM user_indexes;

INDEX_NAME INDEX_TYPE                  STATUS   FUNCIDX_

---------- --------------------------- -------- --------

X1_TMP_T1  FUNCTION-BASED NORMAL       VALID    ENABLED

Not expected either. Due to "create or replace" function F1 never did go through an "invalid" phase which might be required for index becoming unusable? What about queries against table TMP_T1? Does optimizer still uses index access or not? What about the results?

SQL> EXPLAIN PLAN SET statement_id='s1' FOR

  2  SELECT a_string, f1(a_string) as f1_a_string, f1('a') as f1_literal

  3    FROM tmp_t1

  4   WHERE f1(a_string) = 'a';

Explained.

SQL> SELECT * from table(dbms_xplan.display(statement_id=>'s1'));

PLAN_TABLE_OUTPUT                                                                                                  

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 3133804460                                                                                        

                                                                                                                   

-------------------------------------------------------------------------------------------------                  

| Id  | Operation                           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |                  

-------------------------------------------------------------------------------------------------                  

|   0 | SELECT STATEMENT                    |           |     1 |  2024 |     2   (0)| 00:00:01 |                  

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TMP_T1    |     1 |  2024 |     2   (0)| 00:00:01 |                  

|*  2 |   INDEX RANGE SCAN                  | X1_TMP_T1 |     1 |       |     1   (0)| 00:00:01 |                  

-------------------------------------------------------------------------------------------------

Index is indeed used by the optimizer, let's see the results.

SQL> column f1_a_string format a15;

SQL> column f1_literal format a15;

SQL> SELECT a_string, f1(a_string) as f1_a_string, f1('a') as f1_literal

  2    FROM tmp_t1

  3   WHERE f1(a_string) = 'a';

A_STRING   F1_A_STRING     F1_LITERAL

---------- --------------- ---------------

a          a               A

A_STRING = value from the table

F1_A_STRING = value of f1(a_string) but the value is not evaluated because it is taken from an index, hence lowercase value (remember, at the time index was created function did return lowercase values)

F1_LITERAL = value of newly evaluated f1 function, using literal instead of value in the table.

Predicate f1(a_string) = 'a' should return no rows, because no uppercase character equals 'a'. Query with f1(a_string)='A' should return one row but it doesn't.

SQL> SELECT a_string, f1(a_string) as f1_a_string, f1('a') as f1_literal

  2    FROM tmp_t1

  3   WHERE f1(a_string) = 'A';

no rows selected

Anyone knows if this is an expected behaviour? And, is there any way to disable function-based index whenever signature of underlying function is changed? The parameter query_rewrite_integrity=enforced from

DOC ID 66277.1 doesn't seem to do the trick:

  c) Session variables

  ~~~~~~~~~~~~~~~~~~~~

        QUERY_REWRITE_ENABLED(true,false),

        QUERY_REWRITE_INTEGRITY (trusted, enforced, stale_tolerated)

        determines the optimizer to use the function-based index with

        expressions using SQL functions, user defined functions.

 

        TRUSTED: Oracle allows rewrites using relationships that have

                 been declared.

        ENFORCED: Oracle enforces and gaurantees consistency and integrity.

        STALE_TOLERATED: Oracle allows rewrites using unenforced relation ships.

         Used in case of materialized views.

            

        Set session variable for cost based optimizer to choose the

        function-based index

Regards,

Samo

This post has been answered by Jonathan Lewis on Sep 21 2015
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 19 2015
Added on Sep 21 2015
8 comments
3,470 views