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
bts index parameters
{ | <analyzer index parameter>(explicit id ) | <canonical_map index parameter>(explicit id ) | delete= { "deferred" | "immediate" } (explicit id ) | field_token_max="number_tokens"(explicit id ) | max_clause_count="max_clauses"(explicit id ) | query_default_field=" { field | * } "(explicit id ) | query_default_operator= { "OR" | "AND" } | query_log= { "no" | "yes" } (explicit id ) | <stopwords index parameter>(explicit id ) | tempspace=tempspace_name(explicit id ) | <thesaurus index parameters>(explicit id ) | <xact_memory index parameter>(explicit id ) | xact_ramdirectory=" { no | yes } " (explicit id ) | { <XML index parameters>(explicit id ) | <JSON index parameters>(explicit id ) } }
| 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:
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.
| 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;