Creating a text index

In the following example, you perform a text search using a source table called t_log.

In the following example, you perform a text search using a source table called t_log. This source table has two columns:

  • One column containing the table's primary key

  • Another column containing log file information

You must associate a projection with the source table. Use a projection that is sorted by the primary key and either segmented by hash(id) or unsegmented. You can define this projection on the source table, along with any other existing projections.

Create a text index on the table for which you want to perform a text search.

=> CREATE TEXT INDEX text_index ON t_log (id, text);

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 text_index;
          token         | doc_id | partition
------------------------+--------+-----------
<info>                  |      6 |      2014
<warning>               |      2 |      2014
<warning>               |      3 |      2014
<warning>               |      4 |      2014
<warning>               |      5 |      2014
database                |      6 |      2014
execute:                |      6 |      2014
object                  |      4 |      2014
object                  |      5 |      2014
[catalog]               |      4 |      2014
[catalog]               |      5 |      2014

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.