bts access method syntax

The bts access method is a secondary access method to create indexes that support basic text search queries.

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


1  CREATE INDEX index_name ON table_name  ( + , column_name op_class )  USING bts?   ( %bts index parameters  )
1  IN space_name
1  FRAGMENT BY EXPRESSION + , ( expression ) IN space_name?  REMAINDER  IN space_name
2  ;
bts index parameters

1+ ,
1  %analyzer
index parameter(1)
1  %canonical_map index parameter(2)
1 delete=
2.1 "deferred"
2.1 "immediate"(3)
1  field_token_max = " number_tokens "(4)
1 max_clause_count="max_clauses"(5)
1 query_default_field="
2.1 field
2.1 *
1"(6)
1 query_default_operator=
2.1 "OR"
2.1 "AND"
1 query_log=
2.1 "no"
2.1 "yes"(7)
1  %stopwords index parameter(8)
1 tempspace=tempspace_name(9)
1  %thesaurus index parameters(10)
1  %xact_memory index parameter(11)
1   xact_ramdirectory = "
2.1 no
2.1 yes
1 "(12)
2.1  %XML index parameters(13)
2.1  %JSON
index parameters(14)
ElementDescription
column_nameThe name of the column in the table that contains the text documents to search.
expressionThe 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.

fieldThe name of the field to set as the default field in basic text search queries instead of the contents field.
index_nameThe name of the bts index.
max_clausesThe maximum number of clauses in a basic text search query. Default is 1024.
number_tokensThe maximum number of tokens to index for each document. Default is 10 000. Maximum is 2 000 000 000.
op_classThe operator class for the data type that is specified in the column_name element.
space_nameThe name of the sbspace or extspace in which to store the bts index.
table_nameThe name of the table for which you are creating the index.
tempspace_nameThe 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 typeOperator class
BLOBbts_blob_ops
BSONbts_bson_ops
CHARbts_char_ops
CLOBbts_clob_ops
JSONbts_json_ops
LVARCHARbts_lvarchar_ops
NCHARbts_nchar_ops
NVARCHARbts_nvarchar_ops
VARCHARbts_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;