FBI = function based index

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

  1. rebuild your index after create function. You could find the candidates by looking at the last ddl time and dependencies


    2. 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.

  2. file an SR and encourage Oracle to test features before making them available
  3. stop using FBI immediately