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