Let me today tell you, I now hate FBI for real.
Let’s start with an easy working example
SQL> CREATE TABLE t(x NUMBER PRIMARY KEY)
Table created.
SQL> insert into t(x) values (1)
1 row created.
SQL> insert into t(x) values (2)
1 row created.
SQL> insert into t(x) values (3)
1 row created.
SQL> commit
Commit complete.
SQL> CREATE FUNCTION f (x NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN x * x;
END;
Function created.
SQL> CREATE INDEX i
ON t (f (x))
Index created.
SQL> select * from t where f(x)=4
X
----------
2
Execution Plan
------------------------------------------------
0 SELECT STATEMENT
1 0 TABLE ACCESS BY INDEX ROWID BATCHED T
2 1 INDEX RANGE SCAN I
Okay, this is a nice-working example. I can use where f(x)=4.
A non-fbi code would be something like
SQL> create or replace type tn as table of number;
Type created.
SQL> create or replace function f2(y number)
return tn deterministic is
begin
if (y<0) then return null; end if;
return tn (sqrt(y), -sqrt(y));
end;
Function created.
SQL> select * from t where x member of f2(4)
X
----------
2
Execution Plan
------------------------------------
0 SELECT STATEMENT
1 0 INDEX FULL SCAN SYS_C0026437
The reverse function is somehow more challenging to code, but the benefit is enormous, I have no more fbi.
What’s wrong with fbi?
First example : I recreate my function:
SQL> DROP FUNCTION f
Function dropped.
SQL> CREATE FUNCTION f (x NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN power(x,2);
END;
Function created.
SQL> select * from t where f(x)=4
*
Error at line 0
ORA-30554: function-based index I is disabled
SQL> SELECT object_type, object_name, status
FROM user_objects
WHERE object_name IN ('F','I')
OBJECT_TYPE OBJECT_NAME STATUS
----------------------- ------------ -------
INDEX I VALID
FUNCTION F VALID
SQL> SELECT index_name,
table_name,
index_type,
status,
funcidx_status
FROM user_indexes
WHERE index_name = 'I'
INDEX TABLE INDEX_TYPE STATUS FUNCIDX_STATUS
----- ----- -------------- ------ --------------
I T FUNCTION-BASED VALID DISABLED
Remember this error. ORA-30554. And this not-so-well-known column, USER_INDEXES.FUNCIDX_STATUS. The behavior is pretty agressive, every object is valid, but you can no longer select from the table.
A small parenthese. We all know about unusable indexes. Index often get unusable due to partition maintenance and the like.
SQL> create table t2(x number)
Table created.
SQL> insert into t2 values (1)
1 row created.
SQL> create index i2 on t2(x) unusable
Index created.
SQL> SELECT index_name,
table_name,
status
FROM user_indexes
WHERE index_name = 'I2'
INDEX TABLE STATUS
----- ----- ---------
I2 T2 UNUSABLE
SQL> insert into t2 values (2)
1 row created.
SQL> select * from t2 where x=2
X
----------
2
Execution Plan
---------------------------------
0 SELECT STATEMENT
1 0 TABLE ACCESS FULL T2
The index is not unused, but it prevents neither INSERT nor SELECT.
Let’s add a constraint
SQL> alter index i2 rebuild
Index altered.
SQL> alter table t2 add primary key (x)
Table altered.
SQL> alter index i2 unusable
Index altered.
SQL> insert into t2 values (2)
ORA-01502: index 'I2' or partition of such index is in unusable state
SQL> select * from t2 where x=2
X
----------
2
If the index is used by a constraint or is unique, then insert is prevented. But no select is prevented ever.
Okay, frequent readers may wonder why I did DROP FUNCTION and CREATE FUNCTION instead of CREATE OR REPLACE FUNCTION.
Fine, let’s try.
SQL> CREATE or replace FUNCTION f (x NUMBER)
RETURN NUMBER
DETERMINISTIC
IS
BEGIN
RETURN power(x,2);
END;
Function created.
SQL> alter index i rebuild
Index altered.
SQL> alter index i enable
Index altered.
SQL> select x, f(x) from t where f(x)=4
X F(X)
---------- ----------
2 4
SQL> create or replace function f(x number)
return number deterministic is
begin
return 1;
end;
Function created.
SQL> select x, f(x), f(2) from t where f(x)=4
X F(X) F(2)
---------- ---------- ----------
2 4 1
Oh my goodness, select returns completly wrong result, but the index is valid and enabled.
There is more than way to solve this
- rebuild your index after create function. You could find the candidates by looking at the last ddl time and dependencies
- file an SR and encourage Oracle to test features before making them available
- stop using FBI immediately
SQL> select name from user_dependencies d
where referenced_type = 'FUNCTION'
and type = 'INDEX' and
(
select last_ddl_time
from user_objects i
where i.object_name=d.name
) < (
select last_ddl_time
from user_objects f
where f.object_name=d.referenced_name
)
NAME
-----
I
SQL> alter index i rebuild
Index altered.
SQL> select x, f(x), f(2) from t where f(x)=4
no rows selected.
SQL> select x, f(x), f(2) from t where f(x)=1
X F(X) F(2)
---------- ---------- ----------
1 1 1
2 1 1
3 1 1
SQL> select name from user_dependencies d
where referenced_type = 'FUNCTION'
and type = 'INDEX' and
(
select last_ddl_time
from user_objects i
where i.object_name=d.name
) < (
select last_ddl_time
from user_objects f
where f.object_name=d.referenced_name
)
no rows selected.