I have a table of contacts with 1 million registers and now the user wants to search in it by the telephone.

Trying to avoid a fullscan I decided to create a new index in this column, but the trick part is that the data in this column is not normalized. I can find telephones separate by spaces, by "-", and even a lot of different formats. (ex: (418) 123-1234, 418 123-1234, 4181231234, 55(11)12321-1234, ...)

Found some articles saying to use REGEXP_REPLACE to take care of that... just removing everything and comparing always with numbers. So, my query for test would by like that:

select * from tb_contc WHERE REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+','') = '1234567777';

First problem I've found is that that is not a valid index to create. Trying to create it returns me a " ORA-01450: maximum key length":


Apparently this kind of function in the index would create a index that is worst than making a full scan in the table... The next step was to add a SUBSTRING in the index, that allows to create it by limiting the size of the VARCHAR returned from my REGEX

CREATE INDEX IX_06_GR_CONTC ON GR_CONTC (substr( REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+',''), 1, 20));

Things got real ugly... Besides, to force the use of this index I need to add the exactly same query in my select.

Wouldn't be bad if it it had a good performance, but instead what I got is:

Without the index: 15 seconds the first time I execute the query, all the others would take basically nothing With the index: 2 seconds every time.

Wraping up: Am I doing a good thing by using this kind of index with REGEX? Is there a better way?

  • What is the type of TEL_CONTC column ?
    – a1ex07
    Commented May 25, 2016 at 14:11
  • It's a VARCHAR(20) Commented May 25, 2016 at 14:37
  • 1
    You don't say which DB version, but one option is to create a virtual column that uses regex displays only numbers and you can index this virtual column. Make sure that your where clause queries on the virtual column directly.
    – Raj
    Commented May 25, 2016 at 15:41
  • @DiegoSilvaPires : You should be able to create function -based index REGEXP_REPLACE (TEL_CONTC,'[^[:digit:]]+','') on varchar(20) column without substr. Are you sure TEL_CONTC is not clob/long ?
    – a1ex07
    Commented May 25, 2016 at 15:58
  • Also, To investigate performance, you need to run the query at least couple hundred or thousand times , preferably from different sessions, passing different parameters, and checking v$sql/v$sqlarea system views for execution time, buffer gets, cpu time, physical reads, and so on.
    – a1ex07
    Commented May 25, 2016 at 16:06

1 Answer 1


I don't have direct experience of an index with this kind of REGEX so I would avoid it by creating a 'standardised' telephone number column.

Create a process to go through the telephone numbers and store the clean, formatted version in the new column.

Perform the 'standardising' process on the user input and use that as a search against the new telephone column - which you will have indexed.

The other option is to go through a data clean up process to standardise all the telephone numbers and ensure that any on-going input method applies the same process/validation.


The virtual column option mentioned above may be of use to you as well.

ALTER TABLE contacts ADD(search_telephone AS (REGEXP_REPLACE(tel_no, 'pattern', 'replace')).
  • I could try to correct the data off the BD, but the system is so big, so many ways to put wrong information again there, that this is not much of an option right now Commented May 25, 2016 at 16:54
  • 1
    @DiegoSilvaPires - a million records is not so big ! A simple trigger on CREATE / UPDATE to apply the format to the new column that you would have had in the REGEX should catch user changed values. A script to initially populate a new column ought to take no more than a few minutes really. Commented May 26, 2016 at 7:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.