OceanBase AI Database provides efficient text search capabilities through the full-text index feature. This topic describes how to use full-text indexes, how to select tokenizers and text analyzers, how to create synchronous and asynchronous indexes, and their application scenarios.
Supported features
The following table describes the main features supported by full-text search. For usage limitations and configuration methods, see the corresponding sections.
Feature |
Supported operations |
|---|---|
| Supported column types | Supports columns of the CHAR, VARCHAR, and text object types (TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT). |
| Multiple full-text indexes | You can create multiple full-text indexes on the same table and multiple full-text indexes on the same column. |
| Tokenization and text analysis | Supports the Space (default), Basic English (Beng), Ngram, Ngram2, and IK tokenizers, as well as text analyzers (Analyzer). For more information, see [Tokenizers and text analyzers](#Tokenizers and text analyzers). |
| Synchronous full-text index | Indexes are maintained synchronously during table writes, enabling searches immediately after data is written. This is suitable for scenarios with high requirements for real-time visibility. For more information, see Synchronize a full-text index. |
| Asynchronous full-text index | Indexes are incrementally refreshed in the background to provide eventual consistency, which is suitable for scenarios where index data latency is acceptable. Currently, this is an experimental feature and is not supported in production environments. For more information, see Asynchronous full-text indexing. |
| Data writing, updating, and deletion | Supports data changes on tables with full-text indexes, including INSERT INTO ... ON DUPLICATE KEY UPDATE, REPLACE INTO, multi-table updates and deletes, and data operations through updatable views. For more information, see Update and delete data. |
| Full-text search and relevance scoring | Supports matching text and calculating relevance scores by using MATCH ... AGAINST. For more information, see Search using a synchronous full-text index. |
| Hybrid search | Supports full-text search by using HYBRID_SEARCH, as well as combined full-text and vector search. For more information, see Asynchronous full-text index and hybrid search. |
For differences between synchronous and asynchronous full-text indexes, see Comparison of synchronous and asynchronous indexing. The following sections describe how to use them.
General instructions and limitations
This section describes the basic requirements for using full-text indexes, as well as the support scope of direct load and DDL operations for tables with synchronous or asynchronous full-text indexes.
Basic requirements
- Full-text indexes can be created on non-partitioned tables and partitioned tables, regardless of whether they have a primary key. Asynchronous full-text indexes can only be created on heap tables (
ORGANIZATION = HEAP). - Clustered tables using
CLUSTER BYdo not support creating synchronous or asynchronous full-text indexes. - Supports columns of the
CHAR,VARCHAR, and text object types (TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT). - Only local (
LOCAL) full-text indexes are supported. - The
UNIQUEkeyword cannot be specified when creating a full-text index. - If you want to create a full-text index involving multiple columns, you must ensure these columns have the same character set.
- Neither synchronous nor asynchronous full-text indexes support the columnar storage format.
- For the same table, the same index type cannot exist as both a synchronous and asynchronous index.
Direct load
Tables with synchronous or asynchronous full-text indexes support only full direct load in FULL mode using the INSERT INTO ... SELECT statement; it is not supported in non-FULL mode or by non-INSERT INTO ... SELECT methods.
To verify whether direct load is effective, manually execute the following statement in the target tenant first to prevent fallback to normal import in unsupported scenarios, avoiding mistaking a successful normal import for a successful direct load:
ALTER SYSTEM SET direct_load_allow_fallback = FALSE;
This configuration only controls whether rollback is allowed and does not enable direct load. When performing an import, you still need to specify the direct load method via a hint or the default_load_mode parameter. For parameter descriptions, see direct_load_allow_fallback.
DDL support
The support for DDL operations on tables with synchronous or asynchronous full-text indexes is as follows:
Notice
For operations marked not supported if the table needs to be rebuilt, if you need to rebuild the table via offline DDL, it cannot be performed on a table with a synchronous full-text index.
Action |
Synchronous full-text index |
Asynchronous full-text index |
|---|---|---|
Adjust the order of existing columns, for example, by using CHANGE ... AFTER/FIRST |
Not supported | Supported |
Modify the type or precision of a non-primary key column, for example, change INT to BIGINT or adjust the precision of DECIMAL |
Not supported if the table needs to be rebuilt | Supported |
Modify the type of a full-text index column, for example, change VARCHAR to TEXT |
Not supported if the table needs to be rebuilt | Supported. After the modification, the column type must still meet the requirements of a full-text index. |
| Convert the table character set or modify the character set of a non-primary key column | Not supported if the table needs to be rebuilt | Supported |
Add auto-increment columns or STORED generated columns |
Not supported if the table needs to be rebuilt | Supported, provided that the general constraints on auto-increment columns and generated columns are met. |
| Delete non-primary key columns or generated columns | Not supported if the table needs to be rebuilt | Supported, provided that column dependency constraints are met. |
| Perform multiple operations in combination, for example, change the column type and adjust the column order at the same time | Not supported if the table needs to be rebuilt | Supported for combinations that comply with all constraints. |
| Convert a non-partitioned table into a partitioned table or modify partitioning rules | Not supported | Supported, including RANGE and LIST partitioning scenarios. |
| Add, drop, or modify a primary key | Not supported | Not supported, due to the inherent limitations of heap tables. |
| Modify the primary key column type when data rewriting is required | Not supported | Not supported, due to the inherent limitations of heap tables. |
ADD COLUMN GROUP, including converting row-based storage to columnar storage and hybrid row-column storage |
Not supported | Not supported |
TRUNCATE TABLE, DROP TABLE |
Supported | Supported |
| Delete or truncate partitions and subpartitions | Supported for scenarios that meet the corresponding operation constraints | Supported for scenarios that meet the corresponding operation constraints |
Manually split or merge partitions, for example, by using REORGANIZE PARTITION |
Not supported | Not supported |
Create a full-text index
To create a full-text index, you need to choose whether to use a synchronous or asynchronous index based on your data latency requirements, and select a tokenizer or text analyzer according to the text language and search needs. The following section describes the differences between the two types of indexes, the configuration of tokenizers, and the method for creating them.
Tokenizers and text analyzers
The full-text indexing feature of OceanBase AI Database supports a variety of built-in tokenizers and text analyzers, helping users select the optimal text segmentation strategy for their business scenarios. The default tokenizer is the Space tokenizer; other tokenizers must be explicitly specified using the WITH PARSER parameter. In addition to regular tokenizer plugins, you can also use text analyzers (Analyzers) for more comprehensive text analysis.
List of tokenizers:
- Space tokenizer
- Basic English tokenizer
- IK tokenizer
- Ngram tokenizer
- Analyzer text analyzer
Notice
The Analyzer text analyzer is supported starting from V4.6.2.1.
Example configuration method:
When creating or modifying a table, set the WITH PARSER tokenizer_option parameter in the CREATE TABLE/ALTER TABLE statement when creating a full-text index for the table to specify the tokenizer type for the full-text index. For more information about setting attribute parameters for tokenizers, see Create an index.
CREATE TABLE tbl2(id INT, name VARCHAR(18), doc TEXT,
FULLTEXT INDEX full_idx1_tbl2(name, doc)
WITH PARSER NGRAM
PARSER_PROPERTIES=(ngram_token_size=3));
-- Modify the tokenizer of an existing table's full-text index
ALTER TABLE tbl2(id INT, name VARCHAR(18), doc TEXT,
FULLTEXT INDEX full_idx1_tbl2(name, doc)
WITH PARSER NGRAM
PARSER_PROPERTIES=(ngram_token_size=3)); -- Ngram example
Tokenizer selection strategy
You can first preliminarily select a suitable tokenizer based on the following table, and then determine the specific configuration by referring to the descriptions of each tokenizer's working principle, application scenarios, and segmentation effects later in this topic.
Business scenarios |
Recommended tokenizer/analyzer |
Reason |
|---|---|---|
| General text, English, and multilingual mix | standard Analyzer |
Word boundaries are identified based on the UAX #29 specification, and lowercase letters are unified by the analyzer. No stemming or stop-word filtering is performed additionally. |
| English articles and knowledge base | english Analyzer |
It supports lowercase conversion, basic English stop-word filtering, and stemming, making it suitable for scenarios where different word forms are expected to be matched. |
| Chinese articles and knowledge base | IK | Segments text based on Chinese dictionaries. smart favors coarser-grained terms, while max_word generates finer-grained terms, which helps improve recall but requires evaluating query performance and index overhead. |
| Thai text search | thai Analyzer |
It supports Thai word boundary segmentation, case conversion, number normalization, and basic Thai stop-word filtering. |
| Vietnamese text search | vietnamese Analyzer |
Vietnamese, normalized case, full-width characters, and diacritics. |
| Indonesian text search | indonesian Analyzer |
Supports lowercase conversion, basic Indonesian stopword filtering, and stemming. |
| Malay text search | malay Analyzer |
Provides standard word segmentation and lowercase conversion. |
| Fuzzy match of logs or codes | Ngram / Ngram2 | Generates terms based on consecutive characters; the former has a fixed length, while the latter supports a length range. Suitable for snippet retrieval, but may increase index size and yield irrelevant hits. |
| Basic word segmentation based on spaces and special characters | Space / Beng | Basic word segmentation based on spaces and special characters. |
Space tokenizer (default)
Concept:
- The text is split using spaces, punctuation marks (such as commas and periods), or non-alphanumeric characters (except for the underscore
_). - The tokenization result contains only valid tokens whose length is between
min_token_size(default: 3) andmax_token_size(default: 84). - Chinese characters are treated as single characters.
Application scenarios:
- Languages that use spaces as delimiters, such as English (for example, "apple watch series 9").
- Cases where Chinese characters require manual delimiters (for example, "南京 长江大桥").
Tokenization result:
SELECT TOKENIZE ("南京市长江大桥有1千米长,详见www.XXX.COM, 邮箱xx@OB.COM,一平方公里也很小 hello-word h_name", 'space');
+-------------------------------------------------------------------------------------------------------------+
| tokenize ("南京市长江大桥有1千米长,详见www.XXX.COM, 邮箱xx@OB.COM,一平方公里也很小 hello-word h_name", 'space') |
+-------------------------------------------------------------------------------------------------------------+
|["详见www", "一平方公里也很小", "xxx", "南京市长江大桥有1千米长", "邮箱xx", "word", "hello", "h_name"] |
+-------------------------------------------------------------------------------------------------------------+
As you can see, in this example, the Space tokenizer splits the text into multiple tokens. Symbols such as spaces, commas, and periods serve as delimiters, while consecutive Chinese characters are considered as a single word.
Basic English (Beng) tokenizer
Concept:
- Similar to the Space tokenizer, but it does not retain underscores
_; instead, it treats them as separators. - Suitable for separating English phrases, but has limited segmentation effect on spaceless terms (such as "iPhone15").
Application scenarios:
- Basic search in English documentation (such as logs and comments).
Tokenization result:
SELECT TOKENIZE ("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM, 一平方公里也很小 hello-word h_name", 'beng');
+-----------------------------------------------------------------------------------------------------------------------+
| tokenize ("南京市长江大桥有1千米长,详见WWW.XXX.COM,邮箱xx@OB.COM, 一平方公里也很小 hello-word h_name", 'beng') |
+-----------------------------------------------------------------------------------------------------------------------+
|["详见www", "一平方公里也很小", "xxx", "南京市长江大桥有1千米长", "邮箱xx", "word", "hello", "name"] |
+-----------------------------------------------------------------------------------------------------------------------+
In this example, the underscore _ is split. The core difference between this tokenizer and the Space tokenizer lies in how it handles _.
Ngram tokenizer
Concept:
- Fixed-n-tokenization: The default value of
nis 2. In this mode, consecutive non-separator characters are split into subsequences of lengthn. - The delimiter determination rule is the same as that of the Space tokenizer (retains
_and alphanumeric characters). - The length limit parameter is not supported. All possible
n-length tokens are output.
Application scenarios:
- Short-text fuzzy match (such as user IDs and order numbers).
- Scenarios that require fixed-length feature extraction (such as password policy analysis).
Tokenization result:
SELECT TOKENIZE ("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM, 一平方公里也很小 hello-word h_name", 'ngram');
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tokenize ("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM, 一平方公里也很小 hello-word h_name", 'ngram') |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|["邮箱", "ww", "大桥", "ob", "me", "里也", "or", "_n", "千米", "很小", "米长", "ll", "箱x", "公里", "见w", "co", "也很", "1千", "京市", "lo", "江大", "el", "rd", "一平", "方公", "he", "am", "南京", "h_", "市长", "wo", "xx", "长江", "有1", "na", "详见", "平方", "om", "桥有" |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
In this example, the Ngram tokenizer uses symbols such as spaces, commas, and periods as delimiters. It generates all consecutive character segments of length 2. Each non-delimiter character sequence is segmented into tokens. This segmentation method is language-agnostic and does not divide boundaries based on word meaning. For example, "南京市" generates two tokens: "南京" and "京市".
Ngram2 tokenizer
Concept:
- Dynamic n-gram range: You can set the term length range by specifying the
min_ngram_sizeandmax_ngram_sizeparameters. - Suitable for scenarios requiring coverage of tokens of multiple lengths.
Application scenarios: Scenarios where multiple fixed-length tokens are required simultaneously.
Notice
When using the ngram2 tokenizer, note that it consumes a significant amount of memory. For example, setting large values for the min_ngram_size and max_ngram_size parameters generates a large number of n-gram combinations, which can lead to excessive resource consumption.
Tokenization result:
SELECT TOKENIZE ("南京市长江大桥1千米", 'ngram2', '[{"additional_args":[{"min_ngram_size": 4},{"max_ngram_size": 6}]}]');
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tokenize("南京市长江大桥1千米", 'ngram2', '[{"additional_args":[{"min_ngram_size": 4},{"max_ngram_size": 6}]}]') |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ["长江大桥", "大桥1千", "江大桥1千", "市长江大桥", "京市长江", "江大桥1", "南京市长", "市长江大", "大桥1千米", "江大桥1千米", "市长江大桥1", "长江大桥1", "南京市长江", "桥1千米", "南京市长江大", "长江大桥1千", "京市长江大桥", "京市长江大"] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
In this example, fragments of length 4, 5, and 6 characters are extracted from each position of the original text in sequence. For example, starting from "南" generates "南京市长", "南京市长江", and "南京市长江大", while starting from "京" generates fragments such as "京市长江".
IK tokenizer
Concept:
- The Chinese tokenizer is based on the open-source tool IKAnalyzer and supports two modes:
- Smart mode: Prioritizes outputting long words to reduce segmentation. Each character belongs to only one word. For example, "南京市" is not segmented into "南京" and "市".
- Max Word mode: Outputs all possible short words. It splits the same character into different tokens to provide as many vocabulary items as possible. For example, "南京市" is split into "南京" and "市".
- Automatically recognizes formats such as English words, emails, URLs (excluding
://), and IP addresses.
Application scenarios:
Various Chinese word segmentation scenarios, such as:
- Search for product descriptions in e-commerce platforms (e.g., exact match for "华为Mate60").
- Social media content analysis (such as keyword extraction from user comments).
Tokenization result:
Smart mode:
SELECT TOKENIZE("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM 192.168.1.1 http://www.baidu.com hello-word hello_word", 'IK', '[{"additional_args":[{"ik_mode": "smart"}]}]');+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tokenize("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM 192.168.1.1 http://www.baidu.com hello-word hello_word", 'IK', '[{"additional_args":[{"ik_mode": "smart"}]}]') | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |["邮箱", "hello_word", "192.168.1.1", "hello-word", "长江大桥", "www.baidu.com", "www.xxx.com", "xx@ob.com", "长", "http", "1千米", "详见", "南京市", "有"] | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+In this example, the smart mode splits "南京市长江大桥" into "南京市" and "长江大桥", without outputting short words such as "南京", "长江", or "大桥". It also attempts to merge numerals with measure words, for example, outputting "1千米" as a single word.
Max Word mode:
SELECT TOKENIZE("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM", 'IK', '[{"additional_args":[{"ik_mode": "max_word"}]}]');+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | tokenize("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM", 'IK', '[{"additional_args":[{"ik_mode": "max_word"}]}]') | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |["米", "长江大桥", "市长", "千", "南京市", "南京", "千米", "xx", "www.xxx.com", "长", "www", "xx@ob.com", "长江", "ob", "XXX", "com", "详见", "1", "有", "大桥", "邮箱"] | +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------+In this example, "南京市" and "南京", "长江大桥" and "长江" and "大桥" are all output. It can also identify "市长", which spans word boundaries.
Text analyzer (Analyzer)
Notice
This feature is supported starting with V4.6.2.1.
Concept:
- Enable the Analyzer by using
WITH PARSER analyzerin your JSON configuration. - The Analyzer consists of a text analysis pipeline comprising the character filter (CharFilter), tokenizer (Tokenizer), and token filter (TokenFilter). The Tokenizer is responsible for word segmentation, but the overall capability covers character preprocessing and post-token processing, exceeding the functionality provided by a single word segmentation plugin.
- The current version supports the following built-in types:
standard: general-purpose for multiple languages.english: English.thai: Thai.vietnamese: Vietnamese.indonesian: Indonesian.malay: Malay language.
Application scenarios:
- Supports complex analysis requirements for multilingual and multi-script texts, adapting to different languages and customized text analysis tasks.
Tokenization result:
SELECT TOKENIZE("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM, 一平方公里也很小 hello-word h_name", 'analyzer', '[{"analysis": {"analyzer": "standard"}}]');
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tokenize("南京市长江大桥有1千米长,详见WWW.XXX.COM, 邮箱xx@OB.COM, 一平方公里也很小 hello-word h_name", 'analyzer', '[{"analysis": {"analyzer": "standard"}}]') |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ["邮箱", "大", "千米", "很小", "见", "一", "桥", "1", "word", "hello", "平方公里", "www.xxx.com", "也", "ob.com", "h_name", "长", "详", "xx", "长江", "南京市", "有"] |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
This example uses the standard analyzer to process mixed Chinese and English text. For example, "南京市长江大桥" is segmented into "南京市", "长江", "大", and "桥"; "hello-word" is segmented into "hello" and "word"; and WWW.XXX.COM is converted to lowercase as www.xxx.com. The order of output tokens is not guaranteed to match their order in the original text.
When creating a full-text index using Analyzer, you can use the TOKENIZE function to debug the tokenization results.
For detailed instructions on using Analyzer, see the Create a full-text index by using the text analyzer (Analyzer) section in Create an index.
Tokenization parameters
The following tokenization parameters apply to both synchronous and asynchronous full-text indexes. The configuration method is the same for all three creation methods. For information about the choice of tokenizer and its effects, see Tokenizers and text analyzers.
WITH PARSER tokenizer_option: Optional. Specifies the tokenizer for the full-text index. Valid values:SPACE: Default value. Indicates to tokenize by space. The following attributes can be specified:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] NGRAM: Indicates to tokenize based on N-grams (Chinese). The following attribute can be specified:AttributeValue rangengram_token_size [1, 10] NGRAM2: Indicates to split text into consecutive characters within the range ofmin_ngram_sizetomax_ngram_size.AttributeValue rangemin_ngram_size [1, 16] max_ngram_size [1, 16] BENG: Indicates to tokenize based on Beng (basic English). The following attribute can be specified:AttributeValue rangemin_token_size [1, 16] max_token_size [10, 84] IK: Indicates to tokenize based on IK (Chinese). Currently only supports theutf-8character set. The following attribute can be specified:AttributeValue rangeik_mode smartmax_word
You can use the TOKENIZE function to view the tokenization result of a text using the specified tokenizer and JSON-formatted parameters.
PARSER_PROPERTIES[=](parser_properties_list): Optional. Specifies the properties of the tokenizer. Valid values:min_token_size: Indicates the minimum token length. Default value is 3. Value range is 1 to 16.max_token_size: Indicates the maximum token length. Default value is 84. Value range is 10 to 84.ngram_token_size: Indicates the token length forNGRAM. Only valid for theNGRAMtokenizer. Default value is 2. Value range is 1 to 10.ik_mode: Indicates the tokenization mode for theIKtokenizer. Valid values:smart: Default value. Indicates that dictionary words will be used to improve tokenization accuracy. Dictionary word boundaries will be prioritized, which may reduce unnecessary expansion.max_word: Indicates that dictionary-defined words will be recognized, but it will not affect the maximization of token expansion. Even if a word is defined in the dictionary, themax_wordmode will still attempt to split the text into more words.
Comparison of synchronous and asynchronous indexing
Comparison item |
Synchronous full-text index |
Asynchronous full-text index |
|---|---|---|
| DDL keyword | FULLTEXT |
ASYNC FULLTEXT |
| When to maintain an index | Synchronized maintenance during table writing | Background incremental refresh, non-real-time index writing |
| Data visibility | Searchable after writing | Eventually consistent, with potential visible latency |
| Write/search performance | May become a performance bottleneck in high-throughput scenarios | Trade visible latency for higher write and search performance |
| Application scenarios | Full-text search/hybrid search | Mainly used for hybrid search |
| Partitioner | WITH PARSER / PARSER_PROPERTIES |
Same as for synchronizing full-text indexes |
| Table type | All table types are supported. | Only heap tables are supported. |
UPDATE behavior |
Processed as a regular update | Converted to DELETE + INSERT |
Synchronous full-text index
You can use the CREATE TABLE statement, CREATE FULLTEXT INDEX statement, or ALTER TABLE statement to create a synchronous full-text index. A synchronous full-text index maintains the index in sync while writing data to the table, making it searchable immediately after the write; it is suitable for scenarios requiring high real-time visibility. If you need to improve write and query throughput and are willing to accept a delay in index visibility, see Asynchronous full-text index below. For a comparison of the differences between synchronous and asynchronous, see Synchronous vs asynchronous indexes.
Considerations
In addition to the general instructions and limitations mentioned above, note the following:
- In shared storage mode, a table containing a synchronous full-text index does not support triggering an offline rebuild operation. For support details on various DDL operations, see DDL support.
Syntax for creating an index with the table
When creating a table, you can create a full-text index using FULLTEXT INDEX. The following examples show how to do this using the default tokenizer and by specifying a tokenizer.
Create table
tbl1and create full-text indexfull_idx1_tbl1at the same time.CREATE TABLE tbl1(id INT, name VARCHAR(18), date DATE, PRIMARY KEY (id), FULLTEXT INDEX full_idx1_tbl1(name));Create table
tbl2and create full-text indexfull_idx1_tbl2at the same time. Specify the tokenizer for the full-text index asNGRAM, and set tokenizer properties usingPARSER_PROPERTIES.CREATE TABLE tbl2(id INT, name VARCHAR(18), doc TEXT, FULLTEXT INDEX full_idx1_tbl2(name, doc) WITH PARSER NGRAM PARSER_PROPERTIES=(ngram_token_size=3));
For the complete syntax, see CREATE TABLE. For tokenizer parameters, see Tokenizer parameters.
Syntax for creating an index after table creation
For an existing table, you can use CREATE FULLTEXT INDEX to create an index, or use ALTER TABLE to add an index.
CREATE FULLTEXT INDEX syntax
After creating table tbl3, create full-text index ft_idx1_tbl3.
Create table
tbl3.CREATE TABLE tbl3(col1 INT, col2 VARCHAR(4096));Create full-text index
ft_idx1_tbl3on tabletbl3. Specify the tokenizer for the full-text index asIK, and set tokenizer properties usingPARSER_PROPERTIES.CREATE FULLTEXT INDEX ft_idx1_tbl3 ON tbl3(col2) WITH PARSER IK PARSER_PROPERTIES=(ik_mode='max_word');
For the complete syntax, see CREATE INDEX.
ALTER TABLE syntax
After creating table tbl4, add full-text index ft_idx1_tbl4.
Create table
tbl4.CREATE TABLE tbl4(col1 INT, col2 TEXT);Add a full-text index named
ft_idx1_tbl4to thetbl4table. Specify the tokenizer for the full-text index asBENG, and set the tokenizer properties usingPARSER_PROPERTIES.ALTER TABLE tbl4 ADD FULLTEXT INDEX ft_idx1_tbl4(col2) WITH PARSER BENG PARSER_PROPERTIES=(min_token_size=2, max_token_size=64);
For the complete syntax, see ALTER TABLE.
Asynchronous full-text index
Note
Asynchronous full-text index is supported starting from V4.6.2.1.
Notice
Asynchronous full-text index is an experimental feature in the current version and is not supported for production environments.
Asynchronous full-text index (ASYNC FULLTEXT) changes index maintenance to background incremental refresh, sacrificing some visible latency for improved write and query performance. It is currently mainly used in hybrid search scenarios; queries guarantee eventual consistency but do not guarantee strong consistency or real-time visibility. For more differences, see Synchronous vs asynchronous indexing.
Considerations
In addition to the general instructions and limitations mentioned above, note the following:
- Only heap tables (
ORGANIZATION = HEAP) are supported. - In shared storage mode, tables containing asynchronous full-text indexes support partial offline DDL operations. For details, see DDL support.
- The asynchronous index feature must be manually enabled.
- Background incremental refresh only guarantees eventual consistency; data may be delayed.
- Currently, asynchronous full-text index and asynchronous search index are supported; asynchronous vector index and asynchronous normal index are not supported.
- For tables containing asynchronous indexes,
UPDATEoperations are converted toDELETE+INSERT. - Asynchronous indexes support automatic refresh, automatic merge, and manual merge. You can configure how to handle refresh failures using
REFRESH_ON_ERROR. For details, see Maintain full-text indexes (asynchronous only).
Enable the asynchronous index feature
The asynchronous index feature is disabled by default and must be enabled using the _enable_async_index parameter:
ALTER SYSTEM SET _enable_async_index = true;
Create with the table or create later
To create an asynchronous full-text index, add the ASYNC keyword before the existing full-text index syntax. You can create the index together with the table or after the table is created. Example:
CREATE TABLE tbl5 (
col1 INT,
col2 TEXT,
ASYNC FULLTEXT INDEX ft_idx1_tbl5(col2)
WITH PARSER BENG
PARSER_PROPERTIES=(min_token_size=2, max_token_size=64)
) ORGANIZATION HEAP;
CREATE ASYNC FULLTEXT INDEX ft_idx1_tbl5 ON tbl5(col2)
WITH PARSER BENG
PARSER_PROPERTIES=(min_token_size=2, max_token_size=64);
For an end-to-end example, see Asynchronous full-text indexing and hybrid search below.
Update and delete data
For tables with full-text indexes created, complex DML operations are supported, including INSERT INTO ON DUPLICATE KEY, REPLACE INTO, update/delete across multiple tables, and updating views.
Create a sample table and a sample view:
CREATE TABLE tbl1 (a int PRIMARY KEY, b text, FULLTEXT INDEX(b));
CREATE TABLE tbl2 (a int PRIMARY KEY, b text);
CREATE VIEW fts_view AS SELECT * FROM tbl1;
An example of the INSERT INTO ON DUPLICATE KEY operation is as follows:
INSERT INTO articles VALUES ('OceanBase AI', 'Fulltext search index support insert into on duplicate key')
ON DUPLICATE KEY UPDATE title = 'OceanBase AI 4.6.3';
An example of the REPLACE INTO operation is as follows:
REPLACE INTO articles(title, context) VALUES ('Oceanbase AI 4.6.3', 'Fulltext search index support replace');
An example of update/delete across multiple tables is as follows:
UPDATE tbl1 JOIN tbl2 ON tbl1.a = tbl2.a
SET tbl1.b = 'dddd', tbl2.b = 'eeee';
UPDATE tbl1 JOIN tbl2 ON tbl1.a = tbl2.a SET tbl1.b = 'dddd';
UPDATE tbl1 JOIN tbl2 ON tbl1.a = tbl2.a SET tbl2.b = tbl1.b;
DELETE tbl1, tbl2 FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a;
DELETE tbl1 FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a;
DELETE tbl1 FROM tbl1 JOIN tbl2 ON tbl1.a = tbl2.a;
An example of DML on updatable views is as follows:
INSERT INTO fts_view VALUES(3, 'cccc'), (4, 'dddd');
UPDATE fts_view SET b = 'dddd';
UPDATE fts_view JOIN normal ON fts_view.a = tbl2.a
SET fts_view.b = 'dddd', tbl2.b = 'eeee';
DELETE FROM fts_view WHERE b = 'dddd';
DELETE tbl1 FROM fts_view JOIN tbl1 ON fts_view.a = tbl1.a AND 1 = 0;
Search data
The following sections describe how to search data using both synchronous and asynchronous full-text indexes.
Search using a synchronous full-text index
This example defines a table to store document information and sets up a full-text index for the documents. You can use the full-text index to quickly match documents containing the desired keywords and sort the results by similarity from highest to lowest.
Create a table.
CREATE TABLE Articles ( id INT AUTO_INCREMENT, title VARCHAR(255) , content TEXT , PRIMARY KEY (id), FULLTEXT ft1 (content) WITH PARSER SPACE );Insert data.
INSERT INTO Articles (title, content) VALUES ('OceanBase AI Database overview', 'OceanBase AI Database is an AI-native search database. It unifies relational, vector, text, JSON, and GIS in a single engine, enabling hybrid search and in-database AI workflows.'); ('Full-Text Search in Databases', 'Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly.'), ('Advantages of Using OceanBase AI Database', 'OceanBase AI Database offers several advantages such as high performance, reliability, and ease of use. ');Query the table.
select * from Articles;+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | id | title | content | +----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 1 | OceanBase AI Database overview | OceanBase AI Database is an AI-native search database. It unifies relational, vector, text, JSON, and GIS in a single engine, enabling hybrid search and in-database AI workflows. | | 2 | Full-Text Search in Databases | Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly. | | 3 | Advantages of Using OceanBase AI Database| OceanBase AI Database offers several advantages such as high performance, reliability, and ease of use. | +----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.002 sec)Query matching documents.
select id,title, content,match(content) against('OceanBase AI Database database') score from Articles where match(content) against('OceanBase AI Database database');+----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+ | id | title | content | score | +----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+ | 1 | OceanBase AI Database overview | OceanBase AI Database is an AI-native search database. It unifies relational, vector, text, JSON, and GIS in a single engine, enabling hybrid search and in-database AI workflows. | 0.4570384669555348 | | 3 | Advantages of Using OceanBase AI Database| OceanBase AI Database offers several advantages such as high performance, reliability, and ease of use. | 0.240174672489083 | | 2 | Full-Text Search in Databases | Full-text search allows for searching within the text of documents stored in a database. It is particularly useful for finding specific information quickly. | 0.20072992700729927 | +----+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+ 3 rows in set (0.003 sec)Use the EXPLAIN statement to view the query plan and analyze its performance.
explain select id,title, content,match(content) against('OceanBase AI Database database') score from Articles where match(content) against('OceanBase AI Database database');+-------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | -------------------------------------------------------------- | | |0 |SORT | |4 |139 | | | |1 |└─TEXT RETRIEVAL SCAN|articles(ft1)|4 |138 | | | ============================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([articles.id], [articles.title], [articles.content], [MATCH(articles.content) AGAINST('OceanBase AI Database database')]), filter(nil), rowset=16 | | sort_keys([MATCH(articles.content) AGAINST('OceanBase AI Database database'), DESC]) | | 1 - output([articles.id], [articles.content], [articles.title], [MATCH(articles.content) AGAINST('OceanBase AI Database database')]), filter(nil), rowset=16 | | access([articles.id], [articles.content], [articles.title]), partitions(p0) | | is_index_back=true, is_global_index=false, | | calc_relevance=true, match_expr(MATCH(articles.content) AGAINST('OceanBase AI Database database')), | | pushdown_match_filter(MATCH(articles.content) AGAINST('OceanBase AI Database database')) | +-------------------------------------------------------------------------------------------------------------------------------------------------+ 15 rows in set (0.002 sec)
Asynchronous full-text index and hybrid search
This example creates a heap table with asynchronous full-text indexes and vector indexes, and uses HYBRID_SEARCH for full-text search and hybrid full-text and vector search. The dsl_string syntax is the same as that in the synchronous full-text index scenario. For more information about hybrid search scenarios, see Hybrid search.
Create asynchronous full-text indexes.
Create with tableCreate laterCREATE TABLE doc_async ( c1 INT, vector VECTOR(3), content VARCHAR(255), VECTOR INDEX idx_vec(vector) WITH (distance=l2, type=hnsw_sq, lib=vsag), ASYNC FULLTEXT INDEX idx_content(content) ) ORGANIZATION HEAP;CREATE TABLE doc_async ( c1 INT, vector VECTOR(3), content VARCHAR(255), VECTOR INDEX idx_vec(vector) WITH (distance=l2, type=hnsw_sq, lib=vsag) ) ORGANIZATION HEAP; ALTER TABLE doc_async ADD ASYNC FULLTEXT INDEX idx_content(content); -- or CREATE ASYNC FULLTEXT INDEX idx_content ON doc_async(content);Insert data.
INSERT INTO doc_async VALUES (1, '[1,2,3]', 'oceanbase Elasticsearch database'), (2, '[1,2,1]', 'oceanbase mysql database'), (3, '[1,1,1]', 'oceanbase oracle database');Perform a full-text search (using the asynchronous full-text indexes).
SELECT c1, content FROM HYBRID_SEARCH( TABLE doc_async, '{ "query": { "match": {"content": "oceanbase mysql"} } }' );Perform a hybrid full-text and vector search.
SELECT c1, content FROM HYBRID_SEARCH( TABLE doc_async, '{ "query": { "match": {"content": "oceanbase mysql"} }, "knn": { "field": "vector", "k": 3, "query_vector": "[1,2,3]" }, "rank": { "rrf": { "rank_constant": 60 } } }' );
Maintain a full-text index (asynchronous only)
Automatic refresh and major compaction
Automatic refresh and major compaction for asynchronous indexes are configured at the table level. The following parameters are currently supported:
Parameter |
Type |
Value range |
Default value |
Meaning |
|---|---|---|---|---|
ASYNC_REFRESH_INTERVAL |
INT (seconds) | [1, 60], -1 |
5 |
Controls the scan cycle of the asynchronous index background incremental refresh service. It checks at every interval whether there is new incremental data in the table, and triggers an incremental refresh task if any is found. -1 indicates that background refresh is disabled. This parameter is supported starting from V4.6.2.1. |
REFRESH_ON_ERROR |
Enumeration | SKIP / ABORT |
ABORT |
Controls the handling strategy when a single row of abnormal data is encountered during incremental refresh. ABORT indicates that the entire refresh fails and blocks subsequent incremental refreshes, which is suitable for scenarios with extremely high requirements on data integrity. SKIP indicates that the abnormal row is skipped and the refresh continues without blocking other rows or indexes, and the row is not written to the index. Note: Resource or environment errors are not skipped even in SKIP mode. This parameter is supported starting from V4.6.2.1. |
DELETE_PURGE_THRESHOLD |
INT | [20, 50] |
33 |
Controls the cleanup threshold for deleted data in asynchronous indexes. When the proportion of deleted rows exceeds this threshold, a major compaction is triggered to clean up the deleted data. |
MERGE_SEGMENT_THRESHOLD |
INT | [2, 48] |
10 |
Controls the threshold for the number of segments in the asynchronous index merge strategy. When the number of segments generated by incremental refreshes exceeds this value, a major compaction is triggered to merge multiple small segments into a larger one. |
Notice
When an asynchronous index is created with a table, data written to the table is not validated against whether it can be written to the index. If rows cannot be written to the index, incremental refresh will be blocked under the default ABORT strategy, preventing subsequent increments from being queried through this index. In this case, you can set REFRESH_ON_ERROR to SKIP to skip abnormal rows and continue refresh.
-- Set the refresh cycle upon table creation
CREATE TABLE doc_async (
c1 INT,
content VARCHAR(255),
ASYNC FULLTEXT INDEX idx_content(content)
) ORGANIZATION HEAP ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10';
-- Modify only the refresh interval and keep other configurations unchanged.
ALTER TABLE doc_async SET ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10';
-- Skip Abnormal Rows During Incremental Refresh
ALTER TABLE doc_async SET ASYNC_INDEX_PARAMS = 'REFRESH_ON_ERROR=SKIP';
-- Modify Multiple Configurations Simultaneously
ALTER TABLE doc_async SET ASYNC_INDEX_PARAMS = 'ASYNC_REFRESH_INTERVAL=10, REFRESH_ON_ERROR=SKIP, DELETE_PURGE_THRESHOLD=30, MERGE_SEGMENT_THRESHOLD=8';
For complete syntax details, see ALTER TABLE.
Manual major compaction
In addition to automatic major compaction based on MERGE_SEGMENT_THRESHOLD, you can also manually trigger a major compaction using the DBMS_ASYNC_INDEX system package:
-- Manually trigger a major compaction
CALL DBMS_ASYNC_INDEX.FORCEMERGE('doc_async');
For detailed syntax and parameters, see Overview of DBMS_ASYNC_INDEX.
Application scenarios
Full-text indexes are suitable for scenarios that require keyword search within large volumes of text data, as well as sorting results and performing top-k recall based on text relevance. Examples include searching for documents, articles, product descriptions, and business records.
When text search needs to scan large amounts of data and perform string matching row by row, the query cost increases with the data scale and the number of rows. Full-text indexes reduce scanning and text-matching overhead for full-text search queries by tokenizing the text and creating an inverted index based on tokens. They also support text-relevance calculation and sorting. Queries can be combined with other structured conditions to narrow down the search scope.
Typical scenarios include:
- Enterprise knowledge base systems: Search for text data such as project documents, meeting minutes, and research reports by keyword, and filter results based on conditions like document classification or product version.
- Book and digital content platforms: Search for titles, abstracts, and bodies of content such as books and papers to help users find data containing specified keywords.
- News and community content search: Search for news, posts, and comments containing specified keywords, filter them based on conditions like publication time or category, and sort them by text relevance.
- Legal document search: Search for contracts, court judgments, and regulatory provisions by legal terms or business keywords to assist in locating documents for review.
- Medical record and literature search: Search for keywords such as disease names or drug names in medical record text, medical papers, and reference materials to improve the efficiency of information retrieval.
When using a full-text index, you can select an appropriate tokenizer or text analyzer based on the text language and business query requirements, and evaluate the search effectiveness, query performance, index storage, and write/maintenance costs.