Create a nonfragmented index

Suppose your search text is contained in a column of type CLOB, named abstract, and that the column is located in a table named reports. To create an etx index named reports_idx1 for this table, use the following syntax:

CREATE INDEX reports_idx1 ON reports (abstract etx_clob_ops) 
    USING etx
    IN sbsp1;

The preceding example creates an etx index that by default supports exact word searches but does not support phrase searches. The index is stored in the sbspace sbsp1 and indexes, by default, only ASCII characters. Since no stopword list is specified, all words in the document are indexed. The operator class etx_clob_ops is specified since the abstract column is of type CLOB.

The following example creates an etx index on the title column that is of type CHAR:

CREATE INDEX reports_idx2 ON reports (title etx_char_ops)
    USING etx (STOPWORD_LIST = 'my_stopwordlist',
    CHAR_SET = 'ISO') IN sbsp1;

In this case, the operator class is etx_char_ops instead of the previously used etx_clob_ops for columns of type CLOB. The index does not include the stopwords found in the list my_stopwordlist. By default, the index supports exact matches, but not phrase searches. The index uses the built-in ISO character set, specified by the CHAR_SET parameter. The index is stored in the sbspace sbsp1.

The following statement creates an etx index that supports a medium level of exact and approximate phrase searches, supports exact word searches, and indexes ASCII characters:

CREATE INDEX reports_idx3 ON reports (abstract etx_clob_ops)
    USING etx (WORD_SUPPORT = 'EXACT',
    STOPWORD_LIST='my_stopwordlist', PHRASE_SUPPORT = 'MEDIUM')
    IN sbsp1;

The index does not include the stopwords found in the list my_stopwordlist. The index is stored in the sbspace sbsp1.

The following statement creates an etx index that supports the most accurate level of exact and approximate phrase searching, supports pattern word searches, and indexes characters included in my_new_charset:

CREATE INDEX reports_idx4 ON reports (abstract etx_clob_ops)
    USING etx (WORD_SUPPORT = 'PATTERN',
    STOPWORD_LIST='my_stopwordlist', PHRASE_SUPPORT = 'MAXIMUM',
    CHAR_SET = 'my_new_charset' )
    IN sbsp1;

The index does not include the stopwords found in the list my_stopwordlist. The index is stored in the sbspace sbsp1.