Question: How does the Oracle Text
index get used to tune SQL? I hear that Oracle Text
indexes can help tune SQL that uses the "like" clause.
The Oracle*Text utility (formally called Oracle ConText and
Oracle Intermedia) allows us to parse through a large text
column and index on the words within the column.
Unlike ordinary b-tree or bitmap indexes, Oracle
context, ctxcat and ctxrule indexes can be set
not to update as content is changed. Since most standard
Oracle databases will use the ctxcat index with
standard relational tables, you must decide on a refresh
interval. Oracle provides the SYNC operator for this. The
default is SY^NC=MANUAL and you must manually synchronize
the index with CTX_DDL.SYNC_INDEX.
SYNC (MANUAL | EVERY "interval-string"
| ON COMMIT)]
Hence, Oracle Text indexes are most useful for removing
full-table scans when the tables are largely read-only
and/or the end-users don't mind not having 100% search
Oracle Text works with traditional data columns and also
with XML, MS-Word docs and Adobe PDF files that are stored
within Oracle. Oracle Text has several index types:
WHERE CATSEARCH(text_column, 'ipod')> 0;
Indexes - The
CONTEXT index type is used to index large amounts of
text such as Word, PDF, XML, HTML or plain text
WHERE CONTAINS(test_column, 'ipod', 1) > 0
These types of Oracle text indexes allow you to replace the
old-fashioned SQL "LIKE" syntax with "CONTAINS" or "CATSEARCH"
When we execute the query with the new index we see that the
full-table scan is replaced with a index scan, greatly
reducing execution speed and improving hardware stress: