Hybrid Vector + Keyword Search Performance in Oracle 26ai: Optimization Strategies

Oracle 26ai executes hybrid vector and keyword searches within a single execution plan, leveraging Oracle Text indexes alongside HNSW vector indexes to minimize latency while maximizing recall through pre-filtering, post-filtering, and approximate search strategies.

Oracle 26ai introduces native support for hybrid search patterns that combine traditional text filtering with vector similarity, eliminating the need for client-side orchestration. According to the oracle-devrel/oracle-ai-developer-hub repository, the database engine pushes both predicates down to the storage layer, enabling single-pass execution that dramatically reduces CPU and I/O overhead compared to two-step retrieval patterns.

Single-Pass Query Architecture

The Oracle 26ai optimizer recognizes two independent index structures—an Oracle Text index for keyword filtering and an HNSW vector index for similarity calculation—and generates unified execution plans that evaluate both predicates at the storage layer. This architectural approach eliminates round-trips between client and database, allowing the engine to stream filtered rows directly into the vector ranking step.

When processing hybrid queries, the database reads data once from the storage engine rather than requiring separate passes for keyword and vector operations. This single-pass I/O pattern significantly reduces disk overhead compared to naïve two-step approaches that fetch intermediate IDs between searches.

Pre-Filter Strategy: High Selectivity Keyword Filtering

The pre-filter hybrid approach applies the keyword constraint before computing vector distances, dramatically reducing the candidate set size. When the CONTAINS predicate is highly selective, the database avoids expensive vector calculations on irrelevant rows.

In this pattern, the SQL query first evaluates WHERE CONTAINS(text, :kw, 1) > 0 to leverage the Oracle Text index, then orders the remaining rows by VECTOR_DISTANCE(embedding, :q, COSINE). According to the implementation in workshops/information_retrieval_to_RAG/docs/part-4-retrieval.md (lines 24-30), this approach delivers optimal performance when working with well-defined keywords such as specific product names or precise technical terms.

def hybrid_search_pre_filter(conn, embedding_model, search_phrase, top_k=10):
    # Encode query once

    q_emb = embedding_model.encode(
        [f"search_query: {search_phrase}"], convert_to_numpy=True,
        normalize_embeddings=True
    )[0].astype(np.float32).tolist()
    q_vec = array.array('f', q_emb)

    sql = f"""
        SELECT arxiv_id, title, SUBSTR(text,1,200) AS text_snippet,
               ROUND(1 - VECTOR_DISTANCE(embedding, :q, COSINE),4) AS similarity_score
        FROM research_papers
        WHERE CONTAINS(text, :kw, 1) > 0               -- keyword filter
        ORDER BY similarity_score DESC
        FETCH APPROX FIRST {top_k} ROWS ONLY WITH TARGET ACCURACY 90
    """
    with conn.cursor() as cur:
        cur.execute(sql, q=q_vec, kw=search_phrase)
        rows = cur.fetchall()
        cols = [d[0] for d in cur.description]
    return rows, cols

Performance consideration: Use highly selective keywords to keep vector computation bounded. When the text filter eliminates 90% of rows, the HNSW distance calculation operates only on the remaining 10%, reducing CPU utilization proportionally.

Post-Filter Strategy: Semantic Priority with Keyword Refinement

The post-filter hybrid strategy prioritizes semantic recall by first retrieving the top candidate_k rows using vector similarity, then applying the keyword filter to that reduced set. This approach guarantees exploration of the semantic space before precision filtering occurs.

The trade-off involves a slightly larger vector scan compared to pre-filtering, as the engine must process candidate_k vectors before applying the text constraint. As demonstrated in notebooks/oracle_26ai_unique_features_demo.ipynb, this pattern excels when semantic meaning matters more than exact token presence, such as when filtering product results by price and stock availability after semantic retrieval.

def hybrid_search_post_filter(conn, embedding_model, search_phrase,
                              top_k=10, candidate_k=200):
    q_emb = embedding_model.encode(
        [f"search_query: {search_phrase}"], convert_to_numpy=True,
        normalize_embeddings=True
    )[0].astype(np.float32).tolist()
    q_vec = array.array('f', q_emb)

    sql = f"""
        WITH vec_candidates AS (
            SELECT arxiv_id, title, SUBSTR(text,1,200) AS text_snippet,
                   1 - VECTOR_DISTANCE(embedding, :q, COSINE) AS similarity_score
            FROM research_papers
            ORDER BY similarity_score DESC
            FETCH APPROX FIRST {candidate_k} ROWS ONLY WITH TARGET ACCURACY 90
        )
        SELECT arxiv_id, title, text_snippet, similarity_score
        FROM vec_candidates
        WHERE CONTAINS(text, :kw, 1) > 0               -- keyword post-filter
        ORDER BY similarity_score DESC
        FETCH FIRST {top_k} ROWS ONLY
    """
    with conn.cursor() as cur:
        cur.execute(sql, q=q_vec, kw=search_phrase)
        rows = cur.fetchall()
        cols = [d[0] for d in cur.description]
    return rows, cols

Tuning guideline: Select candidate_k large enough to preserve recall—typically 10-20x the final top_k—but small enough to avoid scanning the entire vector space. The FETCH APPROX clause with TARGET ACCURACY 90 allows the HNSW index to stop early once reaching the specified accuracy threshold, keeping latency sub-second even when processing thousands of vectors.

Reciprocal Rank Fusion Overhead

Oracle 26ai supports Reciprocal Rank Fusion (RRF) for combining independent keyword and vector rank lists using the formula 1/(k+rank). This approach runs pure text and pure vector searches independently, then merges results with modest CPU overhead for the rank calculation.

While RRF adds computational cost for the merge operation, it avoids client-side post-processing and provides balanced ranking when neither pre-filtering nor post-filtering semantics are ideal. The additional overhead remains predictable and scales linearly with result set size, making it suitable for high-throughput production workloads.

Parallel Execution and Scalability

Both the Oracle Text index scan and vector distance computation benefit from parallel execution across available CPU cores. The database engine automatically parallelizes hybrid query processing, enabling near-linear scaling on modern multi-core servers.

Because both predicates execute inside the database kernel, resource contention is minimized and cache locality is maximized. This architecture keeps latency low under concurrent load while maintaining the precision benefits of hybrid filtering.

Summary

• Pre-filter hybrid delivers optimal performance when keyword selectivity is high, limiting vector computation to a small candidate set via CONTAINS before VECTOR_DISTANCE evaluation.
• Post-filter hybrid prioritizes semantic recall by retrieving vector candidates first, then applying text filters, trading slightly higher I/O for better result coverage.
• Single-pass execution eliminates round-trip latency and redundant I/O by evaluating both Oracle Text and HNSW indexes within one query plan.
• Approximate search using FETCH APPROX ... WITH TARGET ACCURACY 90-95 keeps vector operations fast while maintaining result quality.
• Parallel processing across CPU cores ensures hybrid queries scale efficiently under load.

Frequently Asked Questions

What is the difference between pre-filter and post-filter hybrid search in Oracle 26ai?

Pre-filter hybrid applies the CONTAINS keyword predicate before calculating VECTOR_DISTANCE, reducing the dataset size early in the execution pipeline. Post-filter hybrid retrieves the top candidate vectors first, then applies the keyword constraint to that subset. Choose pre-filtering for high-selectivity keywords that eliminate most rows, and post-filtering when semantic coverage is more important than keyword precision.

How does Oracle 26ai achieve sub-second latency on large vector datasets?

The database uses HNSW indexes with approximate search capabilities, specified via FETCH APPROX FIRST ... WITH TARGET ACCURACY 90-95. This allows the vector engine to stop traversing the index once it reaches the target accuracy threshold rather than computing exact distances for the entire dataset. Combined with parallel execution across CPU cores, this approach maintains low latency even when processing thousands of vectors.

Can hybrid vector and keyword searches utilize multiple CPU cores?

Yes. Oracle 26ai automatically parallelizes both the Oracle Text index scan and the vector distance computation across available CPU cores. As implemented in the oracle-devrel/oracle-ai-developer-hub source code, the execution engine distributes work internally without requiring manual query hints, enabling linear scalability on modern multi-core database servers.

Why is single-pass execution important for hybrid search performance?

Single-pass execution allows the database to evaluate both the keyword predicate and vector similarity while reading table data only once. This eliminates the network round-trips and redundant I/O associated with two-step approaches that fetch intermediate results between separate keyword and vector queries. The combined operation executes entirely within the storage layer, reducing latency to levels comparable with pure vector searches while delivering the precision benefits of text filtering.

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