Creating a Text Index on a Flex Table

In the following example, you create a text index on a flex table. The example assumes that you have created a flex table called mountains. See Getting Started in Using Flex Tables to create the flex table used in this example.

Before you can create a text index on your flex table, add a primary key constraint to the flex table.

=> ALTER TABLE mountains ADD PRIMARY KEY (__identity__);

Create a text index on the table for which you want to perform a text search. Tokenize the __raw__column with the FlexTokenizer and specify the data type as LONG VARBINARY. It is important to use the FlexTokenizer when creating text indices on flex tables because the data type of the __raw__ column differs from the default StringTokenizer.

=> CREATE TEXT INDEX flex_text_index ON mountains(__identity__, __raw__) TOKENIZER public.FlexTokenizer(long varbinary);

The text index contains two columns:

  • doc_id uses the unique identifier from the source table.
  • token is populated with text strings from the designated column from the source table. The word column results from tokenizing and stemming the words found in the text column.

If your table is partitioned then your text index also contains a third column named partition.

=> SELECT * FROM flex_text_index;
    token    | doc_id
-------------+--------
 50.6        |      5
 Mt          |      5
 Washington  |      5
 mountain    |      5
 12.2        |      3
 15.4        |      2
 17000       |      3
 29029       |      2
 Denali      |      3
 Helen       |      2
 Mt          |      2
 St          |      2
 mountain    |      3
 volcano     |      2
 29029       |      1
 34.1        |      1
 Everest     |      1
 mountain    |      1
 14000       |      4
 Kilimanjaro |      4
 mountain    |      4
(21 rows)

You create a text index on a source table only once. In the future, you do not have to re-create the text index each time the source table is updated or changed.

Your text index stays synchronized to the contents of the source table through any operation that is run on the source table. These operations include, but are not limited to:

  • COPY
  • INSERT
  • UPDATE
  • DELETE
  • DROP PARTITION
  • MOVE_PARTITIONS_TO_TABLE

    When you move or swap partitions in a source table that is indexed, verify that the destination table already exists and is indexed in the same way.