Basic Text Search

You can perform basic text searching for words and phrases in a document repository stored in a column of a table.

In traditional relational database systems, you must use a LIKE or MATCHES condition to search for text data and use the database server to perform the search. HCL OneDB™ uses the open source CLucene text search package to perform basic text searches. This text search package and its associated functions, known as the text search engine, is specifically designed to perform fast retrieval and automatic indexing of text data. The text search engine runs in virtual processors that are controlled by the database server.

To perform basic text searches, you create a bts index on one or more text columns and then use the bts_contains() search predicate function to query the text data.

You can configure how to index the text data by specifying an analyzer. Each analyzer uses different criteria to index the data. By default the Standard analyzer is used.

You can specify synonyms for data that has multiple words for the same information, for example, proper names with multiple spellings. You can use canonical mapping to create a static list of synonyms. You can create a thesaurus with synonyms that you can update dynamically.

To search for words and phrases you use a predicate called bts_contains() that instructs the database server to call the text search engine to perform the search.

For example, to search for the string century in the column brands in the table products you use the following statement:

SELECT id FROM products 
WHERE bts_contains(brands, 'century');

The search predicate takes a variety of arguments to make the search more detailed than one using a LIKE condition. Search strategies include single and multiple character wildcard searches, fuzzy and proximity searches, AND, OR and NOT Boolean operations, range options, and term-boosting.

If you store XML, JSON, or BSON documents, you can create customized structured indexes so that you can search columns by XML tags, attributes, and paths, or JSON fields, values, and paths. Customize the index with XML or JSON index parameters.

You can search for unstructured text or, if you use XML index parameters, you can search columns with XML documents by tags, attributes, or XML paths.

You can use basic text search functions to perform maintenance tasks, such as compacting the bts index and obtaining the list of indexed field names.