create table country_codes (country_code varchar2(10));

create index country_codes_ix1 on country_codes (country_code);

insert into country_codes (select to_char(object_id) from dba_objects);

commit

exec dbms_stats.gather_table_stats( user, 'COUNTRY_CODES');

--FTS
SELECT country_code
FROM country_codes
WHERE country_code LIKE '%20774%'

Plan
SELECT STATEMENT  ALL_ROWS Cost: 23  Bytes: 11,385  Cardinality: 2,277  	
	1 TABLE ACCESS FULL TABLE SCOTT.COUNTRY_CODES Cost: 23  Bytes: 11,385  Cardinality: 2,277  


-- index scan
SELECT country_code
FROM country_codes
WHERE country_code LIKE '20774%'

Plan
SELECT STATEMENT  ALL_ROWS Cost: 2  Bytes: 5  Cardinality: 1  	
	1 INDEX RANGE SCAN INDEX SCOTT.COUNTRY_CODES_IX1 Cost: 2  Bytes: 5  Cardinality: 1  

SELECT country_code
FROM (SELECT country_code 
FROM country_codes
WHERE '9123434345' LIKE country_code || '%' 
ORDER BY LENGTH(country_code) DESC)
WHERE ROWNUM = 1

SCOTT@orcl_11g> CREATE TABLE country_codes
  2    (country_code  VARCHAR2(15))
  3  /

Table created.

SCOTT@orcl_11g> INSERT ALL
  2  INTO country_codes VALUES (91)
  3  INTO country_codes VALUES (912)
  4  SELECT * FROM DUAL
  5  /

2 rows created.

SCOTT@orcl_11g> INSERT INTO country_codes
  2  SELECT object_id FROM all_objects
  3  /

68724 rows created.

SCOTT@orcl_11g> CREATE INDEX test_idx ON country_codes (country_code)
  2  /

Index created.

SCOTT@orcl_11g> EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'COUNTRY_CODES')

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> VARIABLE searchstring VARCHAR2(15)
SCOTT@orcl_11g> EXEC :searchstring := '9123434345'

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> SET TIMING ON
SCOTT@orcl_11g> SET AUTOTRACE ON
SCOTT@orcl_11g> SELECT country_code
  2  FROM   (SELECT country_code
  3  	     FROM   country_codes,
  4  		    (SELECT  SUBSTR (:searchstring, 1, LEVEL) codestring,
  5  			     LEVEL lvl
  6  		     FROM    DUAL
  7  		     CONNECT BY LEVEL <= LENGTH (:searchstring))
  8  	     WHERE  country_code = codestring
  9  	     ORDER  BY lvl DESC)
 10  WHERE  ROWNUM = 1
 11  /

COUNTRY_CODE
---------------
9123

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1804782531

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |          |     1 |     9 |     4  (25)| 00:00:01 |
|*  1 |  COUNT STOPKEY                    |          |       |       |            |          |
|   2 |   VIEW                            |          |     1 |     9 |     4  (25)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY          |          |     1 |    36 |     4  (25)| 00:00:01 |
|   4 |     NESTED LOOPS                  |          |     1 |    36 |     3   (0)| 00:00:01 |
|   5 |      VIEW                         |          |     1 |    31 |     2   (0)| 00:00:01 |
|*  6 |       CONNECT BY WITHOUT FILTERING|          |       |       |            |          |
|   7 |        FAST DUAL                  |          |     1 |       |     2   (0)| 00:00:01 |
|*  8 |      INDEX RANGE SCAN             | TEST_IDX |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM=1)
   3 - filter(ROWNUM=1)
   6 - filter(LEVEL<=LENGTH(:SEARCHSTRING))
   8 - access("COUNTRY_CODE"="CODESTRING")


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        424  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SCOTT@orcl_11g> SET AUTOTRACE OFF
SCOTT@orcl_11g> SET TIMING OFF

SELECT country_code
FROM   (
    SELECT country_code
    FROM   country_codes
    WHERE country_code IN (
        SELECT  SUBSTR (:searchstring, 1, LEVEL) codestring
        FROM    DUAL
        CONNECT BY LEVEL <= LENGTH (:searchstring)
    )
    ORDER  BY length(country_code) DESC
)
WHERE  ROWNUM = 1
/