IBM® Informix® 12.10
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 Informix® JSON createTextIndex command. Use the same syntax for bts index parameters for both methods.
Syntax
>>-CREATE INDEX--index_name--ON--table_name---------------------> .-,---------------------. V | >--(----column_name--op_class-+--)--USING bts-------------------> >--+--------------------------------+---------------------------> '-(--| bts index parameters |--)-' >--+---------------------------------------------------------------------------------------------+--> +-IN--space_name------------------------------------------------------------------------------+ | .-,--------------------------------. | | V | | '-FRAGMENT BY EXPRESSION----(--expression--)--IN--space_name-+--+---------------------------+-' '-REMAINDER -IN--space_name-' >--;----------------------------------------------------------->< bts index parameters .-,-----------------------------------------------. V (1) | |----+-| analyzer index parameter |----------------+-+----------| | (2) | +-| canonical_map index parameter |-----------+ | (3) | +-delete=-+-"deferred"--+---------------------+ | '-"immediate"-' | | (4) | +-field_token_max--=--"--number_tokens--"-----+ | (5) | +-max_clause_count="max_clauses"--------------+ | (6) | +-query_default_field="-+-field-+-"-----------+ | '-*-----' | +-query_default_operator=-+-"OR"--+-----------+ | '-"AND"-' | | (7) | +-query_log=-+-"no"--+------------------------+ | '-"yes"-' | | (8) | +-| stopwords index parameter |---------------+ | (9) | +-tempspace=tempspace_name--------------------+ | (10) | +-| thesaurus index parameters |--------------+ | (11) | +-| xact_memory index parameter |-------------+ | (12) | +-xact_ramdirectory--=--"--+-no--+--"---------+ | '-yes-' | | (13) | '-+-| XML index parameters |-------+----------' | (14) | '-| JSON index parameters |------'
Notes:
- See analyzer index parameter.
- See canonical_maps index parameter.
- 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:
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.
| 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;