how to use CTXSYS.CONTEXT index

SCOTT@orcl_11g> CREATE TABLE test_tab (test_col VARCHAR2(30))
2 /

Table created.

SCOTT@orcl_11g> INSERT ALL
2 INTO test_tab VALUES ('ab')
3 INTO test_tab VALUES ('a''b')
4 INTO test_tab VALUES ('a b')
5 SELECT * FROM DUAL
6 /

3 rows created.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
2 /

TOKEN_TEXT
----------------------------------------------------------------
AB
B

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'a''b') > 0
2 /

TEST_COL
------------------------------
a'b
a b

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'a b') > 0
2 /

TEST_COL
------------------------------
a'b
a b

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'ab') > 0
2 /

TEST_COL
------------------------------
ab


Another Example



SCOTT@orcl_11g> DROP INDEX test_idx
2 /

Index dropped.

SCOTT@orcl_11g> BEGIN
2 CTX_DDL.CREATE_PREFERENCE ('test_lex', 'BASIC_LEXER');
3 CTX_DDL.SET_ATTRIBUTE ('test_lex', 'PRINTJOINS', '''');
4 END;
5 /

PL/SQL procedure successfully completed.

SCOTT@orcl_11g> CREATE INDEX test_idx ON test_tab (test_col)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 PARAMETERS ('LEXER test_lex')
4 /

Index created.

SCOTT@orcl_11g> SELECT token_text FROM dr$test_idx$i
2 /

TOKEN_TEXT
----------------------------------------------------------------
A'B
AB
B

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'a''b') > 0
2 /

TEST_COL
------------------------------
a'b

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'a b') > 0
2 /

TEST_COL
------------------------------
a b

SCOTT@orcl_11g> SELECT * FROM test_tab WHERE CONTAINS (test_col, 'ab') > 0
2 /

TEST_COL
------------------------------
ab

SCOTT@orcl_11g>

Text search in SQL query

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>

to search a string phonetically?

SCOTT@orcl_11g> CREATE TABLE your_table
2 (name VARCHAR2 (30))
3 / 4 INTO your_table VALUES ('MAHAMMAD')
5 INTO your_table VALUES ('MAHHAMMAD')
6 INTO your_table VALUES ('SOMEBODY')

Table created.

SCOTT@orcl_11g> INSERT ALL
2 INTO your_table VALUES ('MOHAMAD')
3 INTO your_table VALUES ('MUHAMMAD')

7 SELECT * FROM DUAL
8 /

5 rows created.


-- soundex:

SCOTT@orcl_11g> SELECT *
2 FROM your_table
3 WHERE SOUNDEX ('MOHAMMAD') = SOUNDEX (name)
4 /

NAME
------------------------------
MOHAMAD
MUHAMMAD
MAHAMMAD
MAHHAMMAD


-- jaro winkler:

SCOTT@orcl_11g> SELECT UTL_MATCH.JARO_WINKLER_SIMILARITY ('MOHAMMAD', name) jws,
2 name
3 FROM your_table
4 ORDER BY jws DESC
5 /

JWS NAME
---------- ------------------------------
97 MOHAMAD
92 MUHAMMAD
83 MAHAMMAD
81 MAHHAMMAD
47 SOMEBODY


-- Oracle Text search using soundex operator (!):

SCOTT@orcl_11g> CREATE INDEX your_idx ON your_table (name)
2 INDEXTYPE IS CTXSYS.CONTEXT
3 /

Index created.

SCOTT@orcl_11g> SELECT name
2 FROM your_table
3 WHERE CONTAINS (name, '!MOHAMMAD') > 0
4 /

NAME
------------------------------
MOHAMAD
MUHAMMAD
MAHAMMAD
MAHHAMMAD