Oracle 18c - Alternative to REGEXP_REPLACE

Question

After migrating to Oracle 18c Enterprise Edition, a function based index fails to create.

Here is my index DDL:

CREATE INDEX my_index ON my_table
(UPPER( REGEXP_REPLACE ("DEPT_NUM",'[^[:alnum:]]',NULL,1,0)))
TABLESPACE my_tbspace
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

I get 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 this function based index is no longer supported, what is another way to write this function?

Orthographize

This may be a bug in 18c. I don't have an installation of this to mess with so I used dbfiddle.uk. I was able to create this dbfiddle, which uses a self-defined deterministic function to create a function-based index, and it worked fine under 11g. When I switch the database to 18c, however, and try to run it the page just hangs. If someone with a functioning 18c installation can check this out it'd be a help. Perhaps function-based indexes aren't allowed in Oracle 18c Express Edition? A doc check was negative. – Chon

@BobJarvis - I just tested you db<>fiddle and it ran on 18c too. – Rete

Well, OK then..! :-) – Chon

Well, i'm getting error ORA-01743... – Orthographize

I see i'm only able to create the index if I create a function. What if I don't want to create a function, how do I rewrite the index DDL? – Orthographize

I didn't get that error (or anything else - the session hung) when I tried creating a function-based index in 18c on dbfiddle. It looks like they now insist that any function used to define an index in 18c be "pure" - and honestly, I don't know what their definition of "pure" is. I've seen the function annotations they mention (RNDS, RNPS, WNDS, WNPS) used in various system packages but I don't know their meaning. – Chon

My guess is that the cause of the error is the use of the UPPER function (which is absent from @BobJarvis dbfiddle). The function obviously depends on the user environment, as stated in the error message you posted. The easiest solution, in my opinion, would be to remove that function from your index, since you stated you don't want to create your own PL/SQL function. – Systemic

@Abra: the question did not originally contain a reference to the UPPER function. – Chon

Since the posted error message states that NLS-dependent functions are OK, maybe try using NLS_UPPER instead of UPPER. – Systemic

I don't think UPPER depends on NLS-Settings and thus do not cause any problem. Bu result of NLS_UPPER depends on NLS-Settings and might cause an issue, depending what "NLS-dependent functions are OK." actually means. – Aretta

@WernfriedDomscheit I don't think UPPER depends on NLS-Settings Exactly. NLS independent functions are not OK (according to the posted Oracle error message). – Systemic

@Systemic While your suggestion of NLS_UPPER did work, it baffles me that NLS_UPPER(REGEXP_REPLACE gives the same error whereas REGEXP_REPLACE (NLS_UPPER creates the index successfully. – Orthographize

Answer 1

The work-around I found easiest was to create the index using NLS_UPPER instead of UPPER:

CREATE INDEX my_index ON my_table
( REGEXP_REPLACE (NLS_UPPER("DEPT_NUM"),'[^[:alnum:]]',NULL,1,0)))
TABLESPACE my_tbspace
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           );

Orthographize

Answer 2

The issue is regexp_replace is not deterministic. The problem arises when changing NLS settings:

alter session set nls_language = english;

with rws as (
  select 'STÜFF' v
  from   dual
)
  select regexp_replace ( v, '[A-Z]+', '#' )
  from   rws;

REGEXP_REPLACE(V,'[A-Z]+','#')   
#Ü#  

alter session set nls_language = german;

with rws as (
  select 'STÜFF' v
  from   dual
)
  select regexp_replace ( v, '[A-Z]+', '#' )
  from   rws;

REGEXP_REPLACE(V,'[A-Z]+','#')   
#     

U-umlaut is at the end of the alphabet in English. But after U in German. So the first statement doesn't replace it. The second does.

In Oracle Database 12.1 and earlier regexp_replace was incorrectly marked as deterministic. 12.2 fixed this by making it non-deterministic.

Consider carefully whether any workarounds manage diacritics correctly.

MOS note 2592779.1 discusses this further.

Resinoid

Answer 3

Most likely the REGEXP_REPLACE causes the problem, see Find out if a string contains only ASCII characters. You can bypass the limitation with a user defined function (thanks to Bob Jarvis)

CREATE OR REPLACE FUNCTION KEEP_ALNUM(strIn IN VARCHAR2)
  RETURN VARCHAR2
  DETERMINISTIC
AS
BEGIN
  RETURN UPPER(REGEXP_REPLACE(strIn, '[^[:alnum:]]', NULL, 1, 0));
END KEEP_ALNUM;
/

CREATE INDEX DEPTS_1 ON DEPTS(KEEP_ALNUM(DEPT_NUM));

Just ensure function has keyword DETERMINISTIC, then you can define even useless functions like below and create a functional index on it

CREATE OR REPLACE FUNCTION SillyValue RETURN VARCHAR2 DETERMINISTIC
AS
BEGIN
  RETURN DBMS_RANDOM.STRING('p', 20);
END;
/

Aretta

Did you miss this comment from the OP? What if I don't want to create a function, how do I rewrite the index DDL? – Systemic

Maybe use REPLACE if the number of Non-alphanumeric characters is limited and known. – Aretta

Answer 4

There are a couple of workarounds.

First one is a hack. As you may know, when you create FBI then Oracle creates hidden column and index on it. Moreover, you even can specify the name of that column instead of FBI expression and Oracle will use an index.

set lines 70 pages 70
column column_name format a15
column data_type format a15

drop table my_table;

create table my_table(dept_num, dept_descr) as select rownum||'*', 'dummy' from dual connect by level <= 1e6; 

create index my_index
   on my_table(upper(regexp_replace(dept_num, '[^[:alnum:]]', null, 1, 0)));

select column_name, data_type from user_tab_cols where table_name = 'MY_TABLE';

explain plan for
select * from my_table where upper(regexp_replace(dept_num, '[^[:alnum:]]', null, 1, 0)) = '666';
select * from table(dbms_xplan.display(format => 'BASIC'));

explain plan for
select * from my_table where SYS_NC00003$ = '666';
select * from table(dbms_xplan.display(format => 'BASIC'));

Output

Table dropped.
Table created.
Index created.

COLUMN_NAME     DATA_TYPE      
--------------- ---------------
DEPT_NUM        VARCHAR2       
DEPT_DESCR      CHAR           
SYS_NC00003$    VARCHAR2       

3 rows selected.
Explain complete.

PLAN_TABLE_OUTPUT                                                     
----------------------------------------------------------------------
Plan hash value: 2234884270                                           

--------------------------------------------------------              
| Id  | Operation                           | Name     |              
--------------------------------------------------------              
|   0 | SELECT STATEMENT                    |          |              
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE |              
|   2 |   INDEX RANGE SCAN                  | MY_INDEX |              
--------------------------------------------------------              

9 rows selected.
Explain complete.

PLAN_TABLE_OUTPUT                                                     
----------------------------------------------------------------------
Plan hash value: 2234884270                                           

--------------------------------------------------------              
| Id  | Operation                           | Name     |              
--------------------------------------------------------              
|   0 | SELECT STATEMENT                    |          |              
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE |              
|   2 |   INDEX RANGE SCAN                  | MY_INDEX |              
--------------------------------------------------------              

9 rows selected.

So to mimic FBI you can create a hidden column and an index on top of it. That can be done in Oracle 11g using dbms_stats.create_extended_stats.

drop index my_index;

begin
   for i in (select dbms_stats.create_extended_stats
             (user, 'my_table', '(upper(regexp_replace("DEPT_NUM", ''[^[:alnum:]]'', null, 1, 0)))') as col_name
               from dual)
   loop
      execute immediate(utl_lms.format_message('alter table %s rename column "%s" to my_hidden_col','my_table', i.col_name));
   end loop;
end;
/

select column_name, data_type from user_tab_cols where table_name = 'MY_TABLE';

create index my_index on my_table(my_hidden_col);

explain plan for
select * from my_table where upper(regexp_replace(dept_num, '[^[:alnum:]]', null, 1, 0)) = '666';
select * from table(dbms_xplan.display(format => 'BASIC'));

explain plan for
select * from my_table where MY_HIDDEN_COL = '666';
select * from table(dbms_xplan.display(format => 'BASIC'));

Output

Index dropped.
PL/SQL procedure successfully completed.

COLUMN_NAME     DATA_TYPE      
--------------- ---------------
DEPT_NUM        VARCHAR2       
DEPT_DESCR      CHAR           
MY_HIDDEN_COL   VARCHAR2       

3 rows selected.
Index created.
Explain complete.

PLAN_TABLE_OUTPUT                                                     
----------------------------------------------------------------------
Plan hash value: 2234884270                                           

--------------------------------------------------------              
| Id  | Operation                           | Name     |              
--------------------------------------------------------              
|   0 | SELECT STATEMENT                    |          |              
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE |              
|   2 |   INDEX RANGE SCAN                  | MY_INDEX |              
--------------------------------------------------------              

9 rows selected.
Explain complete.

PLAN_TABLE_OUTPUT                                                     
----------------------------------------------------------------------
Plan hash value: 2234884270                                           

--------------------------------------------------------              
| Id  | Operation                           | Name     |              
--------------------------------------------------------              
|   0 | SELECT STATEMENT                    |          |              
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| MY_TABLE |              
|   2 |   INDEX RANGE SCAN                  | MY_INDEX |              
--------------------------------------------------------              

9 rows selected.

Starting with Oracle 12c hidden columns are documented so it becomes even more straightforward.

alter table my_table add (my_hidden_col invisible as 
(upper(regexp_replace(dept_num, '[^[:alnum:]]', null, 1, 0))) virtual);
create index my_index on my_table(my_hidden_col);

Another approach is to implement the same logic without a regex.

create index my_index on my_table(
translate(upper(dept_num, '_'||translate(dept_num, '_ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789', '_'), '_')));

But in this case you have to make sure that all expressions with regex in predicates are replaced with the new one.

Fishworm