Creating a Metamorph Index

A sorted order index is optimized for columns containing values of limited length, which can easily be canonically managed. In some cases, especially when a column contains a large amount of text, there is a need for an index which goes beyond the methods used in these previous examples.

For example, let us take the case of the News database being archived on a daily basis by the Strategic Planning and Intelligence Department. The entire body of the news article is stored in a table, whether the data type in use is VARCHAR, indicating a variable length number of characters, or INDIRECT, indicating it points elsewhere to the actual location of the files. While subjects, dates, and bylines are important, the most often queried part is the body of the article, or the text field itself. The column we want to index is a text column rather than something much more concise like an an employee ID number.

To accurately find text in the files, where search items are to be found in proximity to other search items within some defined delimiters, all the words of all the text in question must be indexed in an efficient manner which still allows everything relevant to be found based on its content, even after it has been archived away. A Metamorph index combines indexing technology with a linear free text scan of selected portions of the database where appropriate in order to accomplish this. This linear scan following the index lookup is referred to as a post-search or post-processing.

Metamorph query language as used following LIKE and its variants is described in detail in Chapter here, Intelligent Text Search Queries. Where you anticipate such LIKE queries will be common on that field, it would be appropriate to create a Metamorph index.

The form of the command is:

CREATE METAMORPH [INVERTED|COUNTER] INDEX index-name
     ON table-name (column-name [, column-name...])
     [WITH option-name [value] [option-name [value] ...]] ;

Syntax is the same as in the previous CREATE INDEX examples, except that you are specifying the type of index you want created (i.e. a Metamorph index). Further options controlling how the index is created may be set in the WITH clause; see here.

Example: The news database that is being accumulated from selected news articles is getting too large to search from beginning to end for content based searches which make heavy use of the LIKE clause. A Metamorph index should be created for the Strategic Planning and Intelligence Department to enhance their research capability. The column containing the text of the articles is called BODY.

An index called BODYINDEX will be created and maintained on the BODY column of the NEWS table, which contains the full text of all collected news articles. Now content searches can stay fast as well as accurate, regardless of how large this database becomes.

Additional columns can be specified in addition to the text field to be indexed. These should be fixed length fields, such as dates, counters or numbers. The extra data in the index can be used to improve searches which combine a LIKE statement with restrictions on the other fields, or which ORDER BY some or all of the other fields.