Full-text and vector hybrid search

This topic describes hybrid search in OceanBase Database: the combination of full-text index and vector index search.

Hybrid search combines vector-based semantic search with keyword search based on full-text indexes and returns results ranked by a combined score. Vector search excels at semantic similarity but is weaker at exact matching of keywords, numbers, and proper nouns; full-text search complements this. Hybrid search is a key feature of vector databases and is widely used in many products.

Usage

Hybrid search is provided by the system package DBMS_HYBRID_SEARCH, which has two subprograms:

For syntax and parameters, see DBMS_HYBRID_SEARCH.

Considerations

• Hybrid search is supported only on heap tables. All examples in this topic use heap tables.
• When using hybrid search, specify the _source field to control which columns are returned. If you omit it, all columns are returned; including vector columns can significantly increase response time (RT).

Use cases and examples

Create sample tables and insert data

This section creates sample tables and inserts data to demonstrate hybrid search in the examples below.

• products table: A basic product table for scalar search. It has product ID, name, description, brand, category, tags, price, stock, release date, on-sale flag, and a vector column vec.

  CREATE TABLE products (
    `product_id` varchar(50) DEFAULT NULL,
    `product_name` varchar(255) DEFAULT NULL,
    `description` text DEFAULT NULL,
    `brand` varchar(100) DEFAULT NULL,
    `category` varchar(100) DEFAULT NULL,
    `tags` varchar(255) DEFAULT NULL,
    `price` decimal(10,2) DEFAULT NULL,
    `stock_quantity` int(11) DEFAULT NULL,
    `release_date` datetime DEFAULT NULL,
    `is_on_sale` tinyint(1) DEFAULT NULL,
    `vec` VECTOR(4) DEFAULT NULL
  ) ORGANIZATION HEAP;
  

  Insert data:

  INSERT INTO products VALUES
  ('prod-001', 'Gamer-Pro Mechanical Keyboard', 'A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.',
   'GamerZone', 'Gaming', 'best-seller,gaming-gear,rgb', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.5,0.1,0.6,0.9]'),
  ('prod-002', 'Gamer-Pro Headset', 'High-fidelity gaming headset with a noise-cancelling microphone.',
   'GamerZone', 'Gaming', 'best-seller,gaming-gear,audio', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.1,0.9,0.2,0]'),
  ('prod-003', 'Eco-Friendly Yoga Mat', 'A non-slip yoga mat made from sustainable and eco-friendly materials.',
   'NatureFirst', 'Sports', 'eco-friendly,health', 49.99, 200, '2023-04-22 00:00:00.000000', 0, '[0.1,0.9,0.3,0]');
  

• products_fulltext table: Same as products but with full-text indexes on product_name, description, and tags for full-text search.

  CREATE TABLE products_fulltext (
      product_id VARCHAR(50),
      product_name VARCHAR(255),
      description TEXT,
      brand VARCHAR(100),
      category VARCHAR(100),
      tags VARCHAR(255),
      price DECIMAL(10, 2),
      stock_quantity INT,
      release_date DATETIME,
      is_on_sale TINYINT(1),
      vec vector(4),
      -- Create full-text indexes on columns used for full-text search
      FULLTEXT INDEX idx_product_name(product_name),
      FULLTEXT INDEX idx_description(description),
      FULLTEXT INDEX idx_tags(tags)
  ) ORGANIZATION HEAP;
  

  Insert data:

  INSERT INTO products_fulltext VALUES
  ('prod-001', 'Gamer-Pro Mechanical Keyboard', 'A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.',
   'GamerZone', 'Gaming', 'best-seller,gaming-gear,rgb', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.5,0.1,0.6,0.9]'),
  ('prod-002', 'Gamer-Pro Headset', 'High-fidelity gaming headset with a noise-cancelling microphone.',
   'GamerZone', 'Gaming', 'best-seller,gaming-gear,audio', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.1,0.9,0.2,0]'),
  ('prod-003', 'Eco-Friendly Yoga Mat', 'A non-slip yoga mat made from sustainable and eco-friendly materials.',
   'NatureFirst', 'Sports', 'eco-friendly,health', 49.99, 200, '2023-04-22 00:00:00.000000', 0, '[0.1,0.9,0.3,0]');
  

• doc_table table: A document table with scalar columns, a vector column, and full-text indexed columns, used for full-text search with scalar filters and for full-text + vector hybrid search.

  CREATE TABLE doc_table(
      c1 INT,
      vector VECTOR(3),
      query VARCHAR(255),
      content VARCHAR(255),
      VECTOR INDEX idx1(vector) WITH (distance=l2, type=hnsw, lib=vsag),
      FULLTEXT INDEX idx2(query),
      FULLTEXT INDEX idx3(content)
  ) ORGANIZATION HEAP;
  

  Insert data:

  INSERT INTO doc_table VALUES
  (1, '[1,2,3]', "hello world", "oceanbase Elasticsearch database"),
  (2, '[1,2,1]', "hello world, what is your name", "oceanbase mysql database"),
  (3, '[1,1,1]', "hello world, how are you", "oceanbase oracle database"),
  (4, '[1,3,1]', "real world, where are you from", "postgres oracle database"),
  (5, '[1,3,2]', "real world, how old are you", "redis oracle database"),
  (6, '[2,1,1]', "hello world, where are you from", "starrocks oceanbase database");
  

• products_vector table: Same structure as products but with a vector index on vec for vector-only search.

  CREATE TABLE products_vector (
    `product_id` varchar(50) DEFAULT NULL,
    `product_name` varchar(255) DEFAULT NULL,
    `description` text DEFAULT NULL,
    `brand` varchar(100) DEFAULT NULL,
    `category` varchar(100) DEFAULT NULL,
    `tags` varchar(255) DEFAULT NULL,
    `price` decimal(10,2) DEFAULT NULL,
    `stock_quantity` int(11) DEFAULT NULL,
    `release_date` datetime DEFAULT NULL,
    `is_on_sale` tinyint(1) DEFAULT NULL,
    `vec` VECTOR(4) DEFAULT NULL,
    -- Create a vector index on the column used for vector search
    VECTOR INDEX idx1(vec) WITH (distance=l2, type=hnsw, lib=vsag)
  ) ORGANIZATION HEAP;
  

  Insert data:

  INSERT INTO products_vector VALUES
  ('prod-001', 'Gamer-Pro Mechanical Keyboard', 'A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.',
   'GamerZone', 'Gaming', 'best-seller,gaming-gear,rgb', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.5,0.1,0.6,0.9]'),
  ('prod-002', 'Gamer-Pro Headset', 'High-fidelity gaming headset with a noise-cancelling microphone.',
   'GamerZone', 'Gaming', 'best-seller,gaming-gear,audio', 149.00, 100, '2023-07-20 00:00:00.000000', 1, '[0.1,0.9,0.2,0]'),
  ('prod-003', 'Eco-Friendly Yoga Mat', 'A non-slip yoga mat made from sustainable and eco-friendly materials.',
   'NatureFirst', 'Sports', 'eco-friendly,health', 49.99, 200, '2023-04-22 00:00:00.000000', 0, '[0.1,0.9,0.3,0]');
  

Scalar search

Typical use cases for scalar search:

• E-commerce: Filter products by brand (e.g., all products from GamerZone).
• Content management: Filter articles or documents by category or author.
• User management: Filter users by status or role (e.g., all VIP users).

Example:

1. Set the search parameters.

   SET @parm = '{
   "query": {
       "bool": {
           "must": [
           {"term": {"brand": "GamerZone"}}
           ]
       }
   },
   "_source": ["product_id", "product_name", "description", "brand", "category", "tags", "price", "stock_quantity", "release_date", "is_on_sale"]
   }';
   

2. Search for rows where brand is "GamerZone".

   SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm));
   

   Example result:

   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | [
   {
       "tags": "best-seller,gaming-gear,rgb",
       "brand": "GamerZone",
       "price": 149.00,
       "_score": 1,
       "category": "Gaming",
       "is_on_sale": 1,
       "product_id": "prod-004",
       "description": "A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.",
       "product_name": "Gamer-Pro Mechanical Keyboard",
       "release_date": "2023-07-20 00:00:00.000000",
       "stock_quantity": 100
   },
   {
       "tags": "best-seller,gaming-gear,audio",
       "brand": "GamerZone",
       "price": 149.00,
       "_score": 1,
       "category": "Gaming",
       "is_on_sale": 1,
       "product_id": "prod-009",
       "description": "High-fidelity gaming headset with a noise-cancelling microphone.",
       "product_name": "Gamer-Pro Headset",
       "release_date": "2023-07-20 00:00:00.000000",
       "stock_quantity": 100
   }
   ] |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

Scalar range search

Typical use cases:

• Price range: Filter products by price (e.g., between 30 and 80).
• Time range: Find orders or logs in a time window (e.g., last 30 days).
• Numeric range: Filter by rating, stock, etc. (e.g., rating between 4 and 5).

Example:

1. Set the search parameters.

   SET @parm = '{
   "query": {
       "range" : {
       "price" : {
           "gte" : 30,
           "lte" : 80
       }
       }
   },
   "_source": ["product_id", "product_name", "description", "brand", "category", "tags", "price", "stock_quantity", "release_date", "is_on_sale"]
   }';
   

2. Search for rows where price is in the range [30, 80].

   SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm));
   

   Example result:

   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products', @parm))                                                                                                                                                                                                                                                                                                                                                                                          |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | [
   {
       "tags": "eco-friendly,health",
       "brand": "NatureFirst",
       "price": 49.99,
       "_score": true,
       "category": "Sports",
       "is_on_sale": 0,
       "product_id": "prod-003",
       "description": "A non-slip yoga mat made from sustainable and eco-friendly materials.",
       "product_name": "Eco-Friendly Yoga Mat",
       "release_date": "2023-04-22 00:00:00.000000",
       "stock_quantity": 200
   }
   ] |
   +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

Full-text search

Typical use cases:

• Document search: Find documents containing specific keywords (e.g., "how to use" in an FAQ).
• Product search: Fuzzy search by name or description (e.g., products containing "OceanBase").
• Knowledge base: Search FAQs and help docs for related questions and answers.

Example:

1. Set the search parameters.

   SET @query_str_with_mini = '{
   "query": {
       "query_string": {
       "type": "best_fields",
       "fields": ["product_name^3", "description^2.5", "tags^1.5"],
       "query": "Gamer-Pro^2 keyboard^1.5 audio^1.2",
       "boost": 1.5
       }
   },
   "_source": ["product_id", "product_name", "description", "brand", "category", "tags", "price", "stock_quantity", "release_date", "is_on_sale"]
   }';
   

2. Search for rows where product_name, description, or tags contain the keywords "Gamer-Pro", "keyboard", and "audio", sorted by the configured field and keyword weights.

   SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products_fulltext', @query_str_with_mini));
   

   Example result:

   +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products_fulltext', @query_str_with_mini))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
   +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | [
   {
       "tags": "best-seller,gaming-gear,rgb",
       "brand": "GamerZone",
       "price": 149.00,
       "_score": 4.569735248749978,
       "category": "Gaming",
       "is_on_sale": 1,
       "product_id": "prod-001",
       "description": "A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.",
       "product_name": "Gamer-Pro Mechanical Keyboard",
       "release_date": "2023-07-20 00:00:00.000000",
       "stock_quantity": 100
   },
   {
       "tags": "best-seller,gaming-gear,audio",
       "brand": "GamerZone",
       "price": 149.00,
       "_score": 1.7338881172399914,
       "category": "Gaming",
       "is_on_sale": 1,
       "product_id": "prod-002",
       "description": "High-fidelity gaming headset with a noise-cancelling microphone.",
       "product_name": "Gamer-Pro Headset",
       "release_date": "2023-07-20 00:00:00.000000",
       "stock_quantity": 100
   }
   ] |
   +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

Full-text search with scalar filters

Typical use cases:

• Precise search: Run keyword search under specific conditions (e.g., only in published articles).
• Access control: Search only within data the user is allowed to see (e.g., orders in a time range).
• Category search: Run keyword search within a category (e.g., active users only).

Example:

1. Set the search parameters.

   -- Scalar filter: c1 >= 2
   SET @query_str = '{
   "query": {
       "bool" : {
       "must" : [
           {"query_string": {
           "fields": ["query", "content"],
           "query": "hello what oceanbase mysql"}
           }
       ],
       "filter" : [
           {"range": {"c1": {"gte" : 2}}}
       ]
       }
   },
   "_source": ["c1", "query", "content"]
   }';
   

2. Search for rows where c1 is greater than or equal to 2 and the full-text condition matches.

   SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @query_str));
   

   Example result:

   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @query_str))                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | [
   {
       "c1": 2,
       "query": "hello world, what is your name",
       "_score": 2.170969786679347,
       "content": "oceanbase mysql database"
   },
   {
       "c1": 3,
       "query": "hello world, how are you",
       "_score": 0.3503184713375797,
       "content": "oceanbase oracle database"
   },
   {
       "c1": 6,
       "query": "hello world, where are you from",
       "_score": 0.3503184713375797,
       "content": "starrocks oceanbase database"
   }
   ] |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

Vector search

Typical use cases:

• Semantic search: Find content by meaning (e.g., semantically related Q&A in a knowledge base).
• Recommendations: Recommend similar items (e.g., similar products on an e-commerce site).
• Image search: Find similar images by feature vectors.
• Q&A: Find semantically related questions and answers in a knowledge base or customer service system.

Example:

1. Set the search parameters.

   -- field: vector column; k: number of results; query_vector: query vector
   SET @parm = '{
   "knn" : {
       "field": "vec",
       "k": 3,
       "query_vector": [0.5,0.1,0.6,0.9]
   },
   "_source": ["product_id", "product_name", "description", "brand", "category", "tags", "price", "stock_quantity", "release_date", "is_on_sale"]
   }';
   

2. Search for rows whose vec is most similar to [0.5,0.1,0.6,0.9].

   SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products_vector', @parm));
   

   Example result:

   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products_vector', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 |
   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | [
   {
       "tags": "best-seller,gaming-gear,rgb",
       "brand": "GamerZone",
       "price": 149.00,
       "_score": 1.0,
       "category": "Gaming",
       "is_on_sale": 1,
       "product_id": "prod-001",
       "description": "A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.",
       "product_name": "Gamer-Pro Mechanical Keyboard",
       "release_date": "2023-07-20 00:00:00.000000",
       "stock_quantity": 100
   },
   {
       "tags": "eco-friendly,health",
       "brand": "NatureFirst",
       "price": 49.99,
       "_score": 0.43405784,
       "category": "Sports",
       "is_on_sale": 0,
       "product_id": "prod-003",
       "description": "A non-slip yoga mat made from sustainable and eco-friendly materials.",
       "product_name": "Eco-Friendly Yoga Mat",
       "release_date": "2023-04-22 00:00:00.000000",
       "stock_quantity": 200
   },
   {
       "tags": "best-seller,gaming-gear,audio",
       "brand": "GamerZone",
       "price": 149.00,
       "_score": 0.42910841,
       "category": "Gaming",
       "is_on_sale": 1,
       "product_id": "prod-002",
       "description": "High-fidelity gaming headset with a noise-cancelling microphone.",
       "product_name": "Gamer-Pro Headset",
       "release_date": "2023-07-20 00:00:00.000000",
       "stock_quantity": 100
   }
   ] |
   +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

Vector search with scalar filters

Typical use cases:

• Precise search: Run vector search under specific conditions (e.g., only in published articles).
• Permission control: Search only within data the user can access (e.g., orders in a time range).
• Category search: Run vector search within a category (e.g., active users only).

Example:

1. Set the search parameters.

   -- Scalar filter: brand = "GamerZone"
   SET @parm = '{
   "knn" : {
       "field": "vec",
       "k": 3,
       "query_vector": [0.1,0.5,0.3,0.7],
       "filter" : [
           {"term" : {"brand": "GamerZone"} }
       ]
   },
   "_source": ["product_id", "product_name", "description", "brand", "category", "tags", "price", "stock_quantity", "release_date", "is_on_sale"]
   }';
   

2. Search for rows whose vec is similar to [0.1,0.5,0.3,0.7] and brand is "GamerZone".

   SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products_vector', @parm));
   

   Example result:

   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products_vector', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | [
   {
       "tags": "best-seller,gaming-gear,rgb",
       "brand": "GamerZone",
       "price": 149.00,
       "_score": 0.59850837,
       "category": "Gaming",
       "is_on_sale": 1,
       "product_id": "prod-001",
       "description": "A responsive mechanical keyboard with customizable RGB lighting for the ultimate gaming experience.",
       "product_name": "Gamer-Pro Mechanical Keyboard",
       "release_date": "2023-07-20 00:00:00.000000",
       "stock_quantity": 100
   },
   {
       "tags": "best-seller,gaming-gear,audio",
       "brand": "GamerZone",
       "price": 149.00,
       "_score": 0.55175342,
       "category": "Gaming",
       "is_on_sale": 1,
       "product_id": "prod-002",
       "description": "High-fidelity gaming headset with a noise-cancelling microphone.",
       "product_name": "Gamer-Pro Headset",
       "release_date": "2023-07-20 00:00:00.000000",
       "stock_quantity": 100
   }
   ] |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

Multi-vector search

Multi-vector search runs search over multiple vector indexes and returns the most similar rows.

Example:

1. Set the search parameters.

   -- Three vector search legs: each specifies the vector field, result count (k), and query vector
   SET @param_multi_knn = '{
   "knn" : [{
               "field": "vec1",
               "k": 5,
               "query_vector": [0.5,0.1,0.6,0.9]
           },
           {
               "field": "vec2",
               "k": 5,
               "query_vector": [0.2,0.3,0.4,0.5]
           },
           {
               "field": "vec3",
               "k": 5,
               "query_vector": [0.1,0.2,0.3,0.4]
           }
           ],
       "size" : 5,
       "_source": ["product_id", "product_name", "description"]
   }';
   

2. Run the query and return the results.

   SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products_multi_vector', @param_multi_knn));
   

   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | json_pretty(DBMS_HYBRID_SEARCH.SEARCH('products_multi_vector', @param_multi_knn))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | [
   {
       "_score": 3.0,
       "product_id": "prod-001",
       "description": "A responsive mechanical keyboard",
       "product_name": "Gamer-Pro Mechanical Keyboard"
   },
   {
       "_score": 2.0957750699999997,
       "product_id": "prod-002",
       "description": "High-fidelity gaming headset",
       "product_name": "Gamer-Pro Headset"
   },
   {
       "_score": 1.86262927,
       "product_id": "prod-003",
       "description": "A non-slip yoga mat",
       "product_name": "Eco-Friendly Yoga Mat"
   }
   ] |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   1 row in set
   

Full-text and vector hybrid search

Typical use cases:

• Intelligent search: Combine keyword matching and semantic understanding (e.g., user query "I need a gaming keyboard" matches "gaming" and "keyboard" and understands "gaming equipment").
• Document search: Support both exact keywords and semantic similarity (e.g., search "database optimization" to find that phrase and related content like "performance tuning", "query optimization").
• Product discovery: Search by product name or by natural-language need (e.g., "laptop for office work" matches keywords and the semantic need "business office").

Example:

1. Set the search parameters.

   SET @parm = '{
     "query": {
       "bool": {
         "should": [
           {"match": {"query": "hi hello"}},
           {"match": { "content": "oceanbase mysql" }}
         ]
       }
     },
      "knn" : {
         "field": "vector",
         "k": 5,
         "query_vector": [1,2,3]
     },
     "_source" : ["query", "content", "_keyword_score", "_semantic_score"]
   }';
   

2. Run the query and return the results.

   SELECT json_pretty(DBMS_HYBRID_SEARCH.SEARCH('doc_table', @parm));
   

   Example result:

   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | json_pretty(dbms_hybrid_search.search('doc_table', @parm))                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
   +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
   | [
   {
       "query": "hello world, what is your name",
       "_score": 2.835628417884166,
       "content": "oceanbase mysql database",
       "_keyword_score": 2.5022950878841663,
       "_semantic_score": 0.33333333
   },
   {
       "query": "hello world",
       "_score": 1.7219400929592013,
       "content": "oceanbase Elasticsearch database",
       "_keyword_score": 0.7219400929592014,
       "_semantic_score": 1.0
   },
   {
       "query": "hello world, how are you",
       "_score": 1.0096539326751595,
       "content": "oceanbase oracle database",
       "_keyword_score": 0.7006369426751594,
       "_semantic_score": 0.30901699
   },
   {
       "query": "real world, how old are you",
       "_score": 0.41421356,
       "content": "redis oracle database",
       "_keyword_score": null,
       "_semantic_score": 0.41421356
   },
   {
       "query": "real world, where are you from",
       "_score": 0.30901699,
       "content": "postgres oracle database",
       "_keyword_score": null,
       "_semantic_score": 0.30901699
   }
   ] |
   

Full-text and vector RRF hybrid search

By default, full-text and vector subquery results are combined with a weighted hybrid. You can use the rank syntax to switch to RRF (Reciprocal Rank Fusion) for ranking. Typical use cases:

• Multi-dimensional ranking: Combine several search dimensions (e.g., academic search: keyword match and semantic relevance).
• Fairness: Give different search signals a reasonable share of the result (e.g., product title/description and image/video features).
• Complex queries: Multiple conditions (e.g., symptoms plus history and test results in a medical system).

Example:

Set the search parameters:

SET @rrf_query_param = '{
  "query": {
    "query_string": {
      "fields": ["title", "author", "description"],
      "query": "fiction American Dream"
    }
  },
  "knn" : {
    "field": "vector_embedding",
    "k": 5,
    "query_vector": [0.1, 0.2, 0.3, 0.4]
  },
  "rank" : {
    "rrf" : {
      "rank_window_size" : 10,
      "rank_constant" : 60
    }
  },
  "_source": ["title", "author", "description"]
}';

The RRF algorithm fuses rankings from multiple subquery result sets to compute the final relevance score. Formula:

score = 0.0
for q in queries:
    if d in result(q):
        score += 1.0 / ( k + rank( result(q), d ) ) # k is rank_constant
return score

Summary

The examples in this topic show how hybrid search can be used in practice:

• Smarter search: Add semantic understanding to keyword search for more accurate, intent-aligned results.
• Better UX: Support natural-language queries and faster, easier discovery.
• Broad use: From basic filtering to intelligent recommendations in e-commerce, content systems, knowledge bases, and customer service.
• Combined strength: Exact match and semantic match together improve accuracy and coverage.

Hybrid search is well suited to large-scale unstructured data and to building intelligent search and recommendation systems.

Related topics

• DBMS_HYBRID_SEARCH overview