Implementing Selective Uniqueness-Indexes

Another useful technique with function-based indexes is to use them to enforce certain types of complex constraints. For example, suppose you have a table with versioned information, such as a projects table. Projects have one of two statuses: either ACTIVE or INACTIVE. You need to enforce a rule such that “Active projects must have a unique name; inactive projects do not.” That is, there can only be one active “project X,” but you could have as many inactive project Xs as you like.

The first response from a developer when they hear this requirement is typically, “We’ll just run a query to see if there are any active project Xs, and if not, we’ll create ours.” If you read Chapter 7, you understand that such a simple implementation cannot work in a multiuser environment. If two people attempt to create a new active project X at the same time, they’ll both succeed. We need to serialize the creation of project X, but the only way to do that is to lock the entire projects table (not very concurrent) or use a function-based index and let the database do it for us.

Building on the fact that we can create indexes on functions, that entire null entries are not made in B*Tree indexes, and that we can create a UNIQUE index, we can easily do the following:
Create unique index active_projects_must_be_unique
On projects ( case when status = ‘ACTIVE’ then name end );

This will do it. When the status column is ACTIVE, the NAME column will be uniquely indexed. Any attempt to create active projects with the same name will be detected, and concurrent access to this table is not compromised at all.

Caveat Regarding ORA-01743
One quirk I have noticed with function-based indexes is that if you create one on the built-in function TO_DATE, it will not succeed in some cases:
$ sqlplus eoda/foo@PDB1
SQL> create table t ( year varchar2(4) ); Table created.
SQL> create index t_idx on t( to_date(year,’YYYY’) ); create index t_idx on t( to_date(year,’YYYY’) )

ORA-01743: only pure functions can be indexed
This seems strange, since we can sometimes create a function using TO_DATE, like so:
SQL> create index t_idx on t( to_date(’01’||year,’MMYYYY’) ); Index created.

The error message that accompanies this isn’t too illuminating either:
SQL> !oerr ora 1743 01743, 00000, “only pure functions can be indexed”
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL
// expressions must not use SYSDATE, USER, USERENV(), or anything
// else dependent on the session state. NLS-dependent functions
// are OK.

We are not using SYSDATE. We are not using the user environment (or are we?). No PL/SQL functions are used, and nothing about the session state is involved. The trick lies in the format we used: YYYY. That format, given the same exact inputs, will return different answers depending on what month you call it in. For example, anytime in the month of May the YYYY format will return May 1, in June it will return June 1, and so on:
SQL> select to_char( to_date(‘2015′,’YYYY’), ‘DD-Mon-YYYY HH24:MI:SS’ ) from dual;
TO_CHAR(TO_DATE(‘200

It turns out that TO_DATE, when used with YYYY, is not deterministic! That is why the index cannot be created: it would only work correctly in the month you created it in (or inserted/updated a row in). So, it is due to the user environment, which includes the current date itself.

To use TO_DATE in a function-based index, you must use a date format that is unambiguous and deterministic—regardless of what day it is currently.