Fastest way to search for longest prefix in ORACLE

Question

I have a list of phone number prefixes defined for large number of zones (in query defined by gvcode and cgi). I need to efficiently find a longest prefix that matches given number PHONE_NR.

I use inverted LIKE clause on field digits (which contains prefixes in form +48%, +49%, +1%, +1232% and so on).

Therefore I can't use normal index on that field.

I managed to get substantial improvement by using IOT on gvcode and cgi field (which are part (first two cols) of primary key). I also looked at some oracle text indexes but can't find one that will match longer input with shorter prefix in the table.

Is there any other way to perform such search that is faster than this approach.

Here is the query which gives a list of all matched prefixes (I sort it afterwards on digits length).

  select  t.gvcode,  t.digits
                from NUMBERS t 
                    where 
                        t.gvcode=ZONE_SET_CODE 
                        and t.cgi=cgi_f
                       and ( PHONE_NR like t.digits)
                         order by length(digits) desc 

Answer 1

In addition to the index on "digits" you can create the index on rpad(substr(digits,1,length(digits)-1), 10, '9'). "10" is the maximum length that you want to support. You will add an additional condition to the where clause: rpad(substr(digits,1,length(digits)-1), 10, '9') >= PHONE_NR

Your SQL would be:

select  t.gvcode,  t.digits
from NUMBERS t 
    where 
        t.gvcode=ZONE_SET_CODE 
        and t.cgi=cgi_f
       and PHONE_NR like t.digits
       and substr(digits, 1, length(digits)-1) <= PHONE_NR
       and rpad(substr(digits,1,length(digits)-1), 10, '9') >= PHONE_NR
order by length(digits) desc 

Here is an example in sqlfiddle