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>
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
-
4 comments:
Thanks for your example. Really appreciated.
Nice example, thank you.
nice work ..,really appreciated
Do you have to create some type of cron job or something similar for the index to keep up to date?
Post a Comment