This topic describes how to use semantic search in OceanBase AI Database.
Notice
This feature is experimental and is not recommended for production environments.
Overview
Semantic indexes greatly simplify the use of vector indexes by leveraging the built-in embedding capabilities of OceanBase AI Database. It makes vectors transparent to users: you can directly write the raw data you want to store (such as text or image URLs), and OceanBase AI Database will automatically convert it into vectors and build the index internally. During searches, you only need to provide the raw search content, and OceanBase AI Database will automatically perform the embedding and search the vector index, significantly enhancing usability.
Based on text semantic indexes, OceanBase AI Database also supports storing image URLs in VARCHAR columns and creating image semantic indexes. The system automatically calls multimodal embedding models to complete vectorization, supporting scenarios such as image search by image and image search by text (cross-modal retrieval).
Considering the performance overhead of embedding models, semantic indexes offer both synchronous and asynchronous embedding options:
- Synchronous mode: Data is embedded and indexed immediately after being written, ensuring real-time visibility.
- Asynchronous mode: Data is embedded and indexed in batches by background tasks, which can significantly improve write performance, while data becomes visible with some delay. You can flexibly set the trigger cycle for background tasks based on your requirements for data visibility.
Additionally, tables with semantic indexes can also perform brute-force searches. Brute-force search refers to searching by scanning the entire table to obtain the exact results of the top n closest rows.
Feature support
Note
In the current version, this feature is supported only for HNSW, HNSW_BQ, and HNSW_SQ indexes.
The syntax for updating, deleting, and searching using semantic indexes, as well as memory management, is identical to that of HNSW series indexes. Index monitoring and maintenance processes are supported, and in asynchronous mode, incremental refreshes will also trigger data embedding.
The supported features are as follows:
Module |
Feature |
Description |
|---|---|---|
| DDL | Create a semantic index during table creation | You can create a semantic index (text or image URL) on a VARCHAR column when creating a table. |
| DDL | Create a semantic index after table creation | You can create a semantic index on a VARCHAR column of an existing table. |
| DDL | Batch File embedding | When creating a semantic index after table creation, you can specify ai_service_tier=BATCH to submit requests in batches through a file. The system processes all requests asynchronously. For more information, see AI model interaction (Batch File). |
| DDL | Multiple indexes on a single column | You can create multiple semantic indexes on the same column, which can use different models, distance algorithms, or index parameter combinations.
NoticeThis feature is for testing only and cannot be used in production. |
| DDL | Image URL semantic index | Specify content_type=image to create a semantic index on a VARCHAR column, which automatically vectorizes image URLs.
NoticeThis feature is supported starting from V4.6.2.1. |
| Search | semantic_distance function |
You can pass in the original text or an image URL for vector search, supporting text-to-image and image-to-image search.
NoteThis feature is supported starting from V4.6.2.1. |
| Search | semantic_vector_distance function |
You can pass vectors to this function to perform a search in two ways:
|
| DBMS_VECTOR | REBUILD_INDEX |
Use this procedure in the same way as for regular vector indexes to perform a full index rebuild. |
Some considerations are as follows:
- In synchronous mode, write performance may be affected by embedding performance; in asynchronous mode, there will be a delay in data visibility.
- For scenarios with repeated searches, it is recommended to use the AI Function Service to pre-fetch the query vector to avoid embedding during each search.
- Multiple semantic indexes cannot be created on the same column.
Prerequisites
Note
Semantic indexes do not currently support registering models using the new interface (REGISTER_PROVIDER). You must use the old interfaces CREATE_AI_MODEL and CREATE_AI_MODEL_ENDPOINT to register embedding models.
Register a text embedding model
Before using semantic indexes, you must first register the embedding model and endpoint. Here is an example registration:
CALL DBMS_AI_SERVICE.DROP_AI_MODEL ('ob_embed');
CALL DBMS_AI_SERVICE.DROP_AI_MODEL_ENDPOINT ('ob_embed_endpoint');
CALL DBMS_AI_SERVICE.CREATE_AI_MODEL(
'ob_embed', '{
"type": "dense_embedding",
"model_name": "BAAI/bge-m3"
}');
CALL DBMS_AI_SERVICE.CREATE_AI_MODEL_ENDPOINT (
'ob_embed_endpoint', '{
"ai_model_name": "ob_embed",
"url": "https://api.siliconflow.cn/v1/embeddings",
"access_key": "sk-xxxxxxxxxxxxxxxxxxxxxxxxxxx",
"provider": "siliconflow"
}');
Note
Replace access_key with your actual API Key. The BAAI/bge-m3 model has a vector dimension of 1024, so dim=1024 must be used when creating a semantic index.
Register a multimodal embedding model (image)
Image semantic indexes (content_type=image) must use multimodal (VL) embedding models and register the model with an endpoint through the legacy interface. When creating an index, specify the registered model_key in the model parameter of the WITH clause.
CALL DBMS_AI_SERVICE.DROP_AI_MODEL ('ob_vl_embed');
CALL DBMS_AI_SERVICE.DROP_AI_MODEL_ENDPOINT ('ob_vl_embed_endpoint');
CALL DBMS_AI_SERVICE.CREATE_AI_MODEL(
'ob_vl_embed', '{
"type": "dense_embedding",
"model_name": "qwen2.5-vl-embedding"
}');
CALL DBMS_AI_SERVICE.CREATE_AI_MODEL_ENDPOINT (
'ob_vl_embed_endpoint', '{
"ai_model_name": "ob_vl_embed",
"url": "https://dashscope.aliyuncs.com/api/v1/services/embeddings/multimodal-embedding/multimodal-embedding",
"access_key": "sk-xxxxxxxxxxxxxxxxxxxxxxxxxxx",
"provider": "aliyun-dashscope"
}');
Note
Replace access_key with your actual API Key. When creating an index, dim must match the actual output dimension of the selected model. When registering an endpoint, provider must be aliyun-dashscope. For complete parameter descriptions, see DBMS_AI_SERVICE package.
Considerations (image semantic index)
- Model requirements:
- Currently, only the multimodal embedding models provided by Alibaba Cloud DashScope are supported.
- Using a non-multimodal (pure-text) embedding model for
content_type=imagewill result in an error.
- Unsupported features:
- Doc mode (slicing) is not supported.
- Other multimodal types such as video and audio are not supported.
- The same semantic index cannot handle both text URLs and image URLs; you must create separate indexes for
content_type=textandcontent_type=image.
- Column type restrictions:
- Image semantic indexes can only be created on
VARCHARcolumns. The following types are not supported:TEXT/LONGTEXT/TINYTEXT/MEDIUMTEXTCHAR/BINARY/VARBINARY/BLOB
- Image semantic indexes can only be created on
- URL constraints:
- Only the
http://orhttps://protocol is supported. - Empty strings are not allowed for
content_type=image. - The image formats supported depend on the AI model used (typically JPEG, PNG, WebP, GIF, etc.); storing images as binary (e.g.,
BLOB/VARBINARY) is currently not supported.
- Only the
- Query restrictions:
- Text-to-image search (cross-modal retrieval) requires the multimodal embedding model to support both text and image inputs and produce outputs in the same vector space.
- Other considerations:
- The
content_typecannot be changed after creation. To modify it, you must delete the index and recreate it. - If you need to create both text and image semantic indexes on the same column, enable the
_enable_multiple_semantic_indexes_on_columnconfiguration and use theVECTOR_INDEXhint to specify the index.
- The
- Private OSS images:
- Object storage Buckets have private permissions by default. Directly writing an object URL may cause a vectorization error due to authentication failure. For images stored in OSS, you must first generate a pre-signed URL, then write it to the database, or pass it as a query parameter.
- You can generate a pre-signed URL through the OSS console or the Alibaba Cloud SDK.
- Pre-signed URLs have an expiration time. When
sync_mode=manualorasync, vectorization occurs asynchronously in the background. It is recommended to set the expiration time to more than 1 hour. - After vectorization is completed, OceanBase AI Database stores only the vector values and does not persist the URL. An expired URL does not affect the existing vector index.
Manually enable semantic index
Enable the semantic index feature using the tenant parameter _enable_semantic_index, which is disabled by default:
ALTER SYSTEM SET _enable_semantic_index = true;
Syntax and description of indexes
Create
Semantic indexes can be created during table creation or after table creation. When creating an index, note the following:
- You must specify a
VARCHARcolumn to create an index. - You can create multiple semantic indexes on the same column.
- The
model,sync_mode, andai_service_tierparameters are not supported for regular vector indexes. - The
ai_service_tier=BATCHparameter is supported only for indexes created after table creation and is not supported during table creation.
You can use the CREATE TABLE statement to create a semantic index and initiate background tasks synchronously or asynchronously through index parameters. In synchronous mode, VARCHAR data is automatically converted into vector data during data insertion. In asynchronous mode, data conversion is completed periodically or manually.
The syntax is as follows:
CREATE TABLE table_name (
column_name1 data_type1,
column_name2 VARCHAR, -- Column for text or image URLs
...,
VECTOR INDEX index_name (column_name2) WITH (param1=value1, param2=value2, ...)
);
Note the following:
- We recommend that you use the VARCHAR(2048) or VARCHAR(4096) type to achieve optimal search performance.
You can create a semantic index on an existing VARCHAR column of a table. When creating an index after table creation, a background task is initiated synchronously or asynchronously based on the provided index parameters. In synchronous mode, all existing VARCHAR data is embedded into vectors. In asynchronous mode, data embedding is completed periodically or manually.
The syntax is as follows:
CREATE VECTOR INDEX index_name
ON table_name(varchar_column_name)
WITH (param1=value1, param2=value2, ...);
When creating an index after table creation, you can specify ai_service_tier=BATCH in the WITH clause to submit requests in batches via files. The system will process all requests asynchronously. For complete usage instructions, see AI model interaction (Batch File).
The param parameters are described as follows:
Parameters |
Default value |
Value range |
Required |
Note |
Remarks |
|---|---|---|---|---|---|
distance |
l2/inner_product/cosine |
Yes | Specifies the vector distance algorithm type. | l2 indicates the Euclidean distance, inner_product indicates the inner product distance, and cosine indicates the cosine distance. |
|
type |
hnsw / hnsw_bq / hnsw_sq |
Yes | Specifies the index algorithm type. | ||
lib |
vsag |
vsag |
No | Specifies the vector index library type. | Only the VSAG vector library is supported. |
model |
The name of a registered model | Yes | Specifies the name of the model used for embedding. | It must be registered by using the legacy CREATE_AI_MODEL interface, and the model_key must be specified in the WITH clause. The new provider/model format is not supported.
NoteThis parameter is not supported for regular vector indexes. |
|
dim |
A positive integer, up to 4096 | Yes | Specifies the dimension of the vector after embedding. | It must match the dimension provided by the model. If the model supports multiple dimensions, you can choose a higher or lower dimensionality within the model's allowed range based on your scenario: prioritize accuracy for 768/1024, prioritize performance for 256/512. | |
sync_mode |
async |
immediate/manual/async |
No | Specifies the synchronization mode of data and indexes. | immediate indicates the synchronous mode, manual indicates the manual mode, and async indicates the asynchronous mode. Image semantic indexes do not support immediate and support only async or manual.
NoteThis parameter is not supported for regular vector indexes. |
sync_interval |
10s |
Time interval, such as 10s, 1h, and 1d |
No | Sets the trigger period of background tasks in asynchronous mode. | The numeric part must be positive. Supported units include seconds (s), hours (h), and days (d). |
content_type |
text |
text/image |
No | Specifies the content type of the semantic index. | text indicates a text semantic index, and image indicates an image URL semantic index. This parameter cannot be changed after creation.
NoticeThis parameter is supported starting from V4.6.2.1. |
ai_service_tier |
STANDARD |
STANDARD/BATCH |
No | Specifies the AI service tier when creating an index. | STANDARD indicates row-by-row synchronous HTTP calls; BATCH indicates batch file asynchronous processing, where embedding is completed through the Batch API of the AI service provider. |
allow_null_on_failure |
FALSE |
TRUE/FALSE |
No | Specifies the fault-tolerant behavior when batch embedding fails. | Takes effect only when ai_service_tier=BATCH. When set to TRUE, the vectors of failed rows are set to NULL and index creation continues; when set to FALSE, the DDL operation is aborted if any row fails. |
Other vector index parameters (such as m, ef_construction, and ef_search) are used in the same way as for regular HNSW/HNSW_BQ indexes. For more information, see the HNSW series index documentation in the References section at the end of this topic.
Search
Semantic indexes support two search methods:
- Search by original content (text or image URL)
- Vector search
- You can specify the
APPROXIMATE/APPROXclause to perform a nearest neighbor search using the vector index. - You can also omit the
APPROXIMATE/APPROXclause and use a brute-force search by performing a full table scan.
- You can specify the
Usage notes and considerations are as follows:
- When multiple semantic indexes exist on the same column, you must explicitly specify the vector index to use with the
VECTOR_INDEXhint; otherwise, the system will report an error. Using this hint, you can not only select a specific vector index in the query statement but also flexibly configure pre- or post-filter conditions to improve search accuracy and flexibility. - The
VECTOR_INDEXhint can be used together with theINDEXhint. - For the usage instructions of the above two points, see Vector Index Hint under Optimized Hint Type.
Use the semantic_distance expression to perform a vector search by passing in the original text or image URL.
The syntax is as follows:
SELECT ... FROM table_name
ORDER BY semantic_distance(column_name, 'query_content' [, 'query_type'])
APPROXIMATE|APPROX
LIMIT n;
Where:
column_name: The column (text or image URL) specified when creating the semantic index.query_content: The original content to search for (text string or image URL).query_type: Optional. Valid values are'TEXT'or'IMAGE'. If not specified, it is treated as'TEXT'. When performing a similar-image search on an image semantic index, you must explicitly pass in'IMAGE'. This parameter is supported starting from V4.6.2.1.n: The number of result rows to return.
Notice
semantic_distance supports only approximate searches with APPROXIMATE/APPROX.
With the
APPROXIMATEclause: Use thesemantic_vector_distanceexpression to perform a vector search by passing in a vector. If the search statement contains theAPPROXIMATE/APPROXclause, the vector index is used for the search. The syntax is as follows:SELECT ... FROM table_name ORDER BY semantic_vector_distance(column_name, 'query_vector') [APPROXIMATE|APPROX] LIMIT n;Where:
column_name: The text column specified when creating the semantic index.query_vector: The query vector.n: The number of result rows to return.
Without the
APPROXIMATEclause: Use thesemantic_vector_distanceexpression to perform a vector search by passing in a vector. If theAPPROXIMATE/APPROXclause is omitted, a brute-force search is performed by scanning the entire table to obtain the exact results of the top n rows with the smallest distances. During the search, thedistancetype is obtained from the table schema, and a full table scan is performed. The vector distance is calculated for each row to ensure accurate results. The syntax is as follows:SELECT ... FROM table_name ORDER BY semantic_vector_distance(column_name, 'query_vector') LIMIT n;The parameter meanings are consistent with those when the
APPROXIMATEclause is used.
For more information about the APPROXIMATE/APPROX clause, see the HNSW series index documentation in the References section at the end of this topic.
Examples of create, update, search, and delete operations
The DML operations (INSERT, UPDATE, and DELETE) on semantic indexes are identical to those on regular vector indexes. When you insert or update VARCHAR data, the system synchronously or asynchronously embeds it into vectors based on the sync_mode parameter.
Create an index during table creation
Example of creating a text semantic index
Create the
vector_idxindex when creating the test tableitems:-- Assume the ob_embed model has been created previously (see the "Prerequisites" section to register the model). CREATE TABLE items ( id BIGINT PRIMARY KEY, doc VARCHAR(100), VECTOR INDEX vector_idx(doc) WITH (distance=l2, lib=vsag, type=hnsw_sq, model=ob_embed, dim=1024, sync_mode=async, sync_interval=10s) );Insert a row of data into the test table
items, and the system will automatically perform vector embedding:INSERT INTO items(id, doc) VALUES(1, 'Rose');Example of creating an image semantic index
-- Assume that the ob_vl_embed model has been created previously (see the "Prerequisites" section to register the model). CREATE TABLE product_images ( id INT PRIMARY KEY, image_url VARCHAR(4096), product_name VARCHAR(256), VECTOR INDEX img_idx(image_url) WITH (distance=l2, type=hnsw, lib=vsag, model=ob_vl_embed, dim=768, sync_mode=manual, content_type=image) ) ORGANIZATION HEAP;
Create an index after table creation
Example of creating a text semantic index after table creation
After creating the test table
items, use theCREATE VECTOR INDEXstatement to create thevector_idxindex:CREATE TABLE items ( id BIGINT PRIMARY KEY, doc VARCHAR(100) ); -- Assume the ob_embed model has been created previously (see the "Prerequisites" section to register the model). CREATE VECTOR INDEX vector_idx ON items (doc) WITH (distance=l2, lib=vsag, type=hnsw_sq, model=ob_embed, dim=1024, sync_mode=async, sync_interval=10s);Insert a row of data into the test table
items, and the system will automatically perform vector embedding:INSERT INTO items(id, doc) VALUES(1, 'Rose');Example of creating an image semantic index after table creation
CREATE TABLE product_images ( id INT PRIMARY KEY, image_url VARCHAR(4096), product_name VARCHAR(256) ) ORGANIZATION HEAP; INSERT INTO product_images VALUES (1, 'https://example.com/shoes.jpg', 'Red Shoes'), (2, 'https://example.com/bag.jpg', 'Leather Bag'); CREATE VECTOR INDEX img_idx ON product_images(image_url) WITH (distance=l2, type=hnsw, lib=vsag, model=ob_vl_embed, dim=768, sync_mode=manual, content_type=image);
Update
When updating data of the VARCHAR type, the system will re-embed the vectors:
- Synchronous mode: Re-embedding is performed immediately after the update.
- Asynchronous mode: The background task will re-embed the vectors during the next trigger cycle after the update.
Example:
UPDATE items SET doc = 'Lily' WHERE id = 1;
Delete
The deletion operation is the same as that for regular vector indexes; you can delete data directly.
Example:
DELETE FROM items WHERE id = 1;
Search
-- Assume the ob_embed model has been created previously.
CREATE TABLE items (
id INT PRIMARY KEY,
doc varchar(100),
VECTOR INDEX vector_idx(doc)
WITH (distance=l2, lib=vsag, type=hnsw_sq, model=ob_embed, dim=1024, sync_mode=immediate)
);
INSERT INTO items(id, doc) VALUES(1, 'Rose');
INSERT INTO items(id, doc) VALUES(2, 'Sunflower');
INSERT INTO items(id, doc) VALUES(3, 'Rose');
INSERT INTO items(id, doc) VALUES(4, 'Sunflower');
INSERT INTO items(id, doc) VALUES(5, 'Rose');
-- Search by raw text
SELECT id, doc FROM items
ORDER BY semantic_distance(doc, 'Sunflower')
APPROXIMATE LIMIT 3;
The returned result is as follows:
+----+-----------+
| id | doc |
+----+-----------+
| 4 | Sunflower |
| 2 | Sunflower |
| 3 | Rose |
+----+-----------+
3 rows in set
Use the semantic_vector_distance expression to pass vectors for search. When a search statement contains the APPROXIMATE/APPROX clause, the vector index is used for the search.
-- Assume the ob_emb model has been created previously (see the "Prerequisites" section to register the model).
CREATE TABLE items (
id INT PRIMARY KEY,
doc varchar(100),
VECTOR INDEX vector_idx(doc)
WITH (distance=l2, lib=vsag, type=hnsw_sq, model=ob_embed, dim=1024, sync_mode=immediate)
);
INSERT INTO items(id, doc) VALUES(1, 'Rose');
INSERT INTO items(id, doc) VALUES(2, 'Lily');
INSERT INTO items(id, doc) VALUES(3, 'Sunflower');
INSERT INTO items(id, doc) VALUES(4, 'Rose');
-- Obtain the query vector first.
SET @query_vector = AI_EMBED('ob_embed', 'Sunflower');
-- Perform index search using vectors
SELECT id, doc FROM items
ORDER BY semantic_vector_distance(doc, @query_vector)
APPROXIMATE LIMIT 3;
The returned result is as follows:
+----+-----------+
| id | doc |
+----+-----------+
| 3 | Sunflower |
| 1 | Rose |
| 4 | Rose |
+----+-----------+
3 rows in set
-- Perform brute-force search using vectors (exact match)
SELECT id, doc FROM items
ORDER BY semantic_vector_distance(doc, @query_vector)
LIMIT 3;
The returned result is as follows:
+----+-----------+
| id | doc |
+----+-----------+
| 3 | Sunflower |
| 4 | Rose |
| 1 | Rose |
+----+-----------+
3 rows in set
Search example for image semantic index:
-- Search by image (IMAGE → IMAGE)
SELECT id, product_name, image_url
FROM product_images
ORDER BY semantic_distance(image_url, 'https://example.com/query.jpg', 'IMAGE')
APPROXIMATE LIMIT 10;
-- Search for images by text (TEXT → IMAGE, cross-modal)
SELECT id, product_name, image_url
FROM product_images
ORDER BY semantic_distance(image_url, 'a photo of red shoes', 'TEXT')
APPROXIMATE LIMIT 10;
-- Filter by WHERE clause
SELECT id, product_name
FROM product_images
WHERE product_name LIKE 'Red%'
ORDER BY semantic_distance(image_url, 'https://example.com/query.jpg', 'IMAGE')
APPROXIMATE LIMIT 10;