In my earlier post, I explored hybrid vector search in Oracle AI Database.
For this post, let us work through a practical example using park descriptions. We will search the same data in different ways and examine how the results change.
Consider someone looking for a park where they can learn about prehistoric mammals. They might search for fossils, or ask a question such as:
Where can I explore ancient fossils and prehistoric mammals?
These searches express a related interest, but they give the search engine different information. The keyword expression specifies words to match. The question provides text that can be embedded and compared with the meaning of the park descriptions.
Using the execution below, we will explore both approaches and then combine them.
Prepare the hybrid vector index
For this demonstration, we will use the park data from the previous blogpost, The resulting table includes the park name, code, description, location, directions, and weather information. Our searches will use the PARK_DESCRIPTION column.
With the data available, creating a hybrid vector index can be as simple as:
demo@ADB26ai> create hybrid vector index park_description_hvi
2 on parks_info( park_description )
3 parameters( 'MODEL MY_DEMO_MODEL');
Index created.Here, MY_DEMO_MODEL is an embedding model already available in my database.
For the remaining examples, I recreated the index using an explicit vectorizer preference. This gives us visibility into the chunking and vector-index settings used for the demonstration.
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.The preference specifies word-based chunking with a maximum chunk size of 100 words, no overlap, and recursive splitting. It also selects an IVF vector index with cosine distance and a target search accuracy of 90. This accuracy setting is a vector-search target, rather than a measure of how relevant the answers are to a user. Oracle vectorizer preference documentation
I then recreated the index using this preference:
demo@ADB26ai> create hybrid vector index park_description_hvi
2 on parks_info( park_description )
3 parameters( 'VECTORIZER MY_HYBRID_INDEX_PREF');
Index created.Inspect the generated vector data
Before searching, let us inspect a few rows from the generated vector data:
demo@ADB26ai> select *
2 from park_description_hvi$VECTORS
3 fetch first 5 rows only;
DOC_ROWID DOC_CHUNK_ID DOC_CHUNK_COUNT DOC_CHUNK_OFFSET DOC_CHUNK_LENGTH DOC_CHUNK_TEXT DOC_CHUNK_EMBEDDING
------------------ ------------ --------------- ---------------- ---------------- ---------------------------------------- ----------------------------------------
AAQVAeABUAAPJSpAAu 1 1 1 297 Headlines screamed "Gold!" The dream of [4.77597751E-002,7.22050816E-002,3.07750
AAQVAeABUAAPJSpAA2 1 1 1 390 The ancient geologic landscape of the up [-4.76894975E-002,9.31795128E-003,7.6873
AAQVAeABUAAPJSpAA4 1 1 1 382 Lava Beds National Monument is a land of [4.78412099E-002,2.7518522E-002,6.380993
AAQVAeABUAAPJSpABD 1 1 1 344 The Delaware River, the largest free-flo [5.27650416E-002,-3.61868404E-002,4.4181
AAQVAeABUAAPJSpABO 1 1 1 395 Welcome to Martin Luther King, Jr. Natio [6.68741763E-002,1.09639429E-001,-1.0090This makes the relationship between the source description, its text chunk, and its embedding visible.
In the five sampled rows, DOC_CHUNK_COUNT is 1. The descriptions shown fit into a single chunk with the selected settings. This observation applies to the sampled rows; it does not establish that every park description has only one chunk.
Now let us search the index.
Starting with semantic search in document mode
Our first query asks:
Where can I explore ancient fossils and prehistoric mammals?
We supply the question inside the vector section and omit the text section.
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
}
]Because this is a vector-only search, the final score is the vector score.
DOCUMENT mode returns park-level results. When retrieved chunks belong to the same document, MAX uses the highest retrieved chunk score as that document’s vector score. Oracle search parameter documentation
The returned descriptions discuss Pliocene mammals, fossil deposits, prehistoric plants, dinosaurs, and Miocene mammals. They relate to the subject of the question without needing to contain its complete wording.
The scores are relevance values used for ranking; a score of 70.91 should not be interpreted as a 70.91% probability that the result is correct.
Connecting the results to the original park data
The search output contains the ROWID of the source row. We can use it to retrieve the original description and other park attributes.
My execution log uses JSON_TABLE to extract those row identifiers and join them back to PARKS_INFO.
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.Returning the matching passages with chunk mode
Sometimes we want the relevant passage itself, rather than only a reference to the source document.
For the same question, we can switch to CHUNK mode and request the chunk text:
demo@ADB26ai> SELECT JSON_SERIALIZE(
2 DBMS_HYBRID_VECTOR.SEARCH(
3 JSON('{
4 "hybrid_index_name": "PARK_DESCRIPTION_HVI",
5 "vector": {
6 "search_text":
7 "Where can I explore ancient fossils and prehistoric mammals?",
8 "search_mode": "CHUNK"
9 },
10 "return": {
11 "values": [
12 "rowid",
13 "chunk_id",
14 "chunk_text",
15 "score",
16 "vector_score"
17 ],
18 "topN": 5
19 }
20 }')
21 )
22 RETURNING CLOB PRETTY
23 ) AS search_results ;
SEARCH_RESULTS
--------------------------------------------------------------------------------
[
{
"rowid" : "AAQVAeABUAAPJS5AAy",
"chunk_id" : "1",
"chunk_text" : "During the Pliocene, this place looked quite different. Lush
wetlands, forests, and grasslands provided excellent habitat for a variety of a
nimals. From fascinating, now-extinct creatures like the saber-toothed cat, mast
odon, and ground sloth, to more familiar animals like horses, beavers, and birds
, the scientific study of Pliocene fossils is the key to Hagerman.",
"score" : 70.91,
"vector_score" : 70.91
},
{
"rowid" : "AAQVAeABUAAPJS5AAI",
"chunk_id" : "1",
"chunk_text" : "In the ridges of southwest Wyoming are some of the best-pres
erved fossils in the world. They tell the story of ancient life in a warm, wet e
nvironment 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 betwee
n the trunks. We reveal more of the past with each fossil found.",
"score" : 70.47,
"vector_score" : 70.47
},
{
"rowid" : "AAQVAeABUAAPJSgABd",
"chunk_id" : "1",
"chunk_text" : "Beneath a grassy mountain valley in central Colorado lies on
e of the richest and most diverse fossil deposits in the world. Petrified redwoo
d stumps up to 14 feet wide and thousands of detailed fossils of insects and pla
nts reveal the story of a very different, prehistoric Colorado.",
"score" : 69.25,
"vector_score" : 69.25
},
{
"rowid" : "AAQVAeABUAAPJSgABI",
"chunk_id" : "1",
"chunk_text" : "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.",
"score" : 69.08,
"vector_score" : 69.08
},
{
"rowid" : "AAQVAeABUAAPHl8AAF",
"chunk_id" : "1",
"chunk_text" : "In the early 1900s, paleontologists unearthed the Age of Mam
mals when they found full skeletons of extinct Miocene mammals in the hills of N
ebraska -- 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 L
akota. These two unprecedented events are preserved and protected here... at Aga
te Fossil Beds.",
"score" : 69.03,
"vector_score" : 69.03
}
]This result gives us the passage that explains the match: it directly discusses extinct mammals and the study of Pliocene fossils.
For this execution, the document and chunk searches returned the same five row identifiers with the same scores. The returned chunks all had chunk_id = 1.
That similarity should not be generalized to larger documents. A document can contain several relevant chunks. Document mode groups retrieved chunks by their source document; chunk mode can return multiple passages from the same document. Oracle search modes
Searching with keywords
Next, let us search using an Oracle Text expression: fossil OR fossils
This time, we supply a text section and omit the vector section.
demo@ADB26ai> SELECT JSON_SERIALIZE(
2 DBMS_HYBRID_VECTOR.SEARCH(
3 JSON('{
4 "hybrid_index_name": "PARK_DESCRIPTION_HVI",
5 "text": {
6 "contains": "fossil OR fossils"
7 },
8 "return": {
9 "values": ["rowid", "score", "text_score"],
10 "topN": 5
11 }
12 }')
13 )
14 RETURNING CLOB PRETTY
15 ) AS search_results ;
SEARCH_RESULTS
--------------------------------------------------------------------------------
[
{
"rowid" : "AAQVAeABUAAPJSpACM",
"score" : 9,
"text_score" : 9
},
{
"rowid" : "AAQVAeABUAAPJSgABI",
"score" : 9,
"text_score" : 9
},
{
"rowid" : "AAQVAeABUAAPJS5AAy",
"score" : 9,
"text_score" : 9
},
{
"rowid" : "AAQVAeABUAAPJS5AAI",
"score" : 9,
"text_score" : 9
},
{
"rowid" : "AAQVAeABUAAPJSgABd",
"score" : 9,
"text_score" : 9
}
]demo@ADB26ai>
demo@ADB26ai> with hits as (
2 SELECT JSON_SERIALIZE(
3 DBMS_HYBRID_VECTOR.SEARCH(
4 JSON('{
5 "hybrid_index_name": "PARK_DESCRIPTION_HVI",
6 "text": {
7 "contains": "fossil OR fossils"
8 },
9 "return": {
10 "values": ["rowid", "score", "text_score"],
11 "topN": 5
12 }
13 }')
14 )
15 RETURNING CLOB PRETTY
16 ) AS final_output
17 )
18 select p.park_description
19 from parks_info p , hits, json_table ( hits.final_output , '$[*]'
20 columns( rid varchar2(80) path '$.rowid' , score number, vector_score) ) jt
21 where p.rowid = jt.rid ;
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 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.
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.
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.
A landscape where deep time lies fully on display, Petrified Forest National Park blends colorful badlands, vast grasslands, and one of the worldÆs largest and most vivid collections of petrified wood
. Spanning more than 200 million years of geologic history, the park protects ancient fossils, ancestral Puebloan sites, habitat for desert animals, and a sweeping stretch of historic Route 66.The returned descriptions included Hagerman, Dinosaur, and Petrified Forest, along with descriptions of fossil deposits in Wyoming and Colorado.
The distinction is in the request we made. The semantic query asked about a subject. This request asks Oracle Text to evaluate a keyword expression against the indexed descriptions. Both requests use PARK_DESCRIPTION_HVI, but they provide different relevance signals
| Request | Returned information |
|---|---|
| Semantic DOCUMENT | Source documents ranked using retrieved chunk scores |
| Semantic CHUNK | Individual passages with their source row identifiers |
| Keyword search | Source documents matching the Oracle Text expression |
