Database Reference
In-Depth Information
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:
EODA@ORA12CR1> create table t ( year varchar2(4) );
Table created.
EODA@ORA12CR1> create index t_idx on t( to_date(year,'YYYY') );
create index t_idx on t( to_date(year,'YYYY') )
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
This seems strange, since we can
sometimes
create a function using
TO_DATE
, like so:
EODA@ORA12CR1> create index t_idx on t( to_date('01'||year,'MMYYYY') );
Index created.
The error message that accompanies this isn't too illuminating either:
EODA@ORA12CR1> !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:
EODA@ORA12CR1> select to_char( to_date('2015','YYYY'),
2 'DD-Mon-YYYY HH24:MI:SS' )
3 from dual;
TO_CHAR(TO_DATE('200
--------------------
01-May-2015 00:00:00
