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?
Prior to the 126.96.36.199 release, it was possible to create a functional index (FBI) with functions whose result was non-deterministic.
For example, in 188.8.131.52 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 184.108.40.206 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 220.127.116.11 and 18.104.22.168 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 22.214.171.124. 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<