Extend Constraints using Function Based Indexes

Overview

Function Based Indexes are a feature virtually every DBA and programmer will be using immediately -- the ability to index functions and use these indexes in query. In a nutshell, this capability allows you to have case insenstive searches or sorts, search on complex equations, and extend the SQL language efficiently by implementing your own functions and operators and then searching on them.

Why to use this feature

• It's easy and provides immediate value.
• It can be used to speed up existing applications without changing any of their logic or queries.
• It can be used to supply additional functionality to applications with very little cost.

So why is it easy and of immediate value? It's easy because it's just a CREATE INDEX statement. The following example shows how you can extend the functionality of constraints with function based indexes.

Extend Constraints using Function Based Indexes

Suppose you have the following rows in a table:

        ID ADATE
---------- ---------
         1
         1 04-JUN-04
         1 04-JUN-04
         1 05-JUN-04
         2
         2 04-JUN-04
         2 05-JUN-04

Now, you are looking for a constraint, which allows duplicate rows, such as ...

        ID ADATE
---------- ---------
         1 04-JUN-04
         1 04-JUN-04

... but not with NULL values for adate, not allowed is therefore:

       ID ADATE
---------- ---------
         1 NULL
         1 NULL

The first idea will be to write a trigger, a constraint cannot be found (CHECK, NOT NULL, UNIQUE). A trigger will do the job, but if you have 100 million rows, this will be a performance problem. The answer is - use a Function Based Index, here is the complete example:

CREATE TABLE demo (id NUMBER, adate DATE);

INSERT INTO DEMO VALUES (1 , NULL);
INSERT INTO DEMO VALUES (2 , NULL);
INSERT INTO DEMO VALUES (1 , SYSDATE);
INSERT INTO DEMO VALUES (1 , SYSDATE);
INSERT INTO DEMO VALUES (2 , SYSDATE);
INSERT INTO DEMO VALUES (1 , SYSDATE+1);
INSERT INTO DEMO VALUES (2 , SYSDATE+1);

Now create the Function Based Index which will do the job:

CREATE UNIQUE INDEX demo_idx
ON demo (DECODE(adate, NULL, id, NULL));

This is a unique index on id when adate is NULL, the index is only indexing rows where by the adate is NULL.

If you have 100 rows in the table, 50 of which have a non-null adate's, the index will have 50 entries. Entirely NULL entries are not made in B*Tree indexes -- hence the DECODE only indexes the rows that have NULL (active) adate's.

DECODE is an IF-THEN-ELSE, row by row:

IF (adate IS NULL) THEN
  Return the Value of ID
ELSE
  Return NULL
END IF

Make a Test:

INSERT INTO DEMO VALUES (1 , NULL);
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEMO_IDX) violated

UPDATE demo SET adate = NULL WHERE id = 2;
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.DEMO_IDX) violated

Conclusion

Traditionally, performing a function on an indexed column in the where clause of a query guaranteed an index would not be used. Oracle introduced Function Based Indexes to counter this problem. Rather than indexing a column, you index the function on that column, storing the product of the function, not the original column data. When a query is passed to the server that could benefit from that index, the query is rewritten to allow the index to be used.

More examples can be found here: