Fastest way to search for longest prefix in ORACLE

I might sound stupid, but when I ran into such problem I went in most non-space-efficient brute force way:

Lets say:

L=length of longest prefix to match (without obvious +, of course)

Add L additional fields naming them, for example, P1, P2,...,PL

Update those fields with

UPDATE NUMBERS set P1=SUBSTR(PHONE_NR,1,1), P2=SUBSTR(PHONE_NR,1,2), ..., PL=SUBSTR(PHONE_NR,1,L)

(in future you can do this in INSERT OR UPDATE trigger too)

Now you have L fields to create index on and compare to anything you like.

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.digitsfrom 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_NRorder by length(digits) desc 

Here is an example in sqlfiddle

Okay, just writing because I had the same issue. If you know the range of the prefix lengths you have, you can do something similar to the following. The following example assumes prefix lengths 2-6

select  t.num,  coalesce(p6.PREFIX, p5.PREFIX, p4.PREFIX, p3.PREFIX, p2.PREFIX) PREFIX  from NUMBERS tLEFT OUTER JOIN PREFIXES p2 ON substr(t.num,1,2)=p2.PREFIX  LEFT OUTER JOIN PREFIXES p3 ON substr(t.num,1,3)=p3.PREFIX  LEFT OUTER JOIN PREFIXES p4 ON substr(t.num,1,4)=p4.PREFIX  LEFT OUTER JOIN PREFIXES p5 ON substr(t.num,1,5)=p5.PREFIX  LEFT OUTER JOIN PREFIXES p6 ON substr(t.num,1,6)=p6.PREFIX  

Equal joins are as good as you can get.

I believe that it runs way better than any other possible solution here, hope it helps anyone who stumbles on the same issue

Sqlfiddle link modified from sailaway's answer whose script still gives all matches instead of only the longest one