Question:
After migrating to Oracle 18c Enterprise Edition, functional index (FBI) creation fails.
This is how the DDL for creating an index looks like:
CREATE INDEX my_index ON my_table(
UPPER (REGEXP_REPLACE ("DEPT_NUM", '[^[:alnum:]]', NULL, 1, 0)));
I am getting the following error:
ORA-01743: only pure functions can be indexed
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.
Is this a known bug in 18c?
If such functionality is no longer supported for FBI, what other way is there to provide such functionality for the index?
Answer:
Prior to the 12.2.0.1 release, it was possible to create a functional index (FBI) with functions whose result was non-deterministic.
For example, in 11.2.0.1 it looked like it worked:
create table depts (dept_num) as
select cast (column_value as varchar2 (16)) dept_num
from xmlTable ('"12345","ABC123", "XYZ#123"');
create index depts_idx on depts(
upper (regexp_replace ((dept_num), '[^[:alnum:]]', null, 1, 0)));
Index DEPTS_IDX created.
The same in 12.2.0.1 will end with an error when creating the index:
ORA-01743: only pure functions can be indexed
As for user-defined functions, everything is clear here, both in 11.2.0.2 and 12.2.0.1 it is explicitly stated that:
Any user-defined function referenced in column_expression must be declared as DETERMINISTIC .
In the case of the REGEXP_REPLACE
internal SQL function, Oracle has recognized that its use in FBI is a bug and has fixed it since release 12.2.0.1. Read more @ConnorMcDonald on Ask Tom :
Bug 20804063 ORA-1499 as REGEXP_REPLACE is allowed to be used in Function-based indexes (FBI)
Indeed, the result of this function depends on the NLS settings of the session, i.e. non-deterministic. For example, the result of the same query with umlauts will be different:
alter session set nls_language = 'german'; --> null
alter session set nls_language = 'english'; --> ÄÖÜ
select (regexp_replace ('ABCÄÖÜ','[A-Z]*')) res from dual;
One possible solution is to restrict department IDs to ASCII characters only. In this case, it is enough to wrap the logic in a user-defined function with a deterministic result. For example like this:
create or replace function normalizeDeptNum (deptNum varchar2) return varchar deterministic is
begin
if replace (asciistr (deptNum), asciistr (chr (92)), chr (92)) != deptNum then
raise_application_error (
-20000, 'The value in deptNum contains non-ascii char >'||deptNum||'<');
end if;
return upper (regexp_replace (deptNum, '[^[:alnum:]]', null, 1, 0));
end;
/
create index depts_idx on depts (normalizeDeptNum (dept_num));
Index DEPTS_IDX created.
insert into depts (dept_num) values ('AAA//123');
1 row inserted.
insert into depts (dept_num) values ('ÄÄÄ//123');
ORA-20000: The value in deptNum contains non-ascii char >ÄÄÄ//123<