This topic describes the hybrid search SQL interface of OceanBase Database. The interface uses the HYBRID_SEARCH keyword to combine full-text search, vector search, and filter conditions in a single SELECT statement, and returns results fused and ranked by relevance.
Hybrid search combines vector-based semantic search with keyword search based on full-text indexes. Fusion ranking provides more accurate and comprehensive results: vector search excels at approximate semantic matching but is less effective for exact keywords, numbers, and proper nouns, while full-text search complements those limitations. Hybrid search has therefore become a key capability of vector databases and is widely used in many products.
Limitations
- Currently, the
HYBRID_SEARCHSQL clause is only supported on heap tables. - Vector search requires a vector index on the target vector column. Multi-vector search currently supports only dense vector columns.
- Full-text search requires a full-text index on the target text column. If a full-text index spans multiple columns, it cannot be used for hybrid search.
- Scalar filtering and JSON/ARRAY filtering can be executed without an index, but it is recommended to create the corresponding index for better performance.
- The current version supports only the HNSW family of vector indexes.
- Only row-store tables are supported.
- Generated columns are not supported.
This section lists only the feature limitations. For syntax limitations, see the HYBRID_SEARCH document in the References section.
Syntax
SELECT select_list
FROM HYBRID_SEARCH(TABLE table_name, dsl_string);
The parameters are described as follows:
table_name: The name of the target table. Only heap tables (ORGANIZATION = HEAP) are supported. Partitioned and non-partitioned tables are supported.dsl_string: A JSON string that describes full-text search, vector search, filtering, and fused ranking.
For complete syntax, parameter descriptions, and limitations, see HYBRID_SEARCH in the References section.
Create sample tables and insert data
This topic uses the same sample tables as Index-based hybrid search (PL interface).
doc_table: A sample table for hybrid search and reranking using full-text search, vector search, and scalar filtering.
CREATE TABLE doc_table(
c1 INT,
vector VECTOR(3),
query VARCHAR(255),
content VARCHAR(255),
VECTOR INDEX idx_vec(vector) WITH (distance=l2, type=hnsw_sq, lib=vsag),
FULLTEXT INDEX idx_query(query),
FULLTEXT INDEX idx_content(content)
) ORGANIZATION HEAP;
-- To try hybrid search with full-text search, vector search, and scalar filtering, insert only the following data, not the reranking data.
INSERT INTO doc_table VALUES
(1, '[1,2,3]', 'hello world', 'oceanbase Elasticsearch database'),
(2, '[1,2,1]', 'hello world, what is your name', 'oceanbase mysql database'),
(3, '[1,1,1]', 'hello world, how are you', 'oceanbase oracle database'),
(4, '[1,3,1]', 'real world, where are you from', 'postgres oracle database'),
(5, '[1,3,2]', 'real world, how old are you', 'redis oracle database'),
(6, '[2,1,1]', 'hello world, where are you from', 'starrocks oceanbase database');
-- To try reranking, insert only the following data.
INSERT INTO doc_table VALUES
(1, '[1,2,3]', 'hello world', 'OceanBase is a distributed relational database with strong consistency and high availability across multiple zones'),
(2, '[1,2,1]', 'hello world, what is your name', 'MySQL is an open-source database popular for web applications and content management systems'),
(3, '[1,1,1]', 'hello world, how are you', 'Redis is an in-memory key-value store used as database cache and message broker for fast data access'),
(4, '[1,3,1]', 'real world, where are you from', 'MongoDB is a document-oriented NoSQL database designed for flexible schema and unstructured data storage'),
(5, '[1,3,2]', 'real world, how old are you', 'TiDB is a distributed NewSQL database that supports horizontal scaling and online transaction processing'),
(6, '[2,1,1]', 'hello world, where are you from', 'PostgreSQL is a single-node relational database known for extensibility and advanced SQL compliance');
products_multi_vector: A sample table for multi-vector search.
CREATE TABLE products_multi_vector (
product_id VARCHAR(50),
product_name VARCHAR(255),
description TEXT,
vec1 VECTOR(4),
vec2 VECTOR(4),
vec3 VECTOR(4),
VECTOR INDEX idx_vec1(vec1) WITH (distance=l2, type=hnsw_sq, lib=vsag),
VECTOR INDEX idx_vec2(vec2) WITH (distance=l2, type=hnsw_sq, lib=vsag),
VECTOR INDEX idx_vec3(vec3) WITH (distance=l2, type=hnsw_sq, lib=vsag)
) ORGANIZATION HEAP;
INSERT INTO products_multi_vector VALUES
('prod-001', 'Gamer-Pro Mechanical Keyboard', 'A responsive mechanical keyboard', '[0.5,0.1,0.6,0.9]', '[0.2,0.3,0.4,0.5]', '[0.1,0.2,0.3,0.4]'),
('prod-002', 'Gamer-Pro Headset', 'High-fidelity gaming headset', '[0.1,0.9,0.2,0]', '[0.3,0.4,0.5,0.6]', '[0.2,0.3,0.4,0.5]'),
('prod-003', 'Eco-Friendly Yoga Mat', 'A non-slip yoga mat', '[0.1,0.9,0.3,0]', '[0.4,0.5,0.6,0.7]', '[0.3,0.4,0.5,0.6]');
doc_json_array: A sample table for hybrid search with JSON and ARRAY filtering.
CREATE TABLE doc_json_array (
id INT,
created_date date,
title varchar(255),
doc_json JSON,
tags_array1 ARRAY(VARCHAR(255)),
tags_array2 ARRAY(VARCHAR(255)),
INDEX idx_multivalue_tags((CAST(doc_json->'$.tags' AS CHAR(255) ARRAY))),
INDEX idx1(id),
INDEX idx2(title),
INDEX idx_created_date(created_date)
) ORGANIZATION HEAP;
INSERT INTO doc_json_array VALUES
(1, '2023-01-01', 'doc1', '{"name":"doc1","tags":["database","oceanbase"],"metadata":{"type":"test","score":40}}', ['database','oceanbase'], ['database','mysql']),
(2, '2023-01-02', 'doc2', '{"name":"doc2","tags":["database","mysql"],"metadata":{"type":"production","score":57}}', ['database','mysql'], ['database','mysql']),
(3, '2023-01-03', 'doc3', '{"name":"doc3","tags":["database","oracle"],"metadata":{"type":"test","score":19}}', ['database','oracle'], ['database','oracle']),
(4, '2023-01-04', 'doc4', '{"name":"doc4","tags":["database","postgres"],"metadata":{"type":"production","score":14}}', ['database','postgres'], ['database','postgres']),
(6, '2023-01-06', 'doc6', '{"name":"doc6","tags":["database","starrocks"],"metadata":{"type":"production","score":25}}', ['database','starrocks'], ['database','starrocks']),
(10, '2023-01-10', 'doc10', '{"name":"doc10","tags":["mobile","ios"],"metadata":{"type":"app","score":90}}', ['mobile','ios'], ['mobile','ios']);
-- We recommend that you create a search index after inserting data to achieve optimal search performance.
CREATE SEARCH INDEX idx_json ON doc_json_array(doc_json);
CREATE SEARCH INDEX idx_tags_array1 ON doc_json_array(tags_array1);
CREATE SEARCH INDEX idx_tags_array2 ON doc_json_array(tags_array2);
The following sections provide quick-start and extended examples for different usage scenarios.
Quick start example
This section provides four core examples covering common hybrid search scenarios: vector search, full-text search, vector and full-text search with RRF fusion, and multi-vector search.
Vector search
This example searches for the three records in the doc_table table that are most similar to the vector [1,2,3] and returns the c1 column.
SELECT c1 FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"knn": {
"field": "vector",
"k": 3,
"query_vector": "[1,2,3]"
}
}'
);
The expected return is as follows:
+------+
| c1 |
+------+
| 1 |
| 5 |
| 2 |
+------+
3 rows in set
Full-text search
This example searches for four records in the doc_table table where the content column contains oceanbase mysql and returns all columns.
SELECT * FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"match": {"content": "oceanbase mysql"}
}
}'
);
The expected return is as follows:
+------+---------+---------------------------------+----------------------------------+--------------------+
| c1 | vector | query | content | __score |
+------+---------+---------------------------------+----------------------------------+--------------------+
| 2 | [1,2,1] | hello world, what is your name | oceanbase mysql database | 2.170969786679347 |
| 1 | [1,2,3] | hello world | oceanbase Elasticsearch database | 0.3503184713375797 |
| 3 | [1,1,1] | hello world, how are you | oceanbase oracle database | 0.3503184713375797 |
| 6 | [2,1,1] | hello world, where are you from | starrocks oceanbase database | 0.3503184713375797 |
+------+---------+---------------------------------+----------------------------------+--------------------+
4 rows in set
Full-text and vector RRF hybrid search
This example statement simultaneously performs a full-text search (matching the keyword "oceanbase mysql") and a vector search (searching for the five records most similar to the vector [1,2,3]). Then, it merges the two results using the RRF fusion algorithm. By default, it returns the ten documents most relevant to the query, with a total of six matching records.
SELECT * FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"match": {"content": "oceanbase mysql"}
},
"knn": {
"field": "vector",
"k": 5,
"query_vector": "[1,2,3]"
},
"rank": {
"rrf": {
"rank_constant": 60,
"rank_window_size": 10
}
}
}'
);
The expected return is as follows:
+------+---------+---------------------------------+----------------------------------+----------------------+
| c1 | vector | query | content | __score |
+------+---------+---------------------------------+----------------------------------+----------------------+
| 1 | [1,2,3] | hello world | oceanbase Elasticsearch database | 0.03252247488101534 |
| 2 | [1,2,1] | hello world, what is your name | oceanbase mysql database | 0.032266458495966696 |
| 3 | [1,1,1] | hello world, how are you | oceanbase oracle database | 0.031754032258064516 |
| 5 | [1,3,2] | real world, how old are you | redis oracle database | 0.016129032258064516 |
| 6 | [2,1,1] | hello world, where are you from | starrocks oceanbase database | 0.016129032258064516 |
| 4 | [1,3,1] | real world, where are you from | postgres oracle database | 0.015625 |
+------+---------+---------------------------------+----------------------------------+----------------------+
6 rows in set
Multi-vector search
This example statement performs independent vector searches on three vector fields (vec1, vec2, vec3), returning five most similar results for each. It then merges them using the default weighted fusion algorithm and returns the document with the highest overall relevance.
SELECT * FROM HYBRID_SEARCH(
TABLE products_multi_vector,
'{
"knn": [
{"field":"vec1","k":5,"query_vector":"[0.5,0.1,0.6,0.9]"},
{"field":"vec2","k":5,"query_vector":"[0.2,0.3,0.4,0.5]"},
{"field":"vec3","k":5,"query_vector":"[0.1,0.2,0.3,0.4]"}
]
}'
);
The expected return results are as follows:
+------------+-------------------------------+----------------------------------+-------------------+-------------------+-------------------+--------------------+
| product_id | product_name | description | vec1 | vec2 | vec3 | __score |
+------------+-------------------------------+----------------------------------+-------------------+-------------------+-------------------+--------------------+
| prod-002 | Gamer-Pro Headset | High-fidelity gaming headset | [0.1,0.9,0.2,0] | [0.3,0.4,0.5,0.6] | [0.2,0.3,0.4,0.5] | 2.7134181710480463 |
| prod-003 | Eco-Friendly Yoga Mat | A non-slip yoga mat | [0.1,0.9,0.3,0] | [0.4,0.5,0.6,0.7] | [0.3,0.4,0.5,0.6] | 2.6366155630441215 |
| prod-001 | Gamer-Pro Mechanical Keyboard | A responsive mechanical keyboard | [0.5,0.1,0.6,0.9] | [0.2,0.3,0.4,0.5] | [0.1,0.2,0.3,0.4] | 2.6237901221768167 |
+------------+-------------------------------+----------------------------------+-------------------+-------------------+-------------------+--------------------+
3 rows in set
Extended examples
This section provides advanced hybrid search examples, including filter-condition merging, score-threshold filtering, complex-type filtering (JSON and ARRAY), vector candidate sizing with num_candidates, wildcard filtering, weighted vector and full-text fusion, WRRF fusion, normalization, and reranking.
Filter condition merging
For multiple scalar filter conditions on the same column under the same bool.must or bool.filter path, these conditions are still intersected using the AND logic to filter out records that satisfy all conditions. The query optimizer merges these conditions during the logical plan phase to avoid repeated iteration during execution. In the following example, setting c1 >= 3 and c1 <= 5 (equivalent to c1 >= 3 AND c1 <= 5) means only rows where c1 is 3, 4, or 5 will be returned.
SELECT c1 FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": { "bool": {
"filter": [
{"range": {"c1": {"gte" : 3}}},
{"range": {"c1": {"lte" : 5}}}
]
}}
}');
The expected return results are as follows:
+------+
| c1 |
+------+
| 3 |
| 4 |
| 5 |
+------+
3 rows in set
Score threshold filtering (min_score)
Note
The min_score parameter is supported only by the SQL interface; the PL interface does not support it.
This example searches doc_table for two rows whose content column contains oceanbase mysql, sets the score threshold to 0.5, and returns all columns.
SELECT * FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {"match": {"content":{"query": "oceanbase mysql", "boost": 0.3}}},
"knn": {"field":"vector","k":5,"query_vector":"[1,2,3]", "boost": 0.7},
"min_score": 0.5
}'
);
The expected return results are as follows:
+------+---------+--------------------------------+----------------------------------+--------------------+
| c1 | vector | query | content | __score |
+------+---------+--------------------------------+----------------------------------+--------------------+
| 1 | [1,2,3] | hello world | oceanbase Elasticsearch database | 0.8050955414012738 |
| 2 | [1,2,1] | hello world, what is your name | oceanbase mysql database | 0.7912909360038041 |
+------+---------+--------------------------------+----------------------------------+--------------------+
2 rows in set
Scalar + JSON/ARRAY filtering
These examples require the use of search indexes. In hybrid search scenarios, full-text tokenization and keyword retrieval are handled by the full-text index, while the search index is used for structured, semi-structured, or simple scalar conditions on the internal paths of complex types. The two apply to different column types, can coexist in the same table, and work together with the vector index to improve query performance.
Furthermore, in analytical wide-table scenarios, the search index can also leverage the optimizer's multi-index join query (Index Merge) capability. It automatically identifies and combines available full-text and scalar indexes to select the optimal scan path, further enhancing query performance. For usage, syntax, and complete examples of search indexes, see Search Index.
JSON examples
JSON_CONTAINS
The JSON_CONTAINS() function determines whether a specified
candidatesubdocument is contained within the target JSON document, or whether the element exists at the given pathpath(optional).SELECT id, doc_json FROM HYBRID_SEARCH( TABLE doc_json_array, '{ "query": { "bool" : { "filter" : [{ "json_contains":{ "doc_json": { "candidate": "doc2", "path": "$.name" } } }] } } }');The expected return is as follows:
+------+--------------------------------------------------------------------------------------------------+ | id | doc_json | +------+--------------------------------------------------------------------------------------------------+ | 2 | {"name": "doc2", "tags": ["database", "mysql"], "metadata": {"type": "production", "score": 57}} | +------+--------------------------------------------------------------------------------------------------+ 1 row in setJSON_OVERLAPS
The JSON_OVERLAPS() function determines whether two JSON documents have any common key-value pairs or array elements.
pathis an optional parameter.SELECT id, doc_json FROM HYBRID_SEARCH( TABLE doc_json_array, '{ "query": { "bool" : { "filter" : [{ "json_overlaps":{ "doc_json": { "candidate": "[\\"database\\", \\"mysql\\"]", "path": "$.tags" } } }] } } }');The expected return is as follows:
+------+------------------------------------------------------------------------------------------------------+ | id | doc_json | +------+------------------------------------------------------------------------------------------------------+ | 1 | {"name": "doc1", "tags": ["database", "oceanbase"], "metadata": {"type": "test", "score": 40}} | | 2 | {"name": "doc2", "tags": ["database", "mysql"], "metadata": {"type": "production", "score": 57}} | | 3 | {"name": "doc3", "tags": ["database", "oracle"], "metadata": {"type": "test", "score": 19}} | | 4 | {"name": "doc4", "tags": ["database", "postgres"], "metadata": {"type": "production", "score": 14}} | | 6 | {"name": "doc6", "tags": ["database", "starrocks"], "metadata": {"type": "production", "score": 25}} | +------+------------------------------------------------------------------------------------------------------+ 5 rows in setJSON_MEMBER_OF
The JSON_MEMBER_OF() function determines whether the element being searched for is equal to any element in a JSON array.
pathis an optional parameter.SELECT id, doc_json FROM HYBRID_SEARCH( TABLE doc_json_array, '{ "query": { "bool" : { "filter" : [{ "json_member_of":{ "doc_json": { "candidate": "\\"database\\"" } } }] } } }');
The expected result set is empty.
JSON_EXTRACT
The JSON_EXTRACT() function returns data from a specified path in a JSON document.
-- Extract the node with the key "name" from the JSON field `doc_json` and check if its value equals "doc2". SELECT id, doc_json FROM HYBRID_SEARCH( TABLE doc_json_array, '{ "query": { "bool" : { "filter" : [{ "term":{"doc_json.name": "doc2"} }] } } }');The expected return results are as follows:
+------+--------------------------------------------------------------------------------------------------+ | id | doc_json | +------+--------------------------------------------------------------------------------------------------+ | 2 | {"name": "doc2", "tags": ["database", "mysql"], "metadata": {"type": "production", "score": 57}} | +------+--------------------------------------------------------------------------------------------------+ 1 row in set
ARRAY examples
Syntax definition:
-- Syntax of ARRAY Expressions
{
"array_func" : {
"field_name" : "value"
}
}
- ARRAY_CONTAINS
The ARRAY_CONTAINS() function determines whether a specific element is contained in an array.
SELECT id, tags_array1 FROM HYBRID_SEARCH(
TABLE doc_json_array,
'{
"query": {
"bool" : {
"filter" : [
{ "array_contains": { "tags_array1" : "ios" }}
]
}
}
}');
The expected return result is as follows:
+------+------------------+
| id | tags_array1 |
+------+------------------+
| 10 | ["mobile","ios"] |
+------+------------------+
1 row in set
- ARRAY_CONTAINS_ALL
The ARRAY_CONTAINS_ALL() function determines whether one array contains all elements of another array.
SELECT id, tags_array1 FROM HYBRID_SEARCH(
TABLE doc_json_array,
'{
"query": {
"bool" : {
"filter" : [
{ "array_contains_all": { "tags_array1": ["database", "postgres"]} }
]
}
}
}');
The expected return result is as follows:
+------+-------------------------+
| id | tags_array1 |
+------+-------------------------+
| 4 | ["database","postgres"] |
+------+-------------------------+
1 row in set
- ARRAY_OVERLAPS
The ARRAY_OVERLAPS() function determines whether two arrays have any overlapping elements.
SELECT id, tags_array1 FROM HYBRID_SEARCH(
TABLE doc_json_array,
'{
"query": {
"bool" : {
"filter" : [
{ "array_overlaps": { "tags_array1": ["database", "postgres", "oceanbase"]} }
]
}
}
}');
The expected return result is as follows:
+------+--------------------------+
| id | tags_array1 |
+------+--------------------------+
| 1 | ["database","oceanbase"] |
| 2 | ["database","mysql"] |
| 3 | ["database","oracle"] |
| 4 | ["database","postgres"] |
| 6 | ["database","starrocks"] |
+------+--------------------------+
5 rows in set
Number of vector candidates (num_candidates)
Note
This feature is supported starting with V4.6.0 BP1.
num_candidates specifies the size of the candidate set during vector search. It is mapped to ef_search at the execution layer, facilitating migration from other search systems. The value range is integers in [k, 10000]. When both num_candidates and search_options.ef_search are set, num_candidates takes precedence; sub-items such as refine_k and filter_mode in search_options can still take effect. For a complete description of priorities, including those of filter_mode, and examples, see the relevant sections in the HYBRID_SEARCH syntax document at the end of this topic.
wildcard filtering
Note
This feature is supported starting with V4.6.0 BP1.
wildcard is used for wildcard fuzzy matching on scalar columns, with semantics equivalent to SQL LIKE ... ESCAPE '\\': * matches any number of characters, and ? matches any single character. wildcard can be used for scoring in bool.must or bool.should, or for hard filtering in bool.filter or knn.filter. Normal indexes on VARCHAR columns can accelerate queries via index scan paths; prefix indexes, full-text indexes, and JSON-related indexes do not support this.
For more information and examples, see the Fuzzy match section in the HYBRID_SEARCH syntax document at the end of this topic.
Other hybrid search scenarios
Weighted hybrid of vector and full-text search
Weighted fusion is the default hybrid search method in OceanBase AI. The default weight is 0.3 for full-text search and 0.7 for vector search.
SELECT * FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"match": {"content": {"query": "oceanbase mysql", "boost": 0.3}}
},
"knn": {
"field": "vector",
"k": 5,
"query_vector": "[1,2,3]",
"boost": 0.7
}
}'
);
The expected return result is as follows:
+------+---------+---------------------------------+----------------------------------+---------------------+
| c1 | vector | query | content | __score |
+------+---------+---------------------------------+----------------------------------+---------------------+
| 1 | [1,2,3] | hello world | oceanbase Elasticsearch database | 0.8050955414012738 |
| 2 | [1,2,1] | hello world, what is your name | oceanbase mysql database | 0.7912909360038041 |
| 5 | [1,3,2] | real world, how old are you | redis oracle database | 0.2333333333333333 |
| 3 | [1,1,1] | hello world, how are you | oceanbase oracle database | 0.22176220806794056 |
| 4 | [1,3,1] | real world, where are you from | postgres oracle database | 0.11666666666666665 |
| 6 | [2,1,1] | hello world, where are you from | starrocks oceanbase database | 0.1050955414012739 |
+------+---------+---------------------------------+----------------------------------+---------------------+
6 rows in set
WRRF hybrid of vector and full-text search
WRRF supports boost weights on top of RRF. In this example, the boost parameters under query and knn specify the weights used for WRRF fusion.
SELECT * FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"match": {"content": {"query": "oceanbase mysql", "boost": 0.3}}
},
"knn": {
"field": "vector",
"k": 5,
"query_vector": "[1,2,3]",
"boost": 0.7
},
"rank": {
"rrf": {
"rank_constant": 60,
"rank_window_size": 10
}
}
}'
);
The expected return result is as follows:
+------+---------+---------------------------------+----------------------------------+----------------------+
| c1 | vector | query | content | __score |
+------+---------+---------------------------------+----------------------------------+----------------------+
| 1 | [1,2,3] | hello world | oceanbase Elasticsearch database | 0.03252247488101534 |
| 2 | [1,2,1] | hello world, what is your name | oceanbase mysql database | 0.032266458495966696 |
| 3 | [1,1,1] | hello world, how are you | oceanbase oracle database | 0.031754032258064516 |
| 5 | [1,3,2] | real world, how old are you | redis oracle database | 0.016129032258064516 |
| 6 | [2,1,1] | hello world, where are you from | starrocks oceanbase database | 0.016129032258064516 |
| 4 | [1,3,1] | real world, where are you from | postgres oracle database | 0.015625 |
+------+---------+---------------------------------+----------------------------------+----------------------+
6 rows in set
Vector and full-text search normalization
You can set the normalizer parameter to normalize the query results. In this example, setting normalizer to "minmax" indicates using Min-Max normalization.
SELECT * FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"match": {"content": {"query": "oceanbase mysql", "boost": 0.3}}
},
"knn": {
"field": "vector",
"k": 5,
"query_vector": "[1,2,3]",
"boost": 0.7
},
"rank": {
"weighted_sum": {
"normalizer": "minmax",
"rank_window_size": 10
}
}
}'
);
The expected return result is as follows:
+------+---------+---------------------------------+----------------------------------+---------------------+
| c1 | vector | query | content | __score |
+------+---------+---------------------------------+----------------------------------+---------------------+
| 1 | [1,2,3] | hello world | oceanbase Elasticsearch database | 0.7 |
| 2 | [1,2,1] | hello world, what is your name | oceanbase mysql database | 0.328 |
| 5 | [1,3,2] | real world, how old are you | redis oracle database | 0.13999999999999999 |
| 3 | [1,1,1] | hello world, how are you | oceanbase oracle database | 0 |
| 4 | [1,3,1] | real world, where are you from | postgres oracle database | 0 |
| 6 | [2,1,1] | hello world, where are you from | starrocks oceanbase database | 0 |
+------+---------+---------------------------------+----------------------------------+---------------------+
6 rows in set
Reranking
Notice
Reranking is supported starting from V4.6.0 BP1.
Hybrid search first retrieves results by using query or knn, and then performs coarse ranking by using rank or the default fusion strategy. It then applies an AI reranking model to the coarse-ranked candidate set and returns size results based on semantic relevance.
Before use, you must register a model provider and specify the reranking model in the provider/model format. The following example uses the built-in provider aliyun-dashscope. Replace access_key with your actual API key.
CALL DBMS_AI_SERVICE.REGISTER_PROVIDER('aliyun-dashscope', '{
"access_key": "sk-xxxx"
}');
Notice
rerank cannot be used alone. It must be used with at least one of query or knn, and is applied after coarse ranking.
This example first retrieves the six records most similar to the vector [1,2,3], and then reranks them.
SELECT c1, content FROM hybrid_search(TABLE doc_table, '{
"knn": {
"field": "vector",
"k": 6,
"query_vector": [1, 2, 3]
},
"rerank": {
"model": "aliyun-dashscope/gte-rerank-v2",
"field": "content",
"query": "distributed database for transaction processing with high availability",
"rank_window_size": 6
}
}');
The expected return is as follows:
+------+--------------------------------------------------------------------------------------------------------------------+
| c1 | content |
+------+--------------------------------------------------------------------------------------------------------------------+
| 1 | OceanBase is a distributed relational database with strong consistency and high availability across multiple zones |
| 5 | TiDB is a distributed NewSQL database that supports horizontal scaling and online transaction processing |
| 2 | MySQL is an open-source database popular for web applications and content management systems |
| 4 | MongoDB is a document-oriented NoSQL database designed for flexible schema and unstructured data storage |
| 3 | Redis is an in-memory key-value store used as database cache and message broker for fast data access |
| 6 | PostgreSQL is a single-node relational database known for extensibility and advanced SQL compliance |
+------+--------------------------------------------------------------------------------------------------------------------+
6 rows in set
tab Single full-text search + reranking
This example first retrieves documents containing "database" by using full-text search, and then reranks them.
SELECT c1, content FROM hybrid_search(TABLE doc_table, '{
"query": {
"match" : {"content" : "database"}
},
"rerank": {
"model": "aliyun-dashscope/gte-rerank-v2",
"field": "content",
"query": "distributed database for transaction processing",
"rank_window_size": 6
}
}');
The expected return is as follows:
+------+--------------------------------------------------------------------------------------------------------------------+
| c1 | content |
+------+--------------------------------------------------------------------------------------------------------------------+
| 5 | TiDB is a distributed NewSQL database that supports horizontal scaling and online transaction processing |
| 1 | OceanBase is a distributed relational database with strong consistency and high availability across multiple zones |
| 2 | MySQL is an open-source database popular for web applications and content management systems |
| 4 | MongoDB is a document-oriented NoSQL database designed for flexible schema and unstructured data storage |
| 6 | PostgreSQL is a single-node relational database known for extensibility and advanced SQL compliance |
| 3 | Redis is an in-memory key-value store used as database cache and message broker for fast data access |
+------+--------------------------------------------------------------------------------------------------------------------+
6 rows in set
tab Hybrid search + reranking
This example first retrieves the four records most similar to the vector [1,2,3], then retrieves documents containing "database" by using full-text search, and finally reranks the candidate documents.
SELECT c1, content FROM hybrid_search(TABLE doc_table, '{
"knn": {
"field": "vector",
"k": 4,
"query_vector": [1, 2, 3]
},
"query": {
"match" : {"content" : "database"}
},
"rerank": {
"model": "aliyun-dashscope/gte-rerank-v2",
"field": "content",
"query": "high availability distributed relational database",
"rank_window_size": 6
}
}');
The expected return is as follows:
+------+--------------------------------------------------------------------------------------------------------------------+
| c1 | content |
+------+--------------------------------------------------------------------------------------------------------------------+
| 1 | OceanBase is a distributed relational database with strong consistency and high availability across multiple zones |
| 5 | TiDB is a distributed NewSQL database that supports horizontal scaling and online transaction processing |
| 6 | PostgreSQL is a single-node relational database known for extensibility and advanced SQL compliance |
| 2 | MySQL is an open-source database popular for web applications and content management systems |
| 4 | MongoDB is a document-oriented NoSQL database designed for flexible schema and unstructured data storage |
| 3 | Redis is an in-memory key-value store used as database cache and message broker for fast data access |
+------+--------------------------------------------------------------------------------------------------------------------+
6 rows in set
For details on model registration, see the AI Model Registration topic at the end of this topic.
Optimize performance through intra-partition parallelism
Hybrid search uses single-threaded serial execution by default, which may result in high response time (RT) for a single query statement in scenarios with large data volumes and sufficient CPU resources. To address this issue, the intra-partition parallel execution feature is introduced, which can reduce RT using a parallel execution strategy within a single partition. This feature is disabled by default and must be manually enabled.
Note
This feature is supported starting from V4.6.0 BP1.
The specific strategy for parallel execution is described as follows:
Parallel Strategy |
Description |
|---|---|
| Multi-path parallelism | Multiple recall paths in the same query (multiple knn paths plus one full-text query path) are executed in parallel by different worker threads. The overall response time approaches that of the longest-running path. |
Intra-query-path parallelism |
A single query recall path, including full-text, scalar, and JSON filtering, is split by data range and scanned in parallel by multiple worker threads, reducing the response time of that path. |
If the conditions for parallel execution are not met or the parallel switch is off, execution automatically falls back to serial execution.
Specific usage instructions and examples are as follows:
Enable parallelism
Hybrid search parallelism is disabled by default. Before use, you must enable the parameter at the tenant level:
ALTER SYSTEM SET _enable_hybrid_search_parallel_execution = true;
Effect after enabling:
- If a query contains ≥ 2 recall paths (for example, one
queryfull-text path + one or moreknnpaths), multi-path parallelism will automatically take effect without additional configuration in thedsl_stringstring. - In the
dsl_stringstring, under thequery.search_optionsobject, the degree of parallelism within a full-text/scalar recall path is controlled by thequery_dopsub-parameter.
Multi-path parallelism (automatically takes effect after enabling)
The following example combines full-text and vector search on doc_table. After enabling the parallel switch, the query path and knn path will be executed in parallel:
SET @q = '{
"query": {
"match": {"text": "machine learning"}
},
"knn": [
{"field": "content_vector", "k": 10, "query_vector": [0.1, 0.2, 0.3, 0.4]}
],
"rank": {"weighted_sum": {"normalizer": "minmax"}},
"size": 10
}';
SELECT tid, __score
FROM HYBRID_SEARCH(table passages_test, @q);
Parallelism within the query path
In the dsl_string string, under the query.search_options object, the degree of parallelism within a single full-text/scalar recall path is controlled by query_dop.
field |
Type |
Default Value |
Value range |
Description |
|---|---|---|---|---|
query_dop |
int | 1 |
[1, 128] |
1 disables intra-path parallelism. Values greater than 1 split the data range for parallel scanning. Values outside the specified range cause an error when dsl_string is parsed. |
Notice
query_dop takes effect only when the parallel switch is on or forced to be parallel via a hint. When the tenant-level switch is off or forced to be serial via a hint, the query_dop in the dsl_string string will be ignored without causing an error.
Setting query_dop = 4 on the query path and combining it with the knn path (where both multi-path parallelism and intra-path parallelism take effect simultaneously) is shown in the following example:
-- query_dop = 4: The full-text recall path is split into four segments for parallel scanning.
SET @q = '{
"query": {
"search_options": {"query_dop": 4},
"match": {"text": "distributed database"}
},
"knn": [
{"field": "content_vector", "k": 50, "query_vector": [0.1, 0.2, 0.3, 0.4]}
],
"rank": {"rrf": {"rank_constant": 60}},
"size": 10
}';
SELECT tid, __score
FROM HYBRID_SEARCH(table passages_test, @q);
For detailed information about the query_dop parameter, see the "Query options" section in the "HYBRID_SEARCH" syntax documentation at the end of this topic.
Query-level switch
You can temporarily override tenant-level configurations for a single query using a hint to facilitate canary testing:
Hint |
Value |
Description |
|---|---|---|
OPT_PARAM('enable_hybrid_search_parallel_execution', 'TRUE'/'FALSE') |
'TRUE' / 'FALSE' |
Overrides the tenant-level parameter _enable_hybrid_search_parallel_execution for a single query. |
Notice
The parameter in the hint does not require an underscore prefix; you can directly use enable_hybrid_search_parallel_execution.
Comparison example of forced serialization and forced parallelism (dsl_string strings are the same, only the hints differ):
-- Force parallelism (the hint overrides the system setting)
SELECT /*+ OPT_PARAM('enable_hybrid_search_parallel_execution', 'TRUE') */
tid, __score
FROM HYBRID_SEARCH(table passages_test, @q);
-- Force serial execution (the hint overrides the system configuration even when it is enabled).
SELECT /*+ OPT_PARAM('enable_hybrid_search_parallel_execution', 'FALSE') */
tid, __score
FROM HYBRID_SEARCH(table passages_test, @q);
For more details about the hint, see the related documentation at the end of this topic.
Parallelism switches and effective conditions
There are two types of switches for hybrid search parallelism, with the following priority (from high to low):
- Query-level hint: The
OPT_PARAMhint has the highest priority and will override system parameter settings. - Tenant parameter:
_enable_hybrid_search_parallel_execution(effective when no hint is specified).
When the final value of the parallelism switch is TRUE, whether parallelism is actually enabled is determined by the execution plan. Parallelism within multiple paths or within a query path is enabled if the following conditions are met:
- Automatic multi-path parallelism: Multi-path parallelism is automatically enabled when the number of recall paths is ≥ 2.
querypath-level parallelism: Path-level parallelism is enabled whenquery_dop > 1(independently controlled by DSL fields).
Notice
Hints can override system configurations. Therefore, setting the tenant parameter _enable_hybrid_search_parallel_execution to FALSE does not guarantee that all parallel queries are disabled. If SQL statements explicitly use hints to force parallelism, these hints must be removed to fully revert to serialization.
For answers to other parallelism-related questions, see FAQ on AI scenarios in the References section.
View execution plans
You can use EXPLAIN to view the subquery nodes, hybrid fusion methods, and whether indexes are used in a hybrid search plan:
EXPLAIN SELECT c1 FROM HYBRID_SEARCH(table doc_table, '{"query": { "bool": {
"must" : [{"match" : {"content": "oceanbase mysql"}}],
"filter": [
{"range": {"c1": {"gte" : 3}}},
{"range": {"c1": {"lte" : 10}}}
]
}},
"knn":
{
"field": "vector",
"k": 10,
"query_vector": "[1, 2, 3]"
}}');
The expected output is as follows, showing the subquery nodes in the hybrid search plan, the WRRF fusion method used for subquery combination, and the use of index idx_vector and full-text index idx_content:
+------------------------------------------------------------------------------+
| Query Plan |
+------------------------------------------------------------------------------+
| ================================================================== |
| |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| |
| ------------------------------------------------------------------ |
| |0 |INDEX MERGE SCAN|doc_table(idx_vector)|1 |3 | |
| ================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([doc_table.c1]), filter(nil), rowset=16 |
| access([doc_table.__pk_increment], [doc_table.c1]), partitions(p0) |
| is_index_back=true, is_global_index=false, use_index_merge=true, |
| fusion node: method=WEIGHT_SUM, limit(10), window_size(10) |
| vector node: index name=idx_vector |
| boolean node: |
| must: |
| match node: index name=idx_content |
| filter: |
| scalar node: filter([doc_table.c1 >= 3], [doc_table.c1 <= 10]) |
+------------------------------------------------------------------------------+
17 rows in set
References
- For syntax, parameters, and limitations, see HYBRID_SEARCH.
- Search Index
- Full-text index
- Vector index
- AI model registration
- For more information about the full-text/scalar query parallel execution hint, see the
_enable_hybrid_search_parallel_executionparameter description in OPT_PARAM Hint. - FAQs