Join using Like Condition - Using Oracle Text

This is also based on one of my posting in OTN. Here, I have demonstrated the use of Oracle Text in place of LIKE operator. This may give significant benefit in terms of performance.

The problem.

create table location_source
(code number,
description varchar2(100)
);

 

insert into location_source values('', 'check in london');
insert into location_source values('', 'flight Paris 9.30');
insert into location_source values('', 'baggage allowance 20kg Hong kong');
insert into location_source values('', 'check out 6.30');
insert into location_source values('', 'new york 23rd Jul');
insert into location_source values('', 'flight jy688 sydney');

 

create table location_lookup
(code number
location_lookup varchar2(10)
);

 

insert into location_lookup values (001, 'London');
insert into location_lookup values (002, 'London');
insert into location_lookup values (003, 'Paris');
insert into location_lookup values (004, 'Hong Kong');
insert into location_lookup values (005, 'Paris');
insert into location_lookup values (006, 'New York');

The SQL looks something like this:

select s.*, min(l.code) , l.location_lookup
from location_source s, locaction_lookup l
and UPPER(s.description||'%') like upper(l.location_lookup||'%')
group by s.*, l.location_lookup

 

Output Required:

 
Code	Description	                  Code	Location_Lookup
	check in london	                  001	London
	flight Paris 9.30                 003	Paris
	baggage allowance 20kg Hong kong  004	Hong Kong
	check out 6.30		
	new york 23rd Jul	          006	New York
	flight jy688 sydney

Now, The problem is other methods using LIKE operator is running slow for huge amount of data. Oracle Text is very useful in these cases.

Preparing SCOTT to use Oracle Text:

    SQL>   
    SQL> SHOW USER  
    USER is "SYS"  
    SQL>   
    SQL> GRANT ctxapp TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_CLS TO scott;  
      
    Grant succeeded.  
      
    SQL> GRANT EXECUTE ON CTXSYS.CTX_DDL TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_DOC TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_OUTPUT TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_QUERY TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_REPORT TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_THES TO scott;  
    GRANT EXECUTE ON CTXSYS.CTX_ULEXER TO scott;  
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  
      
    SQL>   
    Grant succeeded.  

Now the actual test::

    SQL> SHOW user  
    USER is "SCOTT"  
    SQL> --My database version.  
    SQL> SELECT * FROm v$version;  
      
    BANNER  
    --------------------------------------------------------------------------------  
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production  
    PL/SQL Release 11.2.0.1.0 - Production  
    CORE    11.2.0.1.0      Production  
    TNS for Linux: Version 11.2.0.1.0 - Production  
    NLSRTL Version 11.2.0.1.0 - Production  
      
    SQL> SELECT * FROM location_source;  
      
          CODE DESCRIPTION  
    ---------- ----------------------------------------------------------------------------------------------------  
               check in london  
               flight Paris 9.30  
               baggage allowance 20kg Hong kong  
               check out 6.30  
               new york 23rd Jul  
               flight jy688 sydney  
      
    6 rows selected.  
      
    SQL> SELECT * FROM location_lookup;  
      
          CODE LOCATION_L  
    ---------- ----------  
             1 London  
             2 London  
             3 Paris  
             4 Hong Kong  
             5 Paris  
             6 New York  
      
    6 rows selected.  
      
    SQL> CREATE INDEX location_source_idx ON location_source(description) INDEXTYPE IS CTXSYS.CONTEXT;  
      
    Index created.  
      
    SQL>  EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'LOCATION_SOURCE', cascade=>TRUE);  
      
    PL/SQL procedure successfully completed.  
      
    SQL> ed  
    Wrote file afiedt.buf  
      
      1  SELECT ls.code,  
      2    ls.description,  
      3    MIN(ll.code) new_code,  
      4    ll.location_lookup  
      5  FROM location_source ls  
      6  LEFT OUTER JOIN location_lookup ll  
      7  ON contains(ls.description,ll.location_lookup)>0  
      8  GROUP BY ls.code,  
      9    ls.description,  
     10*   ll.location_lookup  
    SQL> /  
      
          CODE DESCRIPTION                                                                                            NEW_CODE LOCATION_L  
    ---------- ---------------------------------------------------------------------------------------------------- ---------- ----------  
               check in london                                                                                               1 London  
               baggage allowance 20kg Hong kong                                                                              4 Hong Kong  
               check out 6.30  
               new york 23rd Jul                                                                                             6 New York  
               flight Paris 9.30                                                                                             3 Paris  
               flight jy688 sydney  
      
    6 rows selected.