Create a fragmented index

You can use the FRAGMENT BY clause of CREATE INDEX to create both round-robin and expression-based fragmentation. You cannot, however, use the etx_contains() operator as part of your fragmentation expression.

Suppose that you want to create a fragmented index on the CLOB column abstract of the table reports with the following requirements: documents with a doc_no value less than 1000 are stored in the sbspace sbsp1, and documents with a doc_no value greater than or equal to 1000 are stored in the sbspace sbsp2.

The following CREATE INDEX statement creates a fragmented etx index that meets the preceding requirements:

CREATE INDEX reports_idx5 ON reports (abstract etx_clob_ops)
    USING etx (WORD_SUPPORT = 'PATTERN', 
    STOPWORD_LIST = 'my_stopwordlist', INCLUDE_STOPWORDS = 'TRUE',
    PHRASE_SUPPORT = 'MAXIMUM')
    FRAGMENT BY EXPRESSION 
    doc_no < 1000 IN sbsp1,
    doc_no >= 1000 IN sbsp2 ;

This index supports pattern and word matching and supports maximum phrase support. Although the word list my_stopwordlist is specified, all of the stopwords are indexed, due to the INCLUDE_STOPWORDS index parameter. However, the stopwords are relevant in a search only if the CONSIDER_STOPWORDS tuning parameter is specified in the etx_contains() operator.