Workshop: Oracel Text
Title: "Getting More Value From Unstructured Data"
Product: Oracle Text, a free option (not installed by default)
Context index types that enables text searching:
- context
- ctxcat
- ctxrule
After text index is created, these keywords can be used:
- where contains
- where catsearch
- where matches
Oracle Text supports stop words (e.g., "the", "on", "a") and they are customizable.
Example:
- > create table foobar (text varchar2(4000));
- > insert into foobar values ('the cat sat on the mat');
- > create index idx_text on foobar(text) indextype is ctxsys.context;
- > select text from foobar where text contains 'cat';
Important: Text index does not synchronize automatically when new data are inserted. However, it can be instructed to synchronize on commit, which may incur a performance hit. Different DML types has different behaviors (RTF[ine]M!).
Grant CTXAPP role to the user to be able to create indices, synonyms, etc.
