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.
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:
Now the actual test::
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.
