How Oracle AI Database Handles Vector Search and Hybrid Search in a Single SQL Query

Oracle AI Database allows developers to perform vector similarity search and full-text search together in one SQL query using the DBMS_HYBRID_VECTOR.SEARCH function with a JSON payload that configures both modalities.

Oracle AI Database (Oracle Database 26ai) natively supports hybrid search capabilities that combine semantic vector similarity with traditional Oracle Text keyword matching. This article examines the source code from the oracle-devrel/oracle-ai-developer-hub repository to demonstrate how the DBMS_HYBRID_VECTOR package enables vector search and hybrid search in a single SQL query.

Hybrid Search Architecture

Oracle AI Database implements hybrid search through three core components that work together to execute both vector and text queries within a single database transaction.

Hybrid Vector Index

The Hybrid Vector Index enables simultaneous semantic and lexical search on the same column. You create this index using SQL syntax that specifies an embedding model and vector index type, as shown in setup-hybrid-search.sql (lines 44-46):

CREATE HYBRID VECTOR INDEX POLICY_HYBRID_IDX
ON POLICY_DOCS(content)
PARAMETERS('MODEL ALL_MINILM_L12_V2 VECTOR_IDXTYPE HNSW');

This index stores dense vectors generated by the specified ONNX embedding model (e.g., all_MiniLM_L12_v2) while maintaining Oracle Text indexing capabilities on the raw CLOB content.

DBMS_HYBRID_VECTOR.SEARCH

The DBMS_HYBRID_VECTOR.SEARCH function accepts a JSON payload describing the search request and returns a JSON array of results with individual scores. According to the implementation in OracleHybridDocumentRetriever.java (lines 44-48), the SQL syntax is:

SELECT DBMS_HYBRID_VECTOR.SEARCH(JSON(?)) FROM DUAL

When the JSON payload contains both vector and text configuration blocks, the database executes both searches, applies the specified fusion algorithm, and returns unified results with separate scoring for each modality.

JSON Payload Structure

The retriever constructs a JSON object that defines all search parameters. In OracleHybridDocumentRetriever.java (lines 83-102), the buildSearchJson method assembles:

private String buildSearchJson(String queryText) {
    String escaped = escapeJson(queryText);
    String containsClause = buildContainsClause(queryText);
    return """
        {
          "hybrid_index_name": "%s",
          "search_scorer": "%s",
          "search_fusion": "UNION",
          "vector": {
            "search_text": "%s"
          },
          "text": {
            "contains": "%s"
          },
          "return": {
            "values": ["chunk_text", "score", "vector_score", "text_score"],
            "topN": %d
          }
        }
        """.formatted(indexName, scorer, escaped, containsClause, topK);
}

Key parameters include:

• hybrid_index_name – References the index created via CREATE HYBRID VECTOR INDEX.
• search_scorer – Specifies the similarity metric (e.g., cosine).
• search_fusion – Determines how to merge results (e.g., UNION keeps the best score per document).
• vector.search_text – Text to embed and compare against stored vectors.
• text.contains – Oracle Text CONTAINS clause for keyword matching.

Executing Vector and Hybrid Searches

You can execute pure vector similarity or hybrid search using the same SQL function call by adjusting the JSON payload contents.

Pure Vector Search

For vector-only search, omit the text block from the JSON payload. The Spring AI integration in PetStoreSearchController.java (lines 20-27) demonstrates this pattern:

List<Document> results = vectorStore.similaritySearch(
        SearchRequest.builder()
                .query(query)
                .topK(10)
                .similarityThreshold(0.4)
                .build());

This approach uses only the vector configuration block, performing semantic similarity against the HNSW vector index without text search components.

Hybrid Search Execution

For hybrid search, include both vector and text blocks. The execution follows this flow:

1. Build the payload – Construct JSON with both search modalities.
2. Execute SQL – Call DBMS_HYBRID_VECTOR.SEARCH via JDBC:

String resultJson = jdbcTemplate.queryForObject(
        "SELECT DBMS_HYBRID_VECTOR.SEARCH(JSON(?)) FROM DUAL",
        String.class,
        searchJson);

3. Parse results – The JSON response includes vector_score, text_score, and fused score for each document, parsed in OracleHybridDocumentRetriever.java (lines 58-71).

The database automatically handles embedding generation for the vector component and Oracle Text parsing for the keyword component, then fuses results according to the search_fusion strategy.

Performance and Fusion Strategies

The search_fusion parameter controls how Oracle AI Database combines vector and text results:

• UNION – Returns documents found by either method, keeping the highest individual score for ranking.
• INTERSECTION – Returns only documents found by both methods (not shown in source but supported by the API).

The JSON response format specified in the return.values array determines which scores appear in the output:

• score – The final fused relevance score.
• vector_score – Cosine similarity between query and document embeddings.
• text_score – Oracle Text relevance score for keyword matches.

Comparison: Single SQL vs. Application-Level Join

Oracle AI Database approach (DBMS_HYBRID_VECTOR.SEARCH) executes both searches, fusion, and ranking entirely within the database engine. This eliminates network round-trips between separate vector and text databases and leverages native HNSW indexing for sub-millisecond similarity calculations.

Application-level join would require separate queries to a vector store and a text search engine, with the application handling result merging and score normalization. The Oracle approach reduces latency and ensures transactional consistency since both indexes reside on the same table column.

Summary

• Oracle AI Database provides native hybrid search through the DBMS_HYBRID_VECTOR package and CREATE HYBRID VECTOR INDEX syntax.
• A single SQL query (SELECT DBMS_HYBRID_VECTOR.SEARCH(JSON(?)) FROM DUAL) handles both vector similarity and Oracle Text search when the JSON payload includes both vector and text configuration blocks.
• The search_fusion parameter determines how results merge, with UNION providing the best single score per document from either search modality.
• Source files in oracle-devrel/oracle-ai-developer-hub demonstrate Java integration via OracleHybridDocumentRetriever.java and index creation in setup-hybrid-search.sql.

Frequently Asked Questions

What is the difference between pure vector search and hybrid search in Oracle AI Database?

Pure vector search compares query embeddings to stored vectors using similarity metrics like cosine distance, while hybrid search combines this with Oracle Text keyword matching in the same SQL execution. According to the source code in OracleHybridDocumentRetriever.java, hybrid search requires both vector and text blocks in the JSON payload sent to DBMS_HYBRID_VECTOR.SEARCH, whereas vector-only search omits the text configuration.

How does Oracle AI Database combine vector and text scores?

The database uses the search_fusion strategy specified in the JSON payload to combine results. When set to UNION, the engine executes both searches independently, then merges the result sets while keeping the highest individual score (vector_score or text_score) as the final score for ranking. This fusion happens entirely within the database engine without requiring application-level processing.

What embedding models does Oracle AI Database support for vector search?

Oracle AI Database supports ONNX embedding models specified during index creation. The example in setup-hybrid-search.sql uses ALL_MINILM_L12_V2, but any compatible ONNX model can be specified in the PARAMETERS clause of CREATE HYBRID VECTOR INDEX. The database automatically generates embeddings for query text using this model when processing DBMS_HYBRID_VECTOR.SEARCH calls.

Can I use Oracle AI Database hybrid search with existing Oracle Text indexes?

Yes, the Hybrid Vector Index builds upon Oracle Text infrastructure while adding vector capabilities. You create it using CREATE HYBRID VECTOR INDEX on a column containing text data, specifying both a text index configuration and a vector model in the PARAMETERS clause. This single index supports both traditional CONTAINS queries and vector similarity searches, or both simultaneously through the DBMS_HYBRID_VECTOR API.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}