Semantic index

This topic describes how to use semantic indexes in OceanBase Database.

Overview

Semantic indexes use the built-in embedding capabilities of OceanBase Database to simplify vector index operations. They abstract away vector concepts: you can write the original data to be stored, such as text or image URLs, and OceanBase Database automatically converts the data into vectors and builds indexes. During a search, you provide the original query content, and OceanBase Database automatically embeds the query and searches the vector index.

Building upon text semantic indexing, OceanBase 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 indexing provides both synchronous and asynchronous embedding methods for users to choose from:

• Synchronous mode: Embedding and indexing are performed immediately after data is written, ensuring data is visible in real time.
• Asynchronous mode: Background tasks perform data embedding and indexing in batches, which can significantly improve write performance, but there will be a delay before data becomes visible. You can flexibly set the trigger cycle of background tasks based on your requirements for data visibility timeliness.

Tables with semantic indexes also support brute-force search. A brute-force search performs a full-table scan to return the exact top n nearest rows.

Feature support

In the current version, updates, deletions, and searches on semantic indexes use the same syntax and memory management as HNSW series indexes. Semantic indexes support index monitoring and maintenance. In asynchronous mode, an incremental refresh also triggers data embedding.

Supported features include:

Some considerations are as follows:

• In synchronous mode, write performance may be affected by embedding performance; in asynchronous mode, data visibility will be delayed.
• For scenarios involving repeated searches, it is recommended to use the AI Function Service to pre-obtain query vectors, avoiding the need for embedding with each search.
• You cannot create multiple semantic indexes on the same column.

Prerequisites

Register a text embedding model

Before using semantic indexing, you must register an embedding model and endpoint. Here is an example of 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"
}');

Register a multimodal embedding model (image)

Image semantic indexes (content_type=image) must use multimodal (VL) embedding models. Register the model and endpoint by using the legacy APIs, and specify the registered model_key in the model parameter of the index 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"
}');

Considerations for image semantic indexes

• Model requirements:
  • Currently, only the multimodal embedding model provided by Alibaba Cloud DashScope is supported.
  • Using a non-multimodal (text-only) embedding model for content_type=image will 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 and image URLs; you must create separate indexes for content_type=text and content_type=image.
• Column type limitations:
  • Image semantic indexes can only be created on VARCHAR columns. The following types are not supported:
    • TEXT / LONGTEXT / TINYTEXT / MEDIUMTEXT
    • CHAR / BINARY / VARBINARY / BLOB
• URL constraints:
  • Only the http:// and https:// protocols are supported.
  • An empty string is not allowed for content_type=image.
  • Supported image formats depend on the AI model used (typically JPEG, PNG, WebP, GIF, etc.). Storing images as binary data (e.g., BLOB/VARBINARY) is currently not supported.
• Query limitations:
  • Cross-modal retrieval (searching for images by text) requires the multimodal embedding model to support both text and image inputs and produce outputs in the same vector space.
• Other considerations:
  • The content_type cannot 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_column configuration and use the VECTOR_INDEX hint to specify the index.
• Private OSS images:
  • Object storage buckets are private by default. Directly writing an object URL may cause a vectorization error due to authentication failure. For images on OSS, you must first generate a pre-signed URL and 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.
  • A pre-signed URL has an expiration time. When sync_mode=manual or async, vectorization is performed asynchronously in the background. It is recommended to set the expiration time to more than 1 hour.
  • After vectorization is complete, OceanBase 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 indexing

Enable semantic indexes by using the tenant-level configuration item _enable_semantic_index. It is disabled by default:

ALTER SYSTEM SET _enable_semantic_index = true;

Index syntax and description

Create

Semantic indexes can be created during table creation or after table creation. Consider the following points when creating an index:

• You must specify a VARCHAR column for creating an index.
• You can create multiple semantic indexes on the same column.
• The model, sync_mode, and ai_service_tier parameters are not supported for regular vector indexes.
• ai_service_tier=BATCH is supported only when you create an index after table creation. It is not supported during table creation.

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, ...)
);

CREATE VECTOR INDEX index_name
ON table_name(varchar_column_name)
WITH (param1=value1, param2=value2, ...);

The param parameters are described as follows:

Other vector index parameters, such as m, ef_construction, and ef_search, are used in the same way as those of regular HNSW/HNSW_BQ indexes. For more information, see HNSW series indexes in References.

Search

Semantic indexes support two search methods:

• Search by original content (text or image URL)
• Search by vector
  • You can specify the APPROXIMATE/APPROX clause to perform a nearest neighbor search using the vector index.
  • You can also omit the APPROXIMATE/APPROX clause and use a full-table scan for a brute-force search.

The usage instructions and considerations for the hints are as follows:

• When multiple semantic indexes exist on the same column, you must explicitly specify the desired vector index using the VECTOR_INDEX hint; otherwise, the system will report an error. With 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_INDEX hint can be used together with the INDEX hint.
• For usage instructions on the preceding two points, see VECTOR_INDEX Hint.

SELECT ... FROM table_name
ORDER BY semantic_distance(column_name, 'query_content' [, 'query_type'])
APPROXIMATE|APPROX
LIMIT n;

For detailed syntax descriptions of the APPROXIMATE/APPROX clause, see the HNSW series of index documents in the related documentation at the end of this topic.

Examples of create, update, search, and delete operations

The DML operations for semantic indexes (INSERT, UPDATE, and DELETE) are identical to those for regular vector indexes. When you insert or update VARCHAR data, the system embeds the data synchronously or asynchronously based on the sync_mode setting.

Create during table creation

1. Example of creating a text semantic index

   Create the vector_idx index when creating the test table items:

   -- Assume that 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 automatically performs embedding:

   INSERT INTO items(id, doc) VALUES(1, 'Rose');
   

2. 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 after table creation

1. Example of creating a text semantic index after table creation

   After creating the test table items, use the CREATE VECTOR INDEX statement to create the vector_idx index:

   CREATE TABLE items (
       id BIGINT PRIMARY KEY,
       doc VARCHAR(100)
   );
   
   -- Assume that 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 automatically performs embedding:

   INSERT INTO items(id, doc) VALUES(1, 'Rose');
   

2. 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 re-embeds the vectors:

• Synchronous mode: Re-embedding is performed immediately after the update.
• Asynchronous mode: The background task re-embeds the vectors during the next trigger cycle.

Example:

UPDATE items SET doc = 'Lily' WHERE id = 1;

Delete

The deletion operation is the same as that for a regular vector index; you can directly delete the data.

Example:

DELETE FROM items WHERE id = 1;

Search

-- Assume that 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 original text
SELECT id, doc FROM items
ORDER BY semantic_distance(doc, 'Sunflower')
APPROXIMATE LIMIT 3;

+----+-----------+
| id | doc       |
+----+-----------+
|  4 | Sunflower |
|  2 | Sunflower |
|  3 | Rose      |
+----+-----------+
3 rows in set

-- Assume that the ob_embed 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');

-- Search by index using vectors
SELECT id, doc FROM items
ORDER BY semantic_vector_distance(doc, @query_vector)
APPROXIMATE LIMIT 3;

+----+-----------+
| id | doc       |
+----+-----------+
|  3 | Sunflower |
|  1 | Rose      |
|  4 | Rose      |
+----+-----------+
3 rows in set

-- Perform an exact nearest-neighbor search by using vectors
SELECT id, doc FROM items
ORDER BY semantic_vector_distance(doc, @query_vector)
LIMIT 3;

+----+-----------+
| id | doc       |
+----+-----------+
|  3 | Sunflower |
|  4 | Rose      |
|  1 | Rose      |
+----+-----------+
3 rows in set

Example of semantic index search for images:

-- Image 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;

References

• FAQs about AI
• Syntax and examples of AI functions
• HNSW series indexes
• Index memory management
• Index monitoring and maintenance