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