CREATE TEXT INDEX

TDV supports TEXT INDEXES for the Vertica data source. TEXT INDEX is a useful tool for text-search. Without TEXT INDEX, you may still be able to perform table searches on a string column using the "LIKE" keyword for example, but the search might be slow as the query engine may not be utilizing the indexes. TEXT INDEX is more powerful than using the 'LIKE' keyword. In Vertica, Tokenizers and Stemmers are used when indexing and querying, which makes the Search more efficient.

For more information, refer to Creating a Text Index.

Syntax

CREATE TEXT INDEX index_table_name

ON table_name (column1, column2, ...);

Example

CREATE TEXT INDEX index_1 ON queenbee (column_bee1, text_column_bee2, column_bee3)

Remarks

• The source table must have primary key, an associated projection and must be both sorted and segmented by the primary key. The following example may be used to create the TEXT INDEX table:

CREATE TABLE index_1(column_bee1 INT, text_column_bee2 VARCHAR, column_bee3 INT) SEGMENTED BY HASH(id) ALL NODES;

Index table "index_1" will be created as a separate table and has a constraint to source table "queenbee".

• It is important to note that Studio cannot index Vertica data sources. Vertica creates a separate table to store indexes for that table, however this table will not be included during introspection and will not be stored to tdv metadata.

DROP TEXT INDEX

You will not be able to find index table information from the source table definition. To drop the Text Index table, use the following example:

drop text index index_1 on queenbee;

The text index is always synchronized to the contents of the source table.