Experience AI scenarios

OceanBase Database supports the vector data type starting from V4.3.3 and continues to enhance its AI capabilities in subsequent versions, gradually building an AI database capability that integrates vector storage, vector indexes, full-text indexes, AI function services, and hybrid search. By directly invoking Embedding Models and Large Language Models (LLMs) within the database, developers no longer need to transfer data back and forth between the database and external AI services. They can complete the entire process—from data vectorization, semantic search, to answer generation—using only SQL.

This topic covers the following experiences:

• Create a vector table and a full-text index: Create a single table that contains both VECTOR type fields and a FULLTEXT index, laying the foundation for hybrid search.
• Insert document data and generate vectors: Use the AI_EMBED() function to directly convert text into vectors and insert them into the database, experiencing the level of integration of OceanBase as an "AI database".
• Execute a hybrid search query: Use hybrid retrieval that combines vector search and full-text search to experience OceanBase's integrated search capability.
• Experience RAG search: Complete the entire RAG process within the database: "Retrieve Top-K segments → Reorder → Call LLM to generate answer".

Prerequisites

Before you perform the operations described in this topic, ensure that the following conditions are met:

Configure vector memory

Starting from V4.4.1, the vector search feature is enabled by default. Setting ob_vector_memory_limit_percentage to its default value 0 enables adaptive mode, where the system automatically adjusts the memory usage percentage for vector indexes (adapting to 40 for tenants with 8 GB or less of actual memory, and 50 for those above 8 GB). No manual configuration is required. For versions prior to V4.4.1, you must manually set this parameter before using HNSW series indexes; otherwise, creating an HNSW index will result in an error. If you need to explicitly specify the memory percentage, execute the following command in the sys tenant:

-- Log in to the sys tenant as the root user.
obclient -h127.0.0.1 -uroot@sys -P2881 -p -A oceanbase

-- Manually set the vector memory percentage to 30.
obclient [oceanbase]> ALTER SYSTEM SET ob_vector_memory_limit_percentage = 30;

Register AI models

OceanBase Database manages AI models and access endpoints through the DBMS_AI_SERVICE system package. Before using AI functions such as AI_EMBED, AI_COMPLETE, and AI_RERANK, you must register the corresponding models and access endpoints.

The following example registers three sets of models and endpoints on Alibaba Cloud Bailian: an embedding model, a text generation model, and a reranking model. Replace access_key with your actual API key:

-- Log in to the business tenant
obclient -h127.0.0.1 -uroot@oltp_tenant -P2881 -p -A test

-- Register the embedding model (for the AI_EMBED function)
obclient [test]> CALL DBMS_AI_SERVICE.CREATE_AI_MODEL(
'ob_embed', '{
    "type": "dense_embedding",
    "model_name": "BAAI/bge-m3"
}');

obclient [test]> CALL DBMS_AI_SERVICE.CREATE_AI_MODEL_ENDPOINT(
'ob_embed_endpoint', '{
    "ai_model_name": "ob_embed",
    "url": "https://dashscope.aliyuncs.com/compatible-mode/v1/embeddings",
    "access_key": "sk-xxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "provider": "aliyun-openAI"
}');

-- Register the text generation model (for the AI_COMPLETE function)
obclient [test]> CALL DBMS_AI_SERVICE.CREATE_AI_MODEL(
'ob_complete', '{
    "type": "completion",
    "model_name": "THUDM/GLM-4-9B-0414"
}');

obclient [test]> CALL DBMS_AI_SERVICE.CREATE_AI_MODEL_ENDPOINT(
'ob_complete_endpoint', '{
    "ai_model_name": "ob_complete",
    "url": "https://dashscope.aliyuncs.com/compatible-mode/v1/chat/completions",
    "access_key": "sk-xxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "provider": "aliyun-openAI"
}');

-- Register the reranking model (for the AI_RERANK function)
obclient [test]> CALL DBMS_AI_SERVICE.CREATE_AI_MODEL(
'ob_rerank', '{
    "type": "rerank",
    "model_name": "BAAI/bge-reranker-v2-m3"
}');

obclient [test]> CALL DBMS_AI_SERVICE.CREATE_AI_MODEL_ENDPOINT(
'ob_rerank_endpoint', '{
    "ai_model_name": "ob_rerank",
    "url": "https://dashscope.aliyuncs.com/api/v1/services/rerank/text-rerank/text-rerank",
    "access_key": "sk-xxxxxxxxxxxxxxxxxxxxxxxxxxx",
    "provider": "aliyun-dashscope"
}');

Execute the following query to verify that the model and endpoint have been successfully registered:

obclient [test]> SELECT NAME, TYPE FROM oceanbase.DBA_OB_AI_MODELS;
obclient [test]> SELECT ENDPOINT_NAME, AI_MODEL_NAME, PROVIDER FROM oceanbase.DBA_OB_AI_MODEL_ENDPOINTS;

The returned result should show the three registered models and three endpoints, indicating successful registration:

+-------------+-----------------+
| NAME        | TYPE            |
+-------------+-----------------+
| ob_embed    | DENSE_EMBEDDING |
| ob_complete | COMPLETION      |
| ob_rerank   | RERANK          |
+-------------+-----------------+
3 rows in set

+----------------------+---------------+------------------+
| ENDPOINT_NAME        | AI_MODEL_NAME | PROVIDER         |
+----------------------+---------------+------------------+
| ob_embed_endpoint    | ob_embed      | aliyun-openAI    |
| ob_complete_endpoint | ob_complete   | aliyun-openAI    |
| ob_rerank_endpoint   | ob_rerank     | aliyun-dashscope |
+----------------------+---------------+------------------+
3 rows in set

Create a vector table and a full-text index

OceanBase Database can create both a vector index and a full-text index on the same table at the same time, which is the basis for implementing hybrid search. Vector indexes support semantic similarity search, while full-text indexes support exact keyword matching. Combining the two provides more accurate and comprehensive search results.

Storage format description

Procedure

1. Log in to the business tenant:

   obclient -h127.0.0.1 -uroot@oltp_tenant -P2881 -p -A test
   

2. Create a knowledge base table that includes both vector columns and full-text index columns. Taking a document knowledge base as an example:

   obclient [test]> CREATE TABLE knowledge_base (
       id          INT AUTO_INCREMENT PRIMARY KEY,
       title       VARCHAR(255) DEFAULT NULL COMMENT 'Document title',
       content     TEXT         DEFAULT NULL COMMENT 'Document content',
       embedding   VECTOR(1024) DEFAULT NULL COMMENT 'Content vector',
       FULLTEXT INDEX ft_title (title) WITH PARSER ngram,
       FULLTEXT INDEX ft_content (content) WITH PARSER ngram,
       VECTOR INDEX vec_idx (embedding) WITH (distance=cosine, type=hnsw, lib=vsag)
   ) ORGANIZATION HEAP;
   

   Note

   • VECTOR(1024) declares a 1024-dimensional vector column. The dimensionality must match the output dimensionality of the embedding model. OceanBase supports vectors with up to 16,000 dimensions.
   • FULLTEXT INDEX creates single-column full-text indexes on the title and content columns, respectively, and uses WITH PARSER ngram to specify the ngram tokenizer. The ngram tokenizer splits text into fixed-length segments (2 characters by default), which suits Chinese content. The default space tokenizer splits words by spaces and cannot handle continuous Chinese sentences (it treats an entire sentence as a single word), which can result in no results for Chinese full-text searches.
   • Hybrid search requires single-column full-text indexes on the full-text columns. Combined full-text indexes (such as (title, content)) are invalid for hybrid search. Therefore, single-column full-text indexes are created separately for each column here.
   • VECTOR INDEX ... WITH (distance=cosine, type=hnsw, lib=vsag) creates an HNSW vector index using the cosine distance algorithm.
   • ORGANIZATION HEAP specifies the heap table organization. We recommend using heap tables for hybrid search.
   • The length of an HNSW index name currently does not support exceeding 25 characters.

3. Verify that the table and indexes were created successfully:

   obclient [test]> SHOW CREATE TABLE knowledge_base\G
   

   The return result should show both VECTOR KEY and FULLTEXT KEY index types, indicating that the vector index and full-text index have been successfully created on the same table.

Insert document data and generate vectors

OceanBase Database provides the AI_EMBED() function, which allows you to directly call registered embedding models in SQL statements to convert text data into vector data. This eliminates the cumbersome process of generating vectors using languages like Python externally and then importing them into the database, demonstrating OceanBase's integration as an "AI database".

About the AI_EMBED function

The AI_EMBED(model_key, input[, dim_or_parameters]) function specifies a registered embedding model via model_key and converts the user-provided text data into vector data.

Procedure

1. Insert document data and use the AI_EMBED() function to generate vectors simultaneously:

   obclient [test]> INSERT INTO knowledge_base (title, content, embedding)
   VALUES
       ('Introduction to OceanBase', 'OceanBase is a powerful native distributed database system that supports vector search and AI function services.', AI_EMBED('ob_embed', 'OceanBase is a powerful native distributed database system that supports vector search and AI function services.')),
       ('Principle of Vector Search', 'Vector search finds semantically similar content by calculating the distance between vectors and is commonly used in semantic search and recommendation systems.', AI_EMBED('ob_embed', 'Vector search finds semantically similar content by calculating the distance between vectors and is commonly used in semantic search and recommendation systems.')),
       ('Introduction to Full-text Indexing', 'OceanBase full-text indexing supports keyword search for text data and achieves efficient retrieval through word segmentation and inverted indexes.', AI_EMBED('ob_embed', 'OceanBase full-text indexing supports keyword search for text data and achieves efficient retrieval through word segmentation and inverted indexes.')),
       ('AI Function Services', 'The AI_EMBED function can directly call embedding models to generate vectors in SQL, while the AI.Complete function can call large language models to generate text.', AI_EMBED('ob_embed', 'The AI_EMBED function can directly call embedding models to generate vectors in SQL, while the AI.Complete function can call large language models to generate text.')),
       ('RAG Applications', 'RAG (Retrieval-Augmented Generation) improves the accuracy and reliability of large model responses by first retrieving relevant documents and then generating answers.', AI_EMBED('ob_embed', 'RAG (Retrieval-Augmented Generation) improves the accuracy and reliability of large model responses by first retrieving relevant documents and then generating answers.'));
   

   After the command is successfully executed, obclient returns the insertion result:

   Query OK, 5 rows affected
   Records: 5  Duplicates: 0  Warnings: 0
   

   Note

   By directly calling the AI_EMBED() function in an INSERT statement, the database automatically sends the text to the registered embedding model. After obtaining the vector, it writes it into the embedding column. The entire process is completed at the SQL layer without requiring external code.

   If an error ERROR 4216 (HY000): http request to AI service failed occurs during execution, the database failed to call the AI model service. Check whether the API key is valid, whether the database node can access the model service endpoint (see the network requirement in Prerequisites), and whether the model and endpoint registration parameters are correct.

2. You can also use the UPDATE statement to batch generate vectors for existing data:

   obclient [test]> UPDATE knowledge_base SET embedding = AI_EMBED('ob_embed', content) WHERE embedding IS NULL;
   

3. Verify that vectors were generated successfully:

   obclient [test]> SELECT id, title, vector_dims(embedding) AS dim_count FROM knowledge_base;
   

   The return result is as follows; dim_count should display a value consistent with the model output dimension (for example, 1024):

   +----+------------------+-----------+
   | id | title            | dim_count |
   +----+------------------+-----------+
   |  1 | Introduction to OceanBase Database   |      1024 |
   |  2 | Principle of vector search     |      1024 |
   |  3 | Introduction to full-text index     |      1024 |
   |  4 | AI Function Service      |      1024 |
   |  5 | RAG Application         |      1024 |
   +----+------------------+-----------+
   5 rows in set
   

   Note

   The vector_dims() function returns the dimensionality of a vector. If the return value is NULL, it indicates that the vector for that row was not generated. Check whether the AI model endpoint is registered successfully and whether the network is connected.

Execute hybrid search queries

Hybrid search combines semantic search based on vectors with keyword search based on full-text indexes. Vector search excels at approximate semantic matching but is weak at matching exact keywords, numbers, and proper nouns, while full-text search effectively compensates for this weakness. Starting from V4.5.0, OceanBase provides the DBMS_HYBRID_SEARCH system package (PL interface). The current version also provides the HYBRID_SEARCH table function (SQL interface). Both can combine vector search and full-text search in a single SQL statement, and both automatically merge and rank the results with fusion algorithms such as RRF (Reciprocal Rank Fusion). This topic uses the PL interface for demonstration.

Hybrid search principle

Procedure

1. Pure vector search

Use ORDER BY with a distance function and the APPROXIMATE keyword to perform an approximate nearest neighbor search with vectors:

obclient [test]> SET @query_vector = AI_EMBED('ob_embed', 'Database search functionality');

obclient [test]> SELECT id, title, cosine_distance(embedding, @query_vector) AS distance
                FROM knowledge_base
                WHERE embedding IS NOT NULL
                ORDER BY cosine_distance(embedding, @query_vector) APPROXIMATE LIMIT 3;

The returned results are sorted by distance, from closest to farthest. A smaller distance means greater semantic similarity (the following is sample output; actual values depend on the model and data used):

+----+------------------+----------------------+
| id | title            | distance            |
+----+------------------+----------------------+
| 2 | Principle of vector search | 0.012345678901234567 |
| 3 | Introduction to Full-text Indexes | 0.056789012345678901 |
| 1 | Introduction to OceanBase | 0.089012345678901234 |
+----+------------------+----------------------+
3 rows in set

2. Pure full-text search

Use the MATCH() AGAINST() syntax for full-text search:

obclient [test]> SELECT id, title, MATCH(content) AGAINST('Vector Search' IN NATURAL LANGUAGE MODE) AS score
                FROM knowledge_base
                WHERE MATCH(content) AGAINST('Vector Search' IN NATURAL LANGUAGE MODE);

The returned results are sorted by relevance score (the following output was produced in our test environment; the exact values depend on the data distribution):

+----+------------------+---------------------+
| id | title            | score               |
+----+------------------+---------------------+
| 2 | Principle of vector search | 0.7060192883272365 |
| 1 | Introduction to OceanBase | 0.5315473209814723 |
| 3 | Introduction to Full-text Indexes | 0.24894465290806755 |
| 4 | AI Function Service | 0.22518031395842178 |
+----+------------------+---------------------+
4 rows in set

3. Hybrid vector and full-text search

Use the DBMS_HYBRID_SEARCH.SEARCH function to initiate both vector search and full-text search in a single SQL statement, and fuse and sort the results using the RRF algorithm.

First, generate the query vector:

obclient [test]> SET @query_vector = AI_EMBED('ob_embed', 'Search functionality of a database');

Since the AI_EMBED() function cannot be called directly within JSON parameters, you need to concatenate the query vector to the JSON query parameter using CONCAT():

obclient [test]> SET @hybrid_query = CONCAT('{
  "query": {
    "bool": {
      "should": [
        {"match": {"title": "Database Search"}},
        {"match": {"content": "Vector Search"}}
      ]
    }
  },
  "knn": {
    "field": "embedding",
    "k": 5,
    "query_vector": ', @query_vector, '
  },
  "rank": {
    "rrf": {
      "rank_window_size": 10,
      "rank_constant": 60
    }
  },
  "_source": ["id", "title", "content"]
}');

obclient [test]> SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('knowledge_base', @hybrid_query));

The returned results are sorted by the RRF-fusion relevance (the following is a sample output; actual values depend on the model and data used):

[
  {
    "id": 2,
    "_score": 0.0325,
    "title": "Principle of Vector Search",
    "content": "Vector search finds semantically similar content by calculating vector distances. It is commonly used in semantic search and recommendation systems."
  },
  {
    "id": 3,
    "_score": 0.0323,
    "title": "Introduction to Full-text Indexes",
    "content": "OceanBase full-text indexes support keyword search for text data, achieving efficient retrieval through word segmentation and inverted indexes."
  }
]

Performance evaluation

When evaluating the performance of hybrid search, you can focus on the following metrics:

You can execute each search method separately. obclient displays the execution time (N rows in set (X.XX sec)) after each query result, allowing you to compare the differences in response time and result quality.

Experience RAG search

Retrieval-Augmented Generation (RAG) is a technique that enhances the accuracy and reliability of large language model responses by first retrieving relevant documents and then generating an answer. OceanBase Database supports the complete RAG process within the database using three built-in AI functions: AI_EMBED (vectorization), AI_RERANK (re-ranking), and AI_COMPLETE (text generation), eliminating the need for external code.

RAG process

Input question → AI_EMBED generates query vector → Vector search for Top-K documents → AI_RERANK re-ranks → AI_COMPLETE generates answer

Procedure

The following example demonstrates a simplified RAG process completed within the database.

1. Input a question and generate a query vector:

   obclient [test]> SET @query = 'What search capabilities does OceanBase support?';
   obclient [test]> SET @query_vector = AI_EMBED('ob_embed', @query);
   

2. Vector search for Top-K documents: Retrieve the Top 3 documents most semantically similar to the query:

   obclient [test]> SET @candidate_docs = (
       SELECT JSON_ARRAYAGG(content)
       FROM (
           SELECT content
           FROM knowledge_base
           WHERE embedding IS NOT NULL
           ORDER BY cosine_distance(embedding, @query_vector) APPROXIMATE LIMIT 3
       ) AS t
   );
   

   Note

   JSON_ARRAYAGG() aggregates the retrieved document content into a JSON array format for use by the subsequent AI_RERANK function.

3. Re-ranking with AI_RERANK (Optional step; requires a registered re-ranking model):

   obclient [test]> SELECT AI_RERANK('ob_rerank', @query, @candidate_docs) AS ranked_results;
   

   The returned result is as follows, where index is the document index and relevance_score is the relevance score, sorted in descending order (schematic output; actual values depend on the model):

   +-------------------------------------------------------------------------------------------------------------+
   | ranked_results                                                                                              |
   +-------------------------------------------------------------------------------------------------------------+
   | [{"index": 0, "relevance_score": 0.9904329776763916}, {"index": 2, "relevance_score": 0.16993996500968933}] |
   +-------------------------------------------------------------------------------------------------------------+
   1 row in set
   

   Note

   The AI_RERANK(model_key, query, documents) function sorts a document list by similarity based on the query terms using a re-ranking model. The re-ranking model can more accurately determine the relevance of documents to the query, improving the quality of answers from the RAG system. If no re-ranking model is registered, you can skip this step and directly use the Top-K results from the vector search.

4. Call AI_COMPLETE to generate an answer: Based on the retrieved document content, call a large language model to generate an answer:

   obclient [test]> SELECT AI_COMPLETE('ob_complete',
       CONCAT('Based on the following document content, answer the user's question. User question:', @query,
           ' Related documents:', CAST(JSON_EXTRACT(@candidate_docs, '$[0]') AS CHAR),
           ' Please answer the user's question concisely and accurately based on the above document content.')) AS answer;
   

   The return result is as follows (schematic output; the actual answer depends on the model):

   +------------------------------------------------------------------------------------------------------------------------------------+
   | answer                                                                                                                             |
   +------------------------------------------------------------------------------------------------------------------------------------+
   | OceanBase supports vector search and full-text index search, and supports hybrid search through DBMS_HYBRID_SEARCH, combining semantic search with keyword search to provide more comprehensive retrieval capabilities.        |
   +------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

   Note

   The AI_COMPLETE(model_key, prompt) function processes the prompt using a registered large language model and returns the generated text. The CONCAT() function is used to concatenate the user's question and the retrieved document content into a complete prompt, guiding the large model to answer the question based on the document content.

Performance evaluation

The integrity and accuracy of the RAG process can be evaluated in the following ways:

Next steps

After you complete the AI scenarios in this topic, you can explore the following:

1. Experience advanced features of hybrid search: Explore advanced features such as multi-vector search, scalar filtering combined with hybrid search, and custom RRF parameter tuning. For details, see Hybrid search by index (SQL interface).
2. Experience advanced OceanBase features: Explore enterprise-level capabilities such as multi-tenancy and resource isolation, high availability and disaster recovery, horizontal scaling, parallel import, data protection and recovery, and online DDL. For details, see Experience multi-tenancy and resource isolation.
3. Deepen understanding of vector indexes: Explore the characteristics and application scenarios of different vector index types such as HNSW_SQ, HNSW_BQ, and IVF. For details, see Overview of vector indexes.

References

• For a complete description of vector data types, see Overview of vector data types. For a complete description of vector indexes, see Overview of vector indexes.
• For a complete description of vector functions (such as distance functions and similarity functions), see Use SQL functions.
• For the syntax and search patterns of full-text queries, see Full-text query.
• For the complete syntax and examples of AI function services (AI_EMBED, AI_COMPLETE, AI_RERANK), see Syntax and examples of AI function services.
• For the complete commands and templates for AI model registration, see AI model registration.
• For a complete description and more examples of hybrid search, see Hybrid search by index (PL interface). For the SQL interface, see Hybrid search by index (SQL interface).
• For an overview and methods of vector embedding technology, see Vector embedding technology.