Monday, September 21, 2009

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>

4 comments:

Anonymous said...

Thanks for your example. Really appreciated.

Anonymous said...

Nice example, thank you.

Anonymous said...

nice work ..,really appreciated

Anonymous said...

Do you have to create some type of cron job or something similar for the index to keep up to date?