This topic describes the hybrid search SQL interface of OceanBase AI Database. You can use this interface to combine full-text search, vector search, and filter conditions in a single SELECT statement by using the HYBRID_SEARCH keyword. OceanBase AI Database returns the search results sorted by relevance.
Hybrid search combines vector-based semantic search with keyword search based on full-text indexes, providing more accurate and comprehensive search results through comprehensive ranking. Vector search excels at semantic approximate matching but is weaker at matching exact keywords, numbers, and proper nouns, whereas full-text search can effectively compensate for this deficiency. Therefore, hybrid search has become one of the key features of OceanBase AI Database and is widely used in various products.
Features and limitations
The features are as follows:
- Full-text indexing, starting from V4.6.2.1, supports text analyzers (Analyzers) in addition to regular tokenizers.
- Scalar filtering, JSON/ARRAY filtering can be executed even without indexes, but it is recommended to create the corresponding indexes for better performance.
The limitations are as follows:
- The current version supports only HNSW series vector index types.
- Only row-based tables are supported.
- Generated columns are not supported.
- When using asynchronous full-text or asynchronous search indexes, the syntax for hybrid search queries remains unchanged. However, index data is refreshed incrementally in the background. Queries guarantee eventual consistency but do not guarantee strong consistency or real-time visibility.
HYBRID_SEARCHcan only be used on heap tables.- Vector search requires a vector index to have been created on the target vector column. Multi-way vector search currently only supports dense vector columns.
- Full-text search requires a full-text index to have been created on the target text column. If the full-text column has a composite index, hybrid search is not supported.
This section only lists the feature limitations. They should be understood in conjunction with the syntax limitations provided in the related document HYBRID_SEARCH at the end of this topic.
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 used to describe query semantics such as full-text, vector search, filtering, and sorting fusion.
For complete syntax, parameters, and limitations, see the related document HYBRID_SEARCH at the end of this topic.
Create sample tables and insert data
This topic uses the same sample tables as Overview of DBMS_HYBRID_SEARCH (PL interface).
doc_table: A sample table for hybrid search and refined ranking using full-text, vector, and scalar filters.
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 verify the hybrid search capability of full-text, vector, and scalar filters, insert only the following data. Do not insert the precise ranking 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 verify the precise ranking capability, 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 filters.
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);
doc_table_agg: A sample table for aggregation, sorting, and result folding. Based ondoc_table, it adds scalar columnscategoryandprice. The data corresponds to related documents indoc_tablewith theoceanbaseprefix, and additionalmiddlewareclassification rows are added to demonstrate folding.
CREATE TABLE doc_table_agg (
id INT,
category VARCHAR(64),
price INT,
vector VECTOR(3),
content VARCHAR(255),
VECTOR INDEX idx_agg_vec(vector) WITH (distance=l2, type=hnsw_sq, lib=vsag),
FULLTEXT INDEX idx_agg_content(content)
) ORGANIZATION HEAP;
INSERT INTO doc_table_agg VALUES
(1, 'database', 100, '[1,2,3]', 'oceanbase Elasticsearch database'),
(2, 'database', 80, '[1,2,1]', 'oceanbase mysql database'),
(3, 'database', 120, '[1,1,1]', 'oceanbase oracle database'),
(4, 'middleware', 60, '[1,3,1]', 'redis cache middleware'),
(5, 'middleware', 90, '[1,3,2]', 'kafka message middleware'),
(6, 'database', 100, '[2,1,1]', 'starrocks oceanbase database');
Here are some examples for different use cases, including a quick start and an extended example:
Quick start examples
This section provides six simple core examples covering common scenarios of hybrid search, including vector search, full-text search, vector search + full-text search (RRF fusion), multi-vector search, filter condition merging, and score threshold filtering.
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 result is as follows:
+------+
| c1 |
+------+
| 1 |
| 5 |
| 2 |
+------+
3 rows in set
Full-text search
This example searches for the 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 result 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 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 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 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
Multi-vector search
This example independently performs vector searches on three vector fields (vec1, vec2, vec3), each returning the five most similar results. 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 result is 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
Filter condition merging
This example searches for the six records in the content column of the doc_table table that contain oceanbase mysql. The filter condition is that c1 is greater than or equal to 3 and less than or equal to 5, and the c1 column is returned.
SELECT c1 FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"bool": {
"must": [{"match": {"content": "oceanbase mysql"}}],
"filter": [{"range": {"c1": {"gte": 3}}}]
}
},
"knn": {
"field": "vector",
"k": 10,
"query_vector": "[1,2,3]",
"filter": [
{"range": {"c1": {"lte": 5}}}
]
}
}'
);
The expected result is as follows:
+------+
| c1 |
+------+
| 1 |
| 3 |
| 6 |
| 5 |
| 2 |
| 4 |
+------+
6 rows in set
Hard filtering and scalar scoring
Scalar conditions can be used only for hard filtering or can participate in relevance scoring (contributing to __score). Scalar filter conditions (term, range, terms, wildcard, JSON/array expressions) support boost and can be placed anywhere in the query; whether they contribute to the score depends on their position. The position rules and syntax for each expression are described in the "Detailed description of boost parameters" section of the HYBRID_SEARCH documentation at the end of this topic.
The following example compares the behavior of the same range condition in must and filter:
-- must: Participate in scoring
SELECT c1, __score FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"bool": {
"must": [
{"range": {"c1": {"gt": 1, "boost": 2.1}}}
],
"boost": 2.0
}
}
}'
);
-- filter: not involved in scoring
SELECT c1, __score FROM HYBRID_SEARCH(
TABLE doc_table,
'{
"query": {
"bool": {
"filter": [
{"range": {"c1": {"gt": 1, "boost": 2.1}}}
]
}
}
}'
);
In the former, __score includes a scalar contribution; in the latter, it is used only for filtering, and __score is 0 or has no scoring contribution.
Score threshold filtering (min_score)
Notice
The min_score parameter is supported only by the SQL interface. The PL interface does not support it.
This example sets min_score to 0.5 for the weighted hybrid search to filter out low-score results.
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 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 |
+------+---------+--------------------------------+----------------------------------+--------------------+
2 rows in set
Extended examples
This section provides some more complex extended examples covering advanced scenarios of hybrid search, including filtering of complex data types (such as JSON and ARRAY), specifying the number of vector candidates with num_candidates, wildcard filtering, weighted blending of vector and full-text search, WRRF blending, normalization, hybrid search score queries, reranking, aggregation (aggs), counting (count(*)), sorting (sort), and result folding (collapse).
Scalar + JSON/array filtering
These examples require the use of search indexes. In hybrid search scenarios, full-text tokenization and keyword search are handled by full-text indexes, while search indexes are used for structured, semi-structured, or simple scalar conditions on internal paths within complex types. The two apply to different column types, can coexist in the same table, and work together with vector indexes to improve query performance.
Furthermore, in analytical wide-table scenarios, search indexes can also leverage the optimizer's multi-index join query (Index Merge) capability. They automatically identify and combine available full-text and scalar indexes to select the optimal scan path, further enhancing query performance. For more information about search indexes, including usage, syntax, and complete examples, 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 this 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 result 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 result 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 searched element is identical 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 is empty.
JSON_EXTRACT
The JSON_EXTRACT() function returns data from a specified path in a JSON document.
-- Extract the node with 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 result is 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 present in an array.
SELECT id, tags_array1 FROM HYBRID_SEARCH(
TABLE doc_json_array,
'{
"query": {
"bool" : {
"filter" : [
{ "array_contains": { "tags_array1" : "ios" }}
]
}
}
}');
The expected 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 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 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
term/terms query array columns
Currently, only single-level array columns are supported. For complete syntax support and limitations, see the "Scalar query structure" section in the HYBRID_SEARCH documentation at the end of this topic.
Query array columns with
termSELECT id, tags_array1 FROM HYBRID_SEARCH( TABLE doc_json_array, '{ "query": { "bool": { "filter": [{"term": {"tags_array1": "ios"}}] } } }' );The expected result is as follows:
+------+------------------+ | id | tags_array1 | +------+------------------+ | 10 | ["mobile","ios"] | +------+------------------+ 1 row in setQuery array columns with
termsSELECT id, tags_array1 FROM HYBRID_SEARCH( TABLE doc_json_array, '{ "query": { "bool": { "filter": [{"terms": {"tags_array1": ["database", "mysql"]}}] } } }' );The expected 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 setQuery JSON array paths with
termSELECT id, doc_json FROM HYBRID_SEARCH( TABLE doc_json_array, '{ "query": { "bool": { "filter": [{"term": {"doc_json.tags": "mysql"}}] } } }' );The expected result is as follows:
+------+--------------------------------------------------------------------------------------------------+ | id | doc_json | +------+--------------------------------------------------------------------------------------------------+ | 2 | {"name": "doc2", "tags": ["database", "mysql"], "metadata": {"type": "production", "score": 57}} | +------+--------------------------------------------------------------------------------------------------+ 1 row in setQuery JSON array paths with
termsSELECT id, doc_json FROM HYBRID_SEARCH( TABLE doc_json_array, '{ "query": { "bool": { "filter": [{"terms": {"doc_json.tags": ["database", "mysql", "postgres"]}}] } } }' );The expected result 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 set
Nested query example
Note
Nested queries are supported starting from V4.6.2.1.
When a JSON object array needs to be filtered based on associations with the same array element, first declare NESTED_PATHS in the search index, then use nested queries within dsl_string. For example, to find all comments where author=alice and rating>=5:
CREATE SEARCH INDEX idx_nested ON movie_rating (
j1 WITH (NESTED_PATHS = ('$.comments'))
);
SELECT k, j1 FROM HYBRID_SEARCH(
TABLE movie_rating,
'{
"query": {
"nested": {
"path": "j1.comments",
"query": {
"bool": {
"must": [
{"term": {"j1.comments.author": "alice"}},
{"range": {"j1.comments.rating": {"gte": 5}}}
]
}
}
}
}
}'
);
For complete descriptions and examples of multi-level nesting, must_not parent/subdomain negation, table creation, and test data, see the nested query section in the HYBRID_SEARCH documentation at the end of this topic. For how to declare NESTED_PATHS, see the related documentation on search indexes at the end of this topic.
Vector candidate set size (num_candidates)
num_candidates specifies the scale 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 from [k, 10000]. When set together with search_options.ef_search, num_candidates takes precedence; sub-items such as refine_k and filter_mode in search_options can still take effect. For priority and complete descriptions and examples, including values, see the related sections in the HYBRID_SEARCH syntax documentation at the end of this topic.
Wildcard filtering
wildcard is used for fuzzy matching on scalar columns, with semantics equivalent to SQL LIKE ... ESCAPE '\\': * matches any multiple characters, and ? matches any single character. wildcard can participate in scoring in bool.must / bool.should, or be used for hard filtering in bool.filter or knn.filter. Regular indexes on VARCHAR columns can speed up the process via index scan paths; prefix indexes, full-text indexes, and JSON-related indexes do not support this.
For more descriptions and examples, see the fuzzy match example section in the HYBRID_SEARCH syntax documentation at the end of this topic.
Aggregation, sorting, and folding
For the complete syntax and parameter descriptions, see the HYBRID_SEARCH documentation at the end of this topic.
terms aggregation
In this example, the recall results for the full-text search oceanbase are grouped by category to count the number of documents. by_cat is a pseudo-aggregation bucket column that can be referenced directly in the SELECT list.
SELECT category, by_cat
FROM HYBRID_SEARCH(TABLE doc_table_agg, '{
"query": {
"match": {"content": "oceanbase"}
},
"aggs": {
"by_cat": {
"terms": {"field": "category"}
}
}
}');
The expected result is as follows:
+----------+--------+
| category | by_cat |
+----------+--------+
| database | 4 |
+----------+--------+
1 row in set
Cardinality aggregation
This example counts the number of distinct values in the category field among the recall results for the full-text search oceanbase.
SELECT uniq_cat
FROM HYBRID_SEARCH(TABLE doc_table_agg, '{
"query": {
"match": {"content": "oceanbase"}
},
"aggs": {
"uniq_cat": {
"cardinality": {"field": "category"}
}
}
}');
The expected result is as follows:
+----------+
| uniq_cat |
+----------+
| 1 |
+----------+
1 row in set
Count query (count(*))
This example counts the total number of hits for the full-text search oceanbase. Although "size": 1 is set in the DSL, count(*) returns the actual total number of elements in the union, unaffected by the size limit.
SELECT count(*)
FROM HYBRID_SEARCH(TABLE doc_table, '{
"query": {
"match": {"content": "oceanbase"}
},
"size": 1
}');
The expected result is as follows:
+----------+
| count(*) |
+----------+
| 5 |
+----------+
1 row in set
Sorting (sort)
In this example, the recall results for the full-text search oceanbase are returned in descending order by price. If sort is not specified, the results are sorted in descending order by __score by default.
SELECT id, price, __score
FROM HYBRID_SEARCH(TABLE doc_table_agg, '{
"query": {
"match": {"content": "oceanbase"}
},
"sort": [{"price": {"order": "desc"}}],
"size": 4
}');
The expected result is as follows:
+------+-------+--------------------+
| id | price | __score |
+------+-------+--------------------+
| 3 | 120 | 0.3503184713375797 |
| 1 | 100 | 0.3503184713375797 |
| 6 | 100 | 0.3503184713375797 |
| 2 | 80 | 0.3503184713375797 |
+------+-------+--------------------+
4 rows in set
Result folding (collapse)
This example performs both full-text search and vector search, then folds the results by category: each category retains only the record with the highest price (the folding basis is specified by sort). The size is trimmed after folding.
SELECT id, category, price, __score
FROM HYBRID_SEARCH(TABLE doc_table_agg, '{
"query": {
"match": {"content": "middleware"}
},
"knn": {
"field": "vector",
"k": 3,
"query_vector": "[1,2,3]"
},
"collapse": {"field": "category"},
"sort": [{"price": {"order": "desc"}}],
"size": 5
}');
The expected result is as follows (one record is retained for each of database and middleware, namely the one with the highest price):
+------+------------+-------+--------------------+
| id | category | price | __score |
+------+------------+-------+--------------------+
| 1 | database | 100 | 1 |
| 5 | middleware | 90 | 1.1569834370178316 |
+------+------------+-------+--------------------+
2 rows in set
Rerank fine ranking
Hybrid search first retrieves results using query or knn, then performs coarse ranking via rank (or the default fusion method if not specified). rerank then calls the AI Rerank model on the coarse-ranked candidate set to perform fine ranking, rearranging the results based on semantic relevance and returning size results.
Before use, you must register a vendor configuration and specify the Rerank model in the provider/model format. The following example uses the built-in vendor aliyun-dashscope (replace access_key with your actual API key):
CALL DBMS_AI_SERVICE.REGISTER_PROVIDER('aliyun-dashscope', '{
"access_key": "sk-xxxx"
}');
Note
rerank cannot be used alone; it must appear together with at least one of query or knn. Fine ranking is performed after coarse ranking.
This example first retrieves the six records most similar to the vector [1,2,3] using vector search, then performs fine ranking.
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 result 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 + Rerank fine ranking
This example first retrieves documents containing "database" using text search, then performs rerank fine ranking.
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 result 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 + Rerank fine ranking
This example first retrieves the four records most similar to the vector [1,2,3] using vector retrieval, then retrieves documents containing "database" using text search, and finally performs fine ranking.
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 result 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 more information about model registration, see AI model registration at the end of this topic.
Other hybrid search scenarios
Weighted hybrid of vector and full-text search
Weighted hybrid is the default hybrid method in OceanBase AI Database. The weight for full-text search is 0.3, and the weight for vector search is 0.7.
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 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
Based on RRF, it supports setting a boost weight. In this example, the boost parameter in query and knn represents the weight 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 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
Normalization for vector and full-text search
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 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
Optimize performance through intra-partition parallelism
Hybrid search uses single-threaded serial execution by default, which may lead to high response time (RT) for individual queries 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.
The specific strategies for parallel execution are described as follows:
Parallel strategy |
Note |
|---|---|
| Multiplexing | Multiple recall paths (multiple knn paths + full-text query paths) are executed in parallel by different worker threads, and the overall response time approaches that of the longest path. Both vector paths and full-text paths are supported. |
Parallelism within the query path |
Parallelism within a single query path, including recall paths for full-text, scalar, and JSON type filtering. After splitting by data range, it is processed by multiple workers. |
| Threads perform parallel scanning of different intervals, further reducing single-path RT. Vector paths are not supported. |
The usage instructions and examples are as follows:
Enable parallelism
Hybrid search parallelism is disabled by default. Before using it, you must enable the parameter at the tenant level:
ALTER SYSTEM SET _enable_hybrid_search_parallel_execution = true;
Effect after enabling:
- Automatic multi-way parallelism: If a query contains ≥ 2 recall paths (for example, one
queryfull-text path + one or moreknnpaths), multi-way parallelism is automatically enabled without requiring additional configuration in thedsl_string. - Parallelism within the
querypath:- If
query_dop(the parameter under thequery.search_optionsobject in thedsl_stringstring) is not specified, the system automatically selects the query parallelism based on the CPU load. - If the
query_dopparameter is specified andquery_dop > 1, the data is split according to the specified value, overriding the adaptive parallelism. - If
query_dop = 1is explicitly specified, intra-path parallelism is disabled, but multi-way parallelism remains unaffected.
- If
- If the parallel execution conditions are not met, or if the parallel execution switch is disabled, the execution path automatically degenerates into serial execution.
Multiplexing (automatically enabled)
The following example combines full-text and vector search on doc_table. After enabling the parallelism switch, the query path and the knn path are executed in parallel:
SET @q = '{
"query": {
"match": {"content": "oceanbase mysql"}
},
"knn": [
{"field": "vector", "k": 10, "query_vector": "[1,2,3]"}
],
"rank": {"weighted_sum": {"normalizer": "minmax"}},
"size": 10
}';
SELECT c1, __score
FROM HYBRID_SEARCH(TABLE doc_table, @q);
Parallelism within the query path (automatic/manual)
After parallel execution is enabled, the degree of parallelism within the query path can be controlled in two ways: automatic and manual:
Note
Adaptive parallelism is supported starting from V4.6.2.1.
Adaptive DOP (Auto DOP)
When query_dop is not specified, the system automatically selects the query parallelism based on the CPU load. You can use the same query as in the multiplexing example from the previous section. You can observe the parallelism decision for this query using DBMS_XPROFILE.DISPLAY_PROFILE.
Explicitly specify query_dop
In the dsl_string string, under the query.search_options object, you can specify the intra-path parallelism using query_dop.
Field |
Type |
Default value |
Value range |
Note |
|---|---|---|---|---|
query_dop |
int | Automatically selected if not specified | [1, 128] |
If not specified, the adaptive degree of parallelism (Auto DOP) is used. If explicitly specified, 1 indicates that no intra-path parallelism is performed, and 2 to 128 requests the corresponding degree of parallelism. If the value is out of range, an error is reported during the dsl_string parsing phase. |
Notice
query_dop takes effect only when the parallelism switch is enabled or when forced parallelism is specified using a hint. If the tenant-level switch is disabled, or if forced serialization is specified using a hint, the query_dop in the dsl_string string is ignored and no error is reported.
Set query_dop = 4 on the query path and combine it with the knn path (where both multi-path parallelism and intra-path parallelism take effect simultaneously). Example:
-- query_dop = 4: The full-text recall path is split into four segments for parallel scanning based on the data range.
SET @q = '{
"query": {
"search_options": {"query_dop": 4},
"match": {"content": "oceanbase mysql"}
},
"knn": [
{"field": "vector", "k": 5, "query_vector": "[1,2,3]"}
],
"rank": {"rrf": {"rank_constant": 60}},
"size": 10
}';
SELECT c1, __score
FROM HYBRID_SEARCH(TABLE doc_table, @q);
For more information about the query_dop parameter, see the Query options section in the HYBRID_SEARCH.
Query-level switch
You can temporarily override tenant-level configurations for a single query using hints to facilitate canary testing:
Hint |
Values |
Note |
|---|---|---|
OPT_PARAM('enable_hybrid_search_parallel_execution', 'TRUE'/'FALSE') |
'TRUE' / 'FALSE' |
Single query-level switch with a higher priority than the tenant-level configuration item enable_hybrid_search_parallel_execution. |
Notice
The parameters in the hint do not require an underscore prefix; you can use enable_hybrid_search_parallel_execution directly.
Comparison example of forced serialization and forced parallelism (the dsl_string string is the same, only the hint differs):
-- Force parallelism (the hint overrides the system setting)
SELECT /*+ OPT_PARAM('enable_hybrid_search_parallel_execution', 'TRUE') */
c1, __score
FROM HYBRID_SEARCH(TABLE doc_table, @q);
-- Force serialization (the hint overrides the system setting even when it is enabled)
SELECT /*+ OPT_PARAM('enable_hybrid_search_parallel_execution', 'FALSE') */
c1, __score
FROM HYBRID_SEARCH(TABLE doc_table, @q);
For more information about hints, see Other hints under global hints at the end of this topic.
Parallelism switches and conditions
There are two 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).
A final value of TRUE for the switch only indicates that parallelism is allowed. Whether multi-path parallelism or intra-path parallelism on the query path is actually used is still determined by the execution plan according to the strategy described above.
Notice
Hints can override system configurations. Therefore, simply setting the tenant parameter _enable_hybrid_search_parallel_execution to FALSE does not guarantee that all parallel queries are disabled. If any SQL explicitly uses 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 at the end of this topic.
View execution plans
You can use EXPLAIN to view the subquery nodes, hybrid fusion methods, and index usage 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 result is as follows. It shows the subquery nodes in the hybrid search plan, the hybrid fusion method as WEIGHT_SUM weighted sum and fusion, 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_vec,idx_content)|640 |153 | |
| ========================================================================== |
| Outputs & filters: |
| ------------------------------------- |
| 0 - output([doc_table.c1]), filter(nil), rowset=256 |
| 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), min_score(0) |
| vector node: index name=idx_vec |
| 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 search
- Overview of vector indexes
- Register an AI model
- For more information about the
enable_hybrid_search_parallel_executionparameter, see Other hints under global hints in the description of the full-text/scalar query parallel execution hint. - For information about the profile output related to hybrid search parallelism, see DISPLAY_PROFILE.
- FAQ on AI scenarios