Will Oracle use my regexp function-based index?

It was mentioned at the AUSOUG conference by one of the speakers that he couldn’t get the database to use a function-based index based on the regexp functions. I thought this was a little strange so decided to try for myself.

The index has a row for each block in the table. No histogram on it, so it doesn’t know that most of the index is zeroes.What if we just want stuff from the index?SQL> explain plan forselect regexp_instr(string10, ‘XE’)from testtablewhere regexp_instr(string10, ‘XE’) > 0;

Yes, that uses the index. So how do we get the rest of the data from the table? Let’s try something else.SQL> drop index idx_regexp;

We can take advantage of the fact that NULLS are not stored in an index by converting any zeroes (i.e. the regular expression didn’t match) to NULL:SQL> create index idx_regexp_better on testtable(CASE WHEN regexp_instr(string10, ‘XE’) > 0THEN regexp_instr(string10, ‘XE’)ELSE NULL END);

No. Why? Because it can’t use the index, even if we try to force it with a hint. The optimiser doesn’t know it can modify my expression into the one that was used to build the index.SQL> explain plan forselect /*+ index(testtable idx_regexp_better)*/string10, regexp_instr(string10, ‘XE’)from testtablewhere regexp_instr(string10, ‘XE’) > 0;