Incorrect query results using a function based index

I recently encountered a situation where a Function-Based Index would be disabled and there was not a known reason. This particular FBI performs a query against another table (which is actually a violation of the DETERMINISTIC requirement but was still allowed). So I set up a small test trying to find the reason an FBI would be disabled.

Good news - in the test I performed I did not disable an index
Bad news - since it did not, I don't know what disabled the index
Really, REALLY BAD NEWS - using a query in a function-based index opens the door to incorrect results.

I show my work at the bottom of the blog so you can see exactly what is happening. There are a few more conditions I need to test (index rebuild, disable/reenable the index) and if you have other ideas, suggestions, please include them in your comments.

I created two tables, t_emp & t_dept, that were copies of emp & dept, respectively. There are not any integrity constraints (other than not null) nor indexes. I then created a function that accepted the department number (deptno) and returned the department name (dname). This mirrors the function that DAFY is using for the disabled FBI.

When I created the index, the index value is the t_dept.dname. Here is a list of some of the tests and the impact on the index.
1) Insert a record into t_emp with a deptno value not in t_dept - no error and FBI is enabled.
2) Delete a record from t_dept that has records with the same deptno value in t_emp - no error and FBI is enabled.
3) Truncate t_dept - no error and FBI is enabled.
4) Recreate t_dept as in the original process - no error and FBI is enabled.

I did not find a triggering event to disable the index. However, I found something I consider more of a problem.

When updating the dname in t_dept, the FBI is not updated. This means that incorrect data is returned. If a new record is inserted, then the FBI value reflects the new dname. If the deptno value in t_emp is updated...the FBI is *NOT* updated! Querying the t_emp table using the function created, which is the way FBIs are intended to be used, uses the FBI and returns what are now incorrect results.

SQL> create table t_emp as select * from emp;
SQL> create table t_dept as select * from dept;

SQL> CREATE OR REPLACE FUNCTION get_dept_name
2 ( p_deptno IN t_emp.deptno%TYPE)
3 RETURN t_dept.dname%TYPE
4 DETERMINISTIC
5 IS
6 l_dname t_dept.dname%TYPE;
7 CURSOR c_dname IS
8 SELECT dname
9 FROM t_dept
10 WHERE deptno = p_deptno;
11 BEGIN
12 OPEN c_dname;
13 FETCH c_dname INTO l_dname;
14 CLOSE c_dname;
15 RETURN l_dname;
16 END get_dept_name;
17 /
SQL> show error
No errors.
SQL> CREATE INDEX ix_dname ON t_emp (get_dept_name(deptno))
2 /
SQL> SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED


SQL> select * from t_dept;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL> insert into t_emp (empno, deptno) values (1000, 10);
SQL> commit;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> insert into t_emp (empno, deptno) values (1001, 50)
2 /
SQL> commit;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED
SQL> delete from t_dept where deptno = 10;
SQL> commit;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED
SQL> select * from t_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10
1001 50
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> truncate table t_dept;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> drop table t_dept;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> create table t_dept as select * from dept;
SQL> SELECT index_name, index_type, funcidx_status
2 FROM user_indexes
3 WHERE table_name = 'T_EMP';

INDEX_NAME INDEX_TYPE FUNCIDX_
------------------------------ --------------------------- --------
IX_DNAME FUNCTION-BASED NORMAL ENABLED

SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10

SQL> update t_dept set dname = 'TEST' where deptno = 10;
SQL> commit;
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10

SQL> insert into t_emp (empno, deptno) values (1002, 10);
SQL> commit
2 /
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10
SQL> select * from t_emp where get_dept_name(deptno) = 'TEST'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1002 10
SQL> update t_emp set deptno = deptno;
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10
SQL> update t_emp set deptno = 10 where deptno = 10;
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10

SQL> set autotrace on explain
SQL> select * from t_emp where get_dept_name(deptno) = 'ACCOUNTING'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1000 10

Execution Plan
----------------------------------------------------------
Plan hash value: 4230458767

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_DNAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - access("SCOTT"."GET_DEPT_NAME"("DEPTNO")='ACCOUNTING')
SQL> select * from t_emp where get_dept_name(deptno) = 'TEST'
2 /

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
1002 10

Execution Plan
----------------------------------------------------------
Plan hash value: 4230458767

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T_EMP | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_DNAME | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - access("SCOTT"."GET_DEPT_NAME"("DEPTNO")='TEST')

Note
-----
- dynamic sampling used for this statement