Какая альтернатива REGEXP_REPLACE для создания функционального индекса?

Question

После миграции на Oracle 18c Enterprise Edition, создание функционального индекса (FBI) заканчивается ошибкой.

Так выглядят DDL для создания индекса:

CREATE INDEX my_index ON my_table(
    UPPER (REGEXP_REPLACE ("DEPT_NUM", '[^[:alnum:]]', NULL, 1, 0)));

Получаю следующую ошибку:

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.

Это известный баг в 18c?
Если такая функция больше не поддерживается для FBI, то какой существует другой путь обеспечить такой функционал для индекса?

Answer 1

До выпуска 12.2.0.1 было возможно создать функциональный индекс (FBI) с функциями, результат которых был не детерминирован.

Например в 11.2.0.1 выглядело, что работает:

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.

Тоже самое в 12.2.0.1 закончится ошибкой при создании индекса:

ORA-01743: only pure functions can be indexed

Что касется пользовательских функций, тут всё ясно, и в 11.2.0.2, и в 12.2.0.1 явно указанно, что:

Any user-defined function referenced in column_expression must be declared as DETERMINISTIC.

В случае же внутренней SQL функции REGEXP_REPLACE Oracle признал, что её применение в FBI это баг и начиная с выпуска 12.2.0.1 устранил его. Подробнее @ConnorMcDonald на Спроси Тома:

Bug 20804063 ORA-1499 as REGEXP_REPLACE is allowed to be used in Function-based indexes (FBI)

И действительно, результат этой функции зависит от NLS настроек сессии, т.е. недетрминирован. Например, результат одного и того же запроса с умлаутами будет отличаться:

alter session set nls_language = 'german';  --> null
alter session set nls_language = 'english'; --> ÄÖÜ

select (regexp_replace ('ABCÄÖÜ','[A-Z]*')) res from dual;

Одно из возможных решений, ограничится для идентификаторов отделов только ASCII символами. В этом случае, достаточно логику обернуть в пользовательскую функцию с детерминированным результатом. Например вот так:

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<

БЛОГ НА HUSL

Какая альтернатива REGEXP_REPLACE для создания функционального индекса?

Ответы (1 шт):