Exploring Hybrid Vector Search in Oracle AI Database 26ai - Part I

Keyword search and semantic search solve different problems. Keyword search finds documents containing particular words or expressions, while semantic search finds documents with meanings similar to a question—even when they use different vocabulary.

Oracle’s hybrid vector indexes bring these capabilities together, allowing us to search the same indexed content using keywords, vectors, or a combination of both.

In this post, we’ll use descriptions from the US National Park Service to create a hybrid vector index, inspect its generated chunks, and explore semantic and hybrid queries.

What is a hybrid vector index?

A hybrid vector index combines Oracle Text indexing with vector indexing. For the text-column example in this post, Oracle handles document chunking and embedding generation as part of the indexing pipeline. The vector component can use either an IVF or HNSW index

This gives us three useful search options:

• Keyword search: Find documents matching an Oracle Text expression.
• Semantic search: Find content similar in meaning to a question.
• Hybrid search: Combine results from both approaches.

The appropriate option depends on the question. Semantic search can help with natural-language questions, while keyword search is useful when particular terms matter.

Oracle Hybrid Vector Index

Source documents → Text indexing, chunking and embeddings

Oracle Text index Inverted index of terms to documents Keyword query fossils OR prehistoric Keyword results Text scores and ranks

Vector index Document embeddings as vector points Query embedding Ancient fossils and prehistoric mammals Semantic results Vector scores and ranks

↓
Fusion — RRF / RSF / WRRF
RRF combines ranks, not raw scores.
↓
Final ranked results

Keywords find matching terms. Vectors find similar meaning. Fusion combines the results.

Why chunking matters

Embedding an entire document as a single vector can dilute its meaning when the document covers several unrelated topics. Embedding models also have input-length limits.

Chunking divides the text into smaller passages so that each embedding represents a more focused piece of content.

There is no universally correct chunk size. Small chunks may lose useful context, while large chunks may combine too many ideas or exceed the embedding model’s input limit. Chunk size and overlap should therefore be evaluated against representative questions.

For this example, I use the all-MiniLM-L12-v2 model, previously loaded into the database as MY_DEMO_MODEL. Check the input limits of the actual ONNX model you load; the original Sentence Transformers model documents a default truncation length of 256 word pieces. Words and model tokens are not interchangeable. Model card

Prepare the park descriptions

The National Park Service provides an API containing descriptions and other information about parks and related sites. An API key is required to retrieve the data. NPS API guides

For this demonstration, I downloaded a JSON response and uploaded it to OCI Object Storage.

The following example extracts selected fields from the response’s data array into a relational table. Replace the Object Storage URL with your own location.

demo@ADB26ai> create table parks_info
  2  as
  3  with rws as (
  4  select *
  5  from external(
  6    ( json_data json )
  7    type oracle_bigdata
  8       access parameters(
  9                     com.oracle.bigdata.fileformat = jsondoc
 10                     com.oracle.bigdata.credential.name = OCI$RESOURCE_PRINCIPAL
 11                     )
 12   location ('https://objectstorage.us-ashburn-1.oraclecloud.com/n/ax3h6mpnbnhx/b/tinno-etl-data-backup-dev/o/TEST/park_response_full.json')
 13   reject limit 0) )
 14   select jt.*
 15   from rws , json_table( json_data, '$.data[*]'
 16      columns( park_id varchar2(80) path '$.id'
 17          , park_fullName varchar2(500) path '$.fullName'
 18          , park_Code varchar2(20) path '$.parkCode'
 19          , park_description varchar2(1000) path '$.description'
 20          , park_latitude varchar2(80) path '$.latitude'
 21          , park_longitude varchar2(80) path '$.longitude'
 22          , park_states varchar2(20) path '$.states'
 23          , park_directions_info varchar2(1000) path '$.directionsInfo'
 24          , park_directions_url varchar2(200) path '$.directionsUrl'
 25          , park_weatherInfo varchar2(1000) path '$.weatherInfo'
 26      )
 27   ) jt ;

Table created.

This example assumes that the Autonomous Database resource principal is enabled for the executing user and has permission to read the object.

Review a few rows before indexing.

demo@ADB26ai> SELECT park_description
  2  FROM parks_info
  3  ORDER BY park_fullname
  4  FETCH FIRST 5 ROWS ONLY;

PARK_DESCRIPTION
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
For over a century people from around the world have come to rural Central Kentucky to honor the humble beginnings of our 16th president, Abraham Lincoln. His early life on Kentucky's frontier shaped
his character and prepared him to lead the nation through Civil War. Visit our country's first memorial to Lincoln, built with donations from young and old, and the site of his childhood home.

Acadia National Park protects the natural beauty of the highest rocky headlands along the Atlantic coastline of the United States, an abundance of habitats, and a rich cultural heritage. At 4 million
visits a year, it's one of the top 10 most-visited national parks in the United States. Visitors enjoy 27 miles of historic motor roads, 158 miles of hiking trails, and 45 miles of carriage roads.

From the sweet little farm at the foot of PennÆs Hill to the gentlemanÆs country estate at Peace field, Adams National Historical Park is the story of ôheroes, statesmen, philosophersàand learned wome
nö whose ideas and actions helped to transform thirteen disparate colonies into one united nation.

Over 200,000 African-American soldiers and sailors served in the U.S. Army and Navy during the Civil War. Their service helped to end the war and free over four million slaves. The African American Ci
vil War Memorial honors their service and sacrifice.

The African Burial Ground stands as the oldest and largest known excavated burial site in North America for both free and enslaved Africans. It offers a profound testament to the enduring legacy of Af
rican communities whose labor, resilience, and cultural contributions were fundamental in shaping the development of New York.

The dataset includes descriptions of landscapes, wildlife, historic sites, and fossil deposits, giving us a useful range of subjects to search.

Create the hybrid vector index

If the embedding model is already available in the database, the basic index definition is straightforward:

demo@ADB26ai> create hybrid vector index park_description_hvi
  2  on parks_info( park_description )
  3  parameters( 'MODEL MY_DEMO_MODEL');

Index created.

This uses the default vector-index and chunking settings. Oracle’s documented default vector index type is IVF. To control chunking and vector-index settings, create a vectorizer preference:

demo@ADB26ai> begin
  2     dbms_vector_chain.create_preference(
  3             pref_name => 'MY_HYBRID_INDEX_PREF'
  4             , pref_type => dbms_vector_chain.VECTORIZER
  5             , params => json_object('vector_idxtype' value 'ivf'
  6              , 'distance' value 'cosine'
  7              , 'accuracy' value 90 , 'model' value 'MY_DEMO_MODEL'
  8              , 'by' value 'words' , 'max' value 100
  9              , 'overlap' value 0, 'split' value 'recursively'
 10              returning json)
 11   );
 12  end;
 13  /

PL/SQL procedure successfully completed.

Here, the preference requests chunks of up to 100 words, no overlap, cosine distance, and an IVF vector index. The accuracy setting is an approximate-search target. It does not mean that the returned documents will be “90% relevant.” To use the preference in this demonstration, recreate the index:

demo@ADB26ai> drop index if exists park_description_hvi;

Index dropped.

demo@ADB26ai> create hybrid vector index park_description_hvi
  2  on parks_info( park_description )
  3  parameters( 'VECTORIZER MY_HYBRID_INDEX_PREF');

Index created.

demo@ADB26ai>

Inspect the generated chunks

For this non-partitioned hybrid vector index, Oracle exposes a view named: PARK_DESCRIPTION_HVI$VECTORS

We can inspect the generated chunk text and metadata:

demo@ADB26ai> select *
  2  from PARK_DESCRIPTION_HVI$VECTORS
  3  fetch first 5 rows only;


DOC_ROWID           : AAQVAeABUAAPJSpAAu
DOC_CHUNK_ID        : 1
DOC_CHUNK_COUNT     : 1
DOC_CHUNK_OFFSET    : 1
DOC_CHUNK_LENGTH    : 297
DOC_CHUNK_TEXT      : Headlines screamed "Gold!" The dream of
DOC_CHUNK_EMBEDDING : [4.77597751E-002,7.22050816E-002,3.07750

DOC_ROWID           : AAQVAeABUAAPJSpAA2
DOC_CHUNK_ID        : 1
DOC_CHUNK_COUNT     : 1
DOC_CHUNK_OFFSET    : 1
DOC_CHUNK_LENGTH    : 390
DOC_CHUNK_TEXT      : The ancient geologic landscape of the up
DOC_CHUNK_EMBEDDING : [-4.76894975E-002,9.31795128E-003,7.6873

DOC_ROWID           : AAQVAeABUAAPJSpAA4
DOC_CHUNK_ID        : 1
DOC_CHUNK_COUNT     : 1
DOC_CHUNK_OFFSET    : 1
DOC_CHUNK_LENGTH    : 382
DOC_CHUNK_TEXT      : Lava Beds National Monument is a land of
DOC_CHUNK_EMBEDDING : [4.78412099E-002,2.7518522E-002,6.380993

DOC_ROWID           : AAQVAeABUAAPJSpABD
DOC_CHUNK_ID        : 1
DOC_CHUNK_COUNT     : 1
DOC_CHUNK_OFFSET    : 1
DOC_CHUNK_LENGTH    : 344
DOC_CHUNK_TEXT      : The Delaware River, the largest free-flo
DOC_CHUNK_EMBEDDING : [5.27650416E-002,-3.61868404E-002,4.4181

DOC_ROWID           : AAQVAeABUAAPJSpABO
DOC_CHUNK_ID        : 1
DOC_CHUNK_COUNT     : 1
DOC_CHUNK_OFFSET    : 1
DOC_CHUNK_LENGTH    : 395
DOC_CHUNK_TEXT      : Welcome to Martin Luther King, Jr. Natio
DOC_CHUNK_EMBEDDING : [6.68741763E-002,1.09639429E-001,-1.0090

The view also contains DOC_CHUNK_EMBEDDING, which holds the generated vector. DOC_CHUNK_TEXT contains the text used for each chunk; the original column name, PARK_DESCRIPTION, is not a column in this generated view.

Start with semantic search

Consider the question: Where can I explore ancient fossils and prehistoric mammals?

We can search for descriptions similar in meaning to that question:

demo@ADB26ai> SELECT JSON_SERIALIZE(
  2      DBMS_HYBRID_VECTOR.SEARCH(
  3          JSON('{
  4              "hybrid_index_name": "PARK_DESCRIPTION_HVI",
  5              "vector": {
  6                  "search_text": "Where can I explore ancient fossils and prehistoric mammals?",
  7                  "search_mode": "DOCUMENT",
  8                  "aggregator": "MAX"
  9              },
 10              "return": {
 11                  "topN": 5,
 12                  "values": ["rowid", "score","vector_score"]
 13              }
 14          }')
 15      )
 16      RETURNING CLOB PRETTY
 17  ) final_output ;

FINAL_OUTPUT
--------------------------------------------------------------------------------
[
  {
    "rowid" : "AAQVAeABUAAPJS5AAy",
    "score" : 70.91,
    "vector_score" : 70.91
  },
  {
    "rowid" : "AAQVAeABUAAPJS5AAI",
    "score" : 70.47,
    "vector_score" : 70.47
  },
  {
    "rowid" : "AAQVAeABUAAPJSgABd",
    "score" : 69.25,
    "vector_score" : 69.25
  },
  {
    "rowid" : "AAQVAeABUAAPJSgABI",
    "score" : 69.08,
    "vector_score" : 69.08
  },
  {
    "rowid" : "AAQVAeABUAAPHl8AAF",
    "score" : 69.03,
    "vector_score" : 69.03
  }
]

Although the index supports hybrid search, this query supplies only a vector search specification. It therefore performs semantic search, without a keyword-search component.

DOCUMENT requests document-level results. With MAX, the highest matching chunk score is used to represent each document’s vector score, to see the actual data, we need to connect these rowids back to the table and pull the relevant columns.

demo@ADB26ai> with hits as (
  2      SELECT JSON_SERIALIZE(
  3      DBMS_HYBRID_VECTOR.SEARCH(
  4          JSON('{
  5              "hybrid_index_name": "PARK_DESCRIPTION_HVI",
  6              "vector": {
  7                  "search_text": "Where can I explore ancient fossils and prehistoric mammals?",
  8                  "search_mode": "DOCUMENT",
  9                  "aggregator": "MAX"
 10              },
 11              "return": {
 12                  "topN": 5,
 13                  "values": ["rowid", "score","vector_score"]
 14              }
 15          }')
 16      )
 17      RETURNING CLOB PRETTY
 18  ) final_output
 19  )
 20  select p.park_description
 21  from parks_info p , hits, json_table ( hits.final_output , '$[*]'
 22          columns( rid varchar2(80) path '$.rowid' , score number, vector_score) ) jt
 23  where p.rowid = jt.rid ;

PARK_DESCRIPTION
-----------------------------------------------------------------------------------

In the early 1900s, paleontologists unearthed the Age of Mammals when they found full skeletons of extinct Miocene mammals in the hills of Nebraska -- species previously only known through fragments.
At the same time, an age of friendship began between rancher James Cook and Chief Red Cloud of the Lakota. These two unprecedented events are preserved and protected here... at Agate Fossil Beds.

Beneath a grassy mountain valley in central Colorado lies one of the richest and most diverse fossil deposits in the world. Petrified redwood stumps up to 14 feet wide and thousands of detailed fossil
s of insects and plants reveal the story of a very different, prehistoric Colorado.

150 million years ago, dinosaurs roamed here. Their fossils are still embedded in the rocks. Today, mountains, desert, and rivers flowing in canyons support a variety of life. Petroglyphs reveal the l
ives and connections of Indigenous people to this land. Homesteaders and outlaws found refuge here. Whether your passion is science, adventure, history, or scenery, Dinosaur offers much to explore.

During the Pliocene, this place looked quite different. Lush wetlands, forests, and grasslands provided excellent habitat for a variety of animals. From fascinating, now-extinct creatures like the sab
er-toothed cat, mastodon, and ground sloth, to more familiar animals like horses, beavers, and birds, the scientific study of Pliocene fossils is the key to Hagerman.

In the ridges of southwest Wyoming are some of the best-preserved fossils in the world. They tell the story of ancient life in a warm, wet environment in and around a freshwater lake. Stingrays swam i
n the lake. Turtles basked in the sun. Leaves rustled in the breeze while early horses darted between the trunks. We reveal more of the past with each fossil found.

In my original sample run, the returned descriptions discussed fossil beds, dinosaurs, prehistoric plants, ancient lakes, and extinct mammals. These examples illustrate semantic retrieval, but do not establish that hybrid search improves relevance.

Combine keyword and semantic search

To demonstrate hybrid search explicitly, we can add an Oracle Text expression alongside the semantic question:

demo@ADB26ai> WITH hits AS (
  2      SELECT DBMS_HYBRID_VECTOR.SEARCH(
  3                 JSON('{
  4                     "hybrid_index_name": "PARK_DESCRIPTION_HVI",
  5                     "search_fusion": "UNION",
  6                     "search_scorer": "RRF",
  7                     "vector": {
  8                         "search_text":
  9                             "Where can I explore ancient fossils and prehistoric mammals?",
 10                         "search_mode": "DOCUMENT",
 11                         "aggregator": "MAX"
 12                     },
 13                     "text": {
 14                         "contains": "fossils OR prehistoric"
 15                     },
 16                     "return": {
 17                         "topN": 5,
 18                         "values": [
 19                             "rowid",
 20                             "score",
 21                             "vector_score",
 22                             "text_score"
 23                         ]
 24                     }
 25                 }')
 26             ) AS search_results
 27      FROM dual
 28  )
 29  SELECT p.park_fullname,
 30         p.park_states,
 31         jt.score,
 32         jt.vector_score,
 33         jt.text_score,
 34         p.park_description
 35  FROM hits h,
 36       JSON_TABLE(
 37           h.search_results,
 38           '$[*]'
 39           COLUMNS (
 40               result_position FOR ORDINALITY,
 41               document_rowid VARCHAR2(80) PATH '$.rowid',
 42               score          NUMBER       PATH '$.score',
 43               vector_score   NUMBER       PATH '$.vector_score',
 44               text_score     NUMBER       PATH '$.text_score'
 45           )
 46       ) jt,
 47       parks_info p
 48  WHERE p.ROWID = CHARTOROWID(jt.document_rowid)
 49  ORDER BY jt.result_position;

The output of the above query is turned into a single row view for easier understanding and readability

PARK_FULLNAME    : Hagerman Fossil Beds National Monument
PARK_STATES      : ID
SCORE            : 100
VECTOR_SCORE     : 70.91
TEXT_SCORE       : 9
PARK_DESCRIPTION : During the Pliocene, this place looked quite different. Lush wetlands, forests, and grasslands provided excellent habitat for a variety of animals. From fascinating, now-extinct creatures like the saber-toothed cat, mastodon, and ground sloth, to more familiar animals like horses, beavers, and birds, the scientific study of Pliocene fossils is the key to Hagerman.

PARK_FULLNAME    : Fossil Butte National Monument
PARK_STATES      : WY
SCORE            : 75
VECTOR_SCORE     : 70.47
TEXT_SCORE       : 9
PARK_DESCRIPTION : In the ridges of southwest Wyoming are some of the best-preserved fossils in the world. They tell the story of ancient life in a warm, wet environment in and around a freshwater lake. Stingrays swam in the lake. Turtles basked in the sun. Leaves rustled in the breeze while early horses darted between the trunks. We reveal more of the past with each fossil found.

PARK_FULLNAME    : Florissant Fossil Beds National Monument
PARK_STATES      : CO
SCORE            : 62.5
VECTOR_SCORE     : 69.25
TEXT_SCORE       : 9
PARK_DESCRIPTION : Beneath a grassy mountain valley in central Colorado lies one of the richest and most diverse fossil deposits in the world. Petrified redwood stumps up to 14 feet wide and thousands of detailed fossils of insects and plants reveal the story of a very different, prehistoric Colorado.

PARK_FULLNAME    : Dinosaur National Monument
PARK_STATES      : CO,UT
SCORE            : 55
VECTOR_SCORE     : 69.08
TEXT_SCORE       : 9
PARK_DESCRIPTION : 150 million years ago, dinosaurs roamed here. Their fossils are still embedded in the rocks. Today, mountains, desert, and rivers flowing in canyons support a variety of life. Petroglyphs reveal the lives and connections of Indigenous people to this land. Homesteaders and outlaws found refuge here. Whether your passion is science, adventure, history, or scenery, Dinosaur offers much to explore.

PARK_FULLNAME    : Hovenweep National Monument
PARK_STATES      : CO,UT
SCORE            : 41.67
VECTOR_SCORE     : 64.04
TEXT_SCORE       : 9
PARK_DESCRIPTION : Hovenweep preserves six prehistoric sites built between 1200 and 1300 CE which may have served as ceremonial centers. Explore a variety of structures, including multistory towers perched on canyon rims and balanced on boulders. The construction and attention to detail will leave you marveling at the skill and motivation of the builders.

This query combines a semantic question with a keyword expression, then maps the returned row identifiers to the original park records. (I’ve highlighted the occurrences of the key word matched search)

UNION combines candidates from either search, while RRF uses reciprocal rank fusion to combine their rankings.

Semantic Search in Oracle AI Database - Part III

This is the third post in our series on Oracle AI Vector Search and builds on the examples and data used in the two previous posts. The first was getting started with AI Vector search, the second was using HNSW vector index. In those posts we explored how AI Vector search allows you to find the similar content based on semantic representation or meaning rather than the unstructured data raw values
 
In the first post, we performed exhaustive similarity searches, comparing every item in the dataset to find the closest matches. In contrast, the second post used an In-Memory Neighbor Graph (HNSW) vector index to perform approximate similarity searches, which we found to be much faster than an exhaustive similarity search. Although very fast, an HNSW vector index is an in-memory only index and can require a lot of memory for large datasets. In practice, too much memory for some very large datasets.
 
In this post, we will explore how to use an Inverted File Flat (IVF) partition-based index, which is a type of Neighbor Partition vector index. This is a storage-based index as opposed to the HNSW vector index, which is an in-memory index. The IVF vector index also uses a different search technique than an HNSW index. It is designed to enhance search efficiency by narrowing the search area through the use of neighbor partitions or clusters.
 
The dataset size determines the number of partitions in the index. The center of each partition, or the centroid, represents the average vector for each partition. It is also called the center of gravity of the partition. By default, the number of partitions and their corresponding centroids is set to the square root of the dataset’s size. However, this can be adjusted by specifying the NEIGHBOR PARTITIONS parameter during index creation. The centroids conceptually partition the vector space.
 
For a query vector, the search algorithm identifies the number of nearest centroids, where the default number is the square root of the total number of centroids. This can be adjusted by setting the NEIGHBOR PARTITION PROBES parameter. The higher the number of nearest centroids, the more accurate the search, but that accuracy comes at a cost. The more centroids selected, the more work that needs to be performed. Once the number of partitions is determined, they are thoroughly scanned for the top nearest vectors to the vector specified by the query.
 
More information about how IVF indexes work are available in the docs.
 
To create IVF indexes on CASE_EMBEDDINGS vector column in the table VECTOR_SEARCH_DEMO ,the following statement can be used.

 

demo@ADB26AI> create vector index ivf_demo
  2  on VECTOR_SEARCH_DEMO( CASE_EMBEDDINGS )
  3  organization neighbor partitions
  4* distance cosine ; 

Vector INDEX created.

 

  

This statement will create a global partitioned IVF vector index and will result in the creation of two internal tables:
 
demo@ADB26AI> select table_name
  2  from user_tables
  3* where table_name like 'VECTOR$IVF_DEMO%';
 
TABLE_NAME
_________________________________________________________________
VECTOR$IVF_DEMO$4040231_4231331_0$IVF_FLAT_CENTROIDS
VECTOR$IVF_DEMO$4040231_4231331_0$IVF_FLAT_CENTROID_PARTITIONS
 
These tables are of the format:
 

• VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROIDS, containing the list of identified centroid vectors and associated ids.

• VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROID_PARTITIONS, which is list-partitioned on the centroid ids. Each partition contains the base table vectors closely related (cluster) to the corresponding centroid id for that partition.

 
IVF indexes do need some memory, either from the vector pool if it has been defined or the shared pool, to speed up index creation and during DML operations on the base table. Let’s query the Vector Pool and see how much memory our new index uses.

Details of the pool can be displayed by querying the view V$VECTOR_MEMORY_POOL:
 
demo@ADB26AI> select * from v$vector_memory_pool;
 
POOL            ALLOC_BYTES    USED_BYTES POPULATE_STATUS       CON_ID
____________ ______________ _____________ __________________ _________
1MB POOL           70479593      76546048 OUT OF MEMORY             37
64KB POOL          17619898       1310720 DONE                      37
 
This is far less than our HNSW vector index used in the Using HNSW Vector Indexes post since the index itself is not stored in memory. In fact, if we look at the memory usage of the index as we did in the previous post using the view V$VECTOR_INDEX, we see the following:
 
demo@ADB26AI> select index_name, index_organization, allocated_bytes
  2    , used_bytes, num_vectors, index_dimensions
  3  from v$vector_index
  4* where index_name ='IVF_DEMO';
 
INDEX_NAME    INDEX_ORGANIZATION        ALLOCATED_BYTES    USED_BYTES    NUM_VECTORS    INDEX_DIMENSIONS
_____________ ______________________ __________________ _____________ ______________ ___________________
IVF_DEMO      NEIGHBOR PARTITIONS               2097152         80768        1427029                 384
 
Now we can run the same query that was used in the Using HNSW Vector Indexes post with the APPROX keyword and a target accuracy that matches the index we created:
 
demo@ADB26AI> select case_primary_type , case_description , vd
  2     from (
  3     select case_primary_type
  4     , case_description
  5     , vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using 'LARCENY' as data ) , cosine ) vd
  6     from vector_search_demo t
  7     order by vd
  8     fetch approx first 10 rows only with target accuracy 90 )
  9  group by case_primary_type , case_description ,vd
 10  order by vd
 11* fetch first 15 rows only;
 
CASE_PRIMARY_TYPE    CASE_DESCRIPTION                  VD
____________________ _________________________________ ______________________
OTHER OFFENSE        OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
 
Note that this is an approximate similarity search because we added the APPROX keyword and will use the IVF index we just created. Below is the execution plan:
 
demo@ADB26AI> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID  2p9222n5fnzg7, child number 1
-------------------------------------
select case_primary_type , case_description , vd    from (    select
case_primary_type    , case_description    ,
vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using
'LARCENY' as data ) , cosine ) vd    from vector_search_demo t    order
by vd    fetch approx first 10 rows only with target accuracy 90 )
group by case_primary_type , case_description ,vd order by vd fetch
first 15 rows only
 
Plan hash value: 1724078458
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                                                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                                                                |      1 |        |      1 |00:00:00.32 |    4977 |      3 |
|   1 |  RESULT CACHE                              | d2jdkwfk05fmrfw35q2m0zwf4c                                     |      1 |        |      1 |00:00:00.32 |    4977 |      3 |
|*  2 |   COUNT STOPKEY                            |                                                                |      1 |        |      1 |00:00:00.32 |    4977 |      3 |
|   3 |    VIEW                                    |                                                                |      1 |     10 |      1 |00:00:00.32 |    4977 |      3 |
|*  4 |     SORT GROUP BY STOPKEY                  |                                                                |      1 |     10 |      1 |00:00:00.32 |    4977 |      3 |
|   5 |      VIEW                                  |                                                                |      1 |     10 |     10 |00:00:00.32 |    4977 |      3 |
|   6 |       VIEW                                 |                                                                |      1 |     10 |     10 |00:00:00.32 |    4977 |      3 |
|   7 |        NESTED LOOPS                        |                                                                |      1 |     10 |     10 |00:00:00.32 |    4977 |      3 |
|   8 |         VIEW                               | VW_IVPSR_F17F1C9B                                              |      1 |     10 |     10 |00:00:00.32 |    4962 |      3 |
|*  9 |          COUNT STOPKEY                     |                                                                |      1 |        |     10 |00:00:00.32 |    4962 |      3 |
|  10 |           VIEW                             | VW_IVPSJ_578B79F1                                              |      1 |    169K|     10 |00:00:00.32 |    4962 |      3 |
|* 11 |            SORT ORDER BY STOPKEY           |                                                                |      1 |    169K|     10 |00:00:00.32 |    4962 |      3 |
|* 12 |             HASH JOIN                      |                                                                |      1 |    169K|   1603 |00:00:00.32 |    4962 |      3 |
|  13 |              PART JOIN FILTER CREATE       | :BF0000                                                        |      1 |      7 |      7 |00:00:00.22 |      22 |      0 |
|  14 |               VIEW                         | VW_IVCR_B5B87E67                                               |      1 |      7 |      7 |00:00:00.22 |      22 |      0 |
|* 15 |                COUNT STOPKEY               |                                                                |      1 |        |      7 |00:00:00.22 |      22 |      0 |
|  16 |                 VIEW                       | VW_IVCN_9A1D2119                                               |      1 |     59 |      7 |00:00:00.22 |      22 |      0 |
|* 17 |                  SORT ORDER BY STOPKEY     |                                                                |      1 |     59 |      7 |00:00:00.22 |      22 |      0 |
|  18 |                   TABLE ACCESS STORAGE FULL| VECTOR$IVF_DEMO$4040231_4271572_0$IVF_FLAT_CENTROIDS           |      1 |     59 |     59 |00:00:00.01 |      22 |      0 |
|  19 |              PARTITION LIST JOIN-FILTER    |                                                                |      1 |   1427K|   1603 |00:00:00.10 |    4940 |      3 |
|  20 |               TABLE ACCESS STORAGE FULL    | VECTOR$IVF_DEMO$4040231_4271572_0$IVF_FLAT_CENTROID_PARTITIONS |      7 |   1427K|   1603 |00:00:00.10 |    4940 |      3 |
|  21 |         TABLE ACCESS BY USER ROWID         | VECTOR_SEARCH_DEMO                                             |     10 |      1 |     10 |00:00:00.01 |      15 |      0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=15)
   4 - filter(ROWNUM<=15)
   9 - filter(ROWNUM<=10)
  11 - filter(ROWNUM<=10)
  12 - access("VW_IVCR_B5B87E67"."CENTROID_ID"="VTIX_CNPART"."CENTROID_ID")
  15 - filter(ROWNUM<=7)
  17 - filter(ROWNUM<=7)
 
You may notice that we do not see a VECTOR INDEX IVF SCAN access path. In the Using HNSW Vector Indexes post, we saw a VECTOR INDEX HNSW SCAN access path. With an IVF index, we see TABLE ACCESS FULL scans of the two internal tables that were created as part of the IVF index creation. See the description of the internal tables in the IVF Vector Index Creation section above.

Semantic Search in Oracle AI Database - Part II

In the previous blog post we explored how AI vector search allows you to find similar content based on semantic representation or meaning rather than the actual (raw) values. In that examples we performed exhaustive similarity searches, comparing each item in the dataset to find the closest matches, while this approach helps guarantee accuracy, it doesn’t scale well. (think of searching thorough millions or billions of vectors one by one!)
 
This is where the vector index comes in, instead of checking every possible matches, an approximate similarity search uses a class of algorithms referred to as Approximate nearest neighbour. Using vector indexes helps to reduce the number of distance calculations, making searches faster and more efficient in accuracy
 
The example we used from the previous blog post performed exhaustive similarity searches, An exhaustive search to find the closest match for a given query vector are accurate, but can be slow , since the vector distance computation are needed for all vectors in that column.
 
demo@ADB26AI> select case_primary_type , case_description , vd
  2      from (
  3      select case_primary_type
  4      , case_description
  5      , vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using 'LARCENY' as data ) , cosine ) vd
  6      from vector_search_demo t
  7      order by vd
  8      fetch exact first 500 rows only)
  9  group by case_primary_type , case_description ,vd
 10  order by vd
 11  fetch first 15 rows only;
 
CASE_PRIMARY_TYPE         CASE_DESCRIPTION                                      VD
------------------------- --------------------------------------------- ----------
OTHER OFFENSE             OTHER CRIME INVOLVING PROPERTY                3.845E-001
 
And the execution plan is the following
 
demo@ADB26AI> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID  53rn6381kurk1, child number 0
-------------------------------------
select case_primary_type , case_description , vd     from (     select
case_primary_type     , case_description     ,
vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using
'LARCENY' as data ) , cosine ) vd     from vector_search_demo t
order by vd     fetch exact first 500 rows only) group by
case_primary_type , case_description ,vd order by vd fetch first 15
rows only
 
Plan hash value: 1825924980
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |      1 |        |      1 |00:00:00.78 |   27455 |  27453 |
|   1 |  RESULT CACHE                     | 6msm9kz3v36qg9v5yj4|      1 |        |      1 |00:00:00.78 |   27455 |  27453 |
|*  2 |   COUNT STOPKEY                   |                    |      1 |        |      1 |00:00:00.78 |   27455 |  27453 |
|   3 |    VIEW                           |                    |      1 |    500 |      1 |00:00:00.78 |   27455 |  27453 |
|*  4 |     SORT GROUP BY STOPKEY         |                    |      1 |    500 |      1 |00:00:00.78 |   27455 |  27453 |
|   5 |      VIEW                         |                    |      1 |    500 |    500 |00:00:00.78 |   27455 |  27453 |
|*  6 |       COUNT STOPKEY               |                    |      1 |        |    500 |00:00:00.78 |   27455 |  27453 |
|   7 |        VIEW                       |                    |      1 |   1427K|    500 |00:00:00.78 |   27455 |  27453 |
|*  8 |         SORT ORDER BY STOPKEY     |                    |      1 |   1427K|    500 |00:00:00.78 |   27455 |  27453 |
|   9 |          TABLE ACCESS STORAGE FULL| VECTOR_SEARCH_DEMO |      1 |   1427K|   1427K|00:00:00.43 |   27455 |  27453 |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=15)
   4 - filter(ROWNUM<=15)
   6 - filter(ROWNUM<=500)
   8 - filter(ROWNUM<=500)
 
 
Since we have no indexes defined, we see a full table scan access to the target table (id : 9  - TABLE ACCESS STORAGE FULL)
 
There are currently three types of vector indexes available in AI Vector search, and one among them is in-memory neighbour graph vector index also called as in-memory Hierarchical Navigable small world (HNSW). It is a type of in-memory Neighbour graph vector index , where the vertices represent vectors and the edges between the vertices represent similarity. This is an in-memory only index, this type of index is highly efficient for both accuracy and speed. 

 When HNSW indexes are used, we must need to enable a new memory area in the database called the vector pool , the vector pool is a memory allocated from System global area (SGA) to store HNSW type vector indexes and their associated metadata.
 
There is a new database init parameter called vector_memory_size that specifies the size of the vector pool and enabled HNSW vector index creation.
 
demo@ADB26AI> show parameter vector_memory_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
vector_memory_size                   big integer 83383634
 
Details of the vector pool can be found by querying the V$VECTOR_MEMORY_POOL
 
demo@ADB26AI> select *
  2  from v$vector_memory_pool;
 
POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      58368543   62914560 OUT OF MEMORY                      37
64KB POOL                     22930499    2359296 DONE                               37
 
The 64KB pool is mainly for index metadata, where as the 1MB pool is mainly for the vector contents.
 
How do we know how big to make the Vector pool for the HNSW index creation ? this is where we have DBMS_VECTOR package with a method called INDEX_VECTOR_MEMORY_ADVISOR that can help to evaluate the number of indexes that can fit for a simulated vector memory size.
 
demo@ADB26AI> variable r clob
demo@ADB26AI> begin
  2     dbms_vector.index_vector_memory_advisor(
  3             table_owner => user
  4             , table_name => 'VECTOR_SEARCH_DEMO'
  5             , column_name => 'CASE_EMBEDDINGS'
  6             , index_type => 'HNSW'
  7             , response_json => :r );
  8  end;
  9  /
Using default accuracy: 90%
Suggested vector memory pool size: 3 GB
 
This suggests that we will need approximately 3GB of memory for this index in our vector memory pool.  Since this demo is from ADBS instance, vector memory pool is limited to 70% of the SGA size (that is upto 9GB of memory can be used by vector memory pool )
 
demo@ADB26AI> show parameter sga_target
 
NAME           TYPE        VALUE
-------------- ----------- --------
sga_target     big integer 13600M
 
To create a vector index on the CASE_EMBEDDINGS column of the VECTOR_SEARCH_DEMO table, we use this following DDL
 
demo@ADB26AI> create vector index HNSW_demo
  2  on VECTOR_SEARCH_DEMO( CASE_EMBEDDINGS )
  3  organization inmemory neighbor graph
  4  distance cosine
  5  with target accuracy 90;
 
Index created.
 
This statement will create a vector index of HNSW type with a target accuracy of 90%
 
We can verify the memory usage of vector index with the view v$vector_index
 
demo@ADB26AI> select index_name, index_organization, allocated_bytes
  2    , used_bytes, num_vectors, index_dimensions
  3  from v$vector_index
  4  where owner = user;
 
INDEX_NAME      INDEX_ORGANIZATION        ALLOCATED_BYTES USED_BYTES NUM_VECTORS INDEX_DIMENSIONS
--------------- ------------------------- --------------- ---------- ----------- ----------------
HNSW_DEMO       INMEMORY NEIGHBOR GRAPH        2619080704 2613867800     1427029              384
 
After this index is created we can query the v$vector_memory_pool to see the memory utilization from vector pool.
 
demo@ADB26AI> select *
  2  from v$vector_memory_pool;
 
POOL         ALLOC_BYTES USED_BYTES POPULATE_STATUS     CON_ID
------------ ----------- ---------- --------------- ----------
1MB POOL      2945554763 2658140160 DONE                    37
64KB POOL      447608812   26738688 DONE                    37
 
Note that we are a bit off in actual used space from the estimate we got from the INDEX_VECTOR_MEMORY_ADVISOR procedure in the previous section (that is, 3GB versus 2658 MB).
 
Since HNSW indexes are created in-memory, when a database instance is restarted the index is no longer in-memory and must be re-built. By default, a reload mechanism is triggered at instance restart. This is controlled by the initialization parameter VECTOR_INDEX_NEIGHBOR_GRAPH_RELOAD and defaults to RESTART. To facilitate faster reloads after an instance restart a full checkpoint on-disk structure is also maintained.
 
demo@ORA26AI> show parameter vector_index
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
vector_index_neighbor_graph_reload   string      RESTART
 
 
Now we can run the same query that was used in the exact similarity search section previously, but with the APPROX keyword to enable an approximate similarity search and a target accuracy that matches the index we created:
 
demo@ADB26AI> select case_primary_type , case_description , vd
  2      from (
  3      select case_primary_type
  4      , case_description
  5      , vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using 'LARCENY' as data ) , cosine ) vd
  6      from vector_search_demo t
  7      order by vd
  8      fetch approx first 500 rows only with target accuracy 90 )
  9  group by case_primary_type , case_description ,vd
 10  order by vd
 11  fetch first 15 rows only;
 
CASE_PRIMARY_TYPE         CASE_DESCRIPTION                                      VD
------------------------- --------------------------------------------- ----------
OTHER OFFENSE             OTHER CRIME INVOLVING PROPERTY                3.845E-001
OTHER OFFENSE             OTHER CRIME AGAINST PERSON                    3.949E-001
 
Note that since we added the APPROX keyword, we can use the HNSW index we created previously. We can verify this by displaying the execution plan:
 
demo@ADB26AI> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  amk960c8v78xa, child number 0
-------------------------------------
select case_primary_type , case_description , vd     from (     select
case_primary_type     , case_description     ,
vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using
'LARCENY' as data ) , cosine ) vd     from vector_search_demo t
order by vd     fetch approx first 500 rows only with target accuracy
90 ) group by case_primary_type , case_description ,vd order by vd
fetch first 15 rows only
 
Plan hash value: 3088503952
 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |      1 |        |      2 |00:00:00.01 |    1260 |      7 |
|   1 |  RESULT CACHE                       | 6xt3798pj66dtdndjw |      1 |        |      2 |00:00:00.01 |    1260 |      7 |
|*  2 |   COUNT STOPKEY                     |                    |      1 |        |      2 |00:00:00.01 |    1260 |      7 |
|   3 |    VIEW                             |                    |      1 |    500 |      2 |00:00:00.01 |    1260 |      7 |
|*  4 |     SORT GROUP BY STOPKEY           |                    |      1 |    500 |      2 |00:00:00.01 |    1260 |      7 |
|   5 |      VIEW                           |                    |      1 |    500 |    500 |00:00:00.01 |    1260 |      7 |
|*  6 |       COUNT STOPKEY                 |                    |      1 |        |    500 |00:00:00.01 |    1260 |      7 |
|   7 |        VIEW                         |                    |      1 |    500 |    500 |00:00:00.01 |    1260 |      7 |
|*  8 |         SORT ORDER BY STOPKEY       |                    |      1 |    500 |    500 |00:00:00.01 |    1260 |      7 |
|   9 |          TABLE ACCESS BY INDEX ROWID| VECTOR_SEARCH_DEMO |      1 |    500 |    500 |00:00:00.01 |    1260 |      7 |
|  10 |           VECTOR INDEX HNSW SCAN    | HNSW_DEMO          |      1 |    500 |    500 |00:00:00.01 |       2 |      0 |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=15)
   4 - filter(ROWNUM<=15)
   6 - filter(ROWNUM<=500)
   8 - filter(ROWNUM<=500)
 
 
You can see the use of the vector index that was created in the query execution plan at id 10. Note that Oracle AI Vector Search is fully integrated into Oracle Database 23ai, so SQL execution plans will show the use of vector indexes. Also note that the time and the number of buffers accessed is much less than compared to the previous exhaustive search execution
 
It is important however, to recognize that using a vector index search is approximate as opposed to exhaustive. This means that the results can be different than with an exhaustive search. And depending on the data it can be quite different.