Oracle REGEXP_LIKE function to use index table scan?

Question

I am using regexp_like function to search specific patterns on a column. But, I see this query is not taking the index created on this column instead going for full table scan. Is there any option to create function based index for regexp_like so that my query will use that index? Here, the pattern SV4889 is not constant expression but it will vary every time.

select * from test where regexp_like(id,'SV4889')

If you are constantly searching for different patterns then using an index will tend to be problematic. Depending on exactly what form your data takes, it is possible that you want to create an Oracle Text index and use Text queries instead of `regexp_like`. That would depend on whether what you're looking for can be read as separate words by Oracle Text. If you're looking for "SV4889" inside a larger string, i.e. "1234ASV4889887Y", you're probably stuck. If the string is more of the form "1234A SV8889 887Y", you can probably use Oracle Text. — Justin Cave, Apr 06 '21 at 11:24

score 3 · Answer 1 · answered Apr 06 '21 at 11:22

Yup. Regular expressions do not use indexes. What can you do?

Well, if you are just looking for equality, then use equality:

where id = 'SV4889'

This will use an index on (id).

If you are looking for a leading value, then use like:

where id like 'SV4889%'

This will use an index because the wildcard is at the end of the pattern.

If you are storing multiple values in the column, say 'SV4889,SV4890' then fix your data model. It is broken! You should have another table with one row per id.

Finally, if you really need more sophisticated full text capabilities, then look into Oracle's support for full text indexes. However, such capabilities are usually not needed on a column called id.

score 1 · Answer 2 · answered Apr 06 '21 at 12:09

You can add a virtual column to your table to determine if the substring you're interested in exists in the field, then index the virtual column. For example:

ALTER TABLE TEST
  ADD SV4889_FLAG CHAR(1)
    GENERATED ALWAYS AS (CASE
                           WHEN REGEXP_LIKE(ID,'SV4889') THEN 'Y'
                           ELSE 'N'
                         END) VIRTUAL;

This adds a field named SV4889_FLAG to your table which will contain Y if the text SV4889 exists in the ID field, and N if it doesn't. Then you can create an index on the new field:

CREATE INDEX IDX_TEST_SV4889_FLAG
  ON TEST (SV4889_FLAG);

So to determine if a row has 'SV4889' in it you can use a query such as:

SELECT *
  FROM TEST
  WHERE SV4889_FLAG = 'Y'

db<>fiddle here

Oracle REGEXP_LIKE function to use index table scan?

2 Answers2