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
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.0090The 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.
No comments:
Post a Comment