sql – What is the alternative to REGEXP_REPLACE to create a functional index?

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<
Post Views: 2
Scroll to Top