Oracle TEXT is designed for such things. You can create a multi_column_datastore and use it as a parameter when creating a context index, then you can search quickly for various words in any of the columns in the datastore in various ways. The following is a simple example that searches for any rows that have both CPM and EK12 but not DXB plus the date condition. There are many other conditions that can be specified.
SCOTT@orcl_11g> CREATE TABLE test_tab
2 (id NUMBER,
3 rcvd_dt DATE,
4 msgtext VARCHAR2(15),
5 toptext VARCHAR2(15),
6 text_cols VARCHAR2(1))
7 /
Table created.
SCOTT@orcl_11g> INSERT ALL
2 INTO test_tab VALUES (1, SYSDATE - 1, 'CPM EK12', 'DXB', NULL)
3 INTO test_tab VALUES (2, SYSDATE - 1, 'CPM EK12', 'WHATEVER', NULL)
4 INTO test_tab VALUES (3, SYSDATE - 1, 'ABC DEF', 'WHATEVER', NULL)
5 INTO test_tab VALUES (4, SYSDATE, 'CPM EK12', 'WHATEVER', NULL)
6 SELECT * FROM DUAL
7 /
4 rows created.
SCOTT@orcl_11g> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('test_multi', 'MULTI_COLUMN_DATASTORE');
3 CTX_DDL.SET_ATTRIBUTE ('test_multi', 'COLUMNS', 'msgtext, toptext');
4 END;
5 /
PL/SQL procedure successfully completed.
SCOTT@orcl_11g> CREATE INDEX test_text_idx ON test_tab (text_cols)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('DATASTORE test_multi')
4 /
Index created.
SCOTT@orcl_11g> SELECT *
2 FROM test_tab
3 WHERE CONTAINS (text_cols, 'CPM AND EK12 NOT DXB') > 0
4 AND TRUNC (RCVD_DT) = TRUNC (SYSDATE-1)
5 /
ID RCVD_DT MSGTEXT TOPTEXT T
---------- --------- --------------- --------------- -
2 29-NOV-08 CPM EK12 WHATEVER
SCOTT@orcl_11g>
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Subscribe to:
Post Comments (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-
No comments:
Post a Comment