bts access method syntax

Instead of using the bts access method to create a bts index, you can run the HCL OneDB™ JSON createTextIndex command. Use the same syntax for bts index parameters for both methods.

Syntax

bts index parameters

Notes:

¹ See analyzer index parameter.
² See ids_dbxt_038.html.
³ See delete index parameter.
⁴ See field_token_max index parameter.
⁵ See max_clause_count index parameter.
⁶ See query_default_field index parameter.
⁷ See query_log index parameter.
⁸ See stopwords index parameter.
⁹ See Creating a space for temporary data.
¹⁰ See thesaurus index parameters.
¹¹ See xact_memory index parameter.
¹² See xact_ramdirectory index parameter.
¹³ See XML index parameters syntax.
¹⁴ See JSON index parameters syntax.


Element	Description
`column_name`	The name of the column in the table that contains the text documents to search.
`expression`	The expression that defines an index fragment. The expression must return a Boolean value. The expression can contain only columns from the current table and data values from only a single row. The expression cannot include the following elements: Subqueries Aggregates are not allowed. T The built-in CURRENT, DATE, SYSDATE, and TODAY functions The bts_contains() search predicate For more information about expressions, see Expression.
`field`	The name of the field to set as the default field in basic text search queries instead of the `contents` field.
`index_name`	The name of the bts index.
`max_clauses`	The maximum number of clauses in a basic text search query. Default is 1024.
`number_tokens`	The maximum number of tokens to index for each document. Default is 10 000. Maximum is 2 000 000 000.
`op_class`	The operator class for the data type that is specified in the `column_name` element.
`space_name`	The name of the sbspace or extspace in which to store the bts index.
`table_name`	The name of the table for which you are creating the index.
`tempspace_name`	The name of the space in which to store temporary files.

Usage

Include a comma between index parameters.

You must create a bts index for each text column that you plan to search. You can either create a separate bts index for each text column, or create a composite index on multiple text columns in a table by including multiple column and operator class pairs. You cannot create a composite index that includes a JSON or BSON column. If you want to index each column separately, include the query_default_field="*" index parameter.

You cannot alter the characteristics of a bts index after you create it. Instead, you must drop the index and re-create it.

When you create a bts index, you specify the operator class that is defined for the data type of the column that is indexed. An operator class is a set of functions that the database server associates with the bts access method to optimize queries and build indexes. Each of the data types that support a bts index has a corresponding operator class. The following table lists each data type and its corresponding operator class.

Table 1. Data types and the corresponding operator classes
Data type	Operator class
BLOB	bts_blob_ops
BSON	bts_bson_ops
CHAR	bts_char_ops
CLOB	bts_clob_ops
JSON	bts_json_ops
LVARCHAR	bts_lvarchar_ops
NCHAR	bts_nchar_ops
NVARCHAR	bts_nvarchar_ops
VARCHAR	bts_varchar_ops

Examples

Example 1: Create a bts index and store it in an sbspace

For example, suppose that your search data is contained in a column that is named brands, of data type CHAR, in a products table. To create a bts index that is named desc_idx in the sbspace sbsp1, use the following syntax:

CREATE INDEX desc_idx ON products (brands  bts_char_ops)
 USING bts IN sbsp1;

Example 2: Create a fragmented bts index

The following example stores the bts_idx index in three sbspaces by fragmenting the index according to an expression:

CREATE INDEX bts_idx ON bts_tab(col2 bts_char_ops) USING bts
      FRAGMENT BY EXPRESSION
        (col1 <= 1000000) IN bts_sbspace00,
        (col1 > 1000000 and col1 <= 2000000) 
        IN bts_sbspace01,
        REMAINDER IN bts_sbspace36;