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>