A customer asking how many custom roles an Enterprise account can have needs one sentence from page two of an administrator guide. Once documents are split into embedded chunks, finding that sentence is a similarity search over the chunks. The harder questions are how big the chunks should be, and how to keep the search current as documents change.
This guide searches chunked PDF, Word, and HTML documents in Oracle AI Database 26ai, measures five chunk sizes on questions with known answers, adds documents with one function, and lets a hybrid vector index do the whole job instead.
Code for This Guide
The examples are files 07 to 13 in the examples/ch09 folder of the Oracle AI code repository on GitHub, each with its output.
These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.
They search the ATLAS_DOCUMENTS and DOC_CHUNKS tables built in how to chunk documents for vector search in Oracle: eight sample documents split into 44 chunks of up to 100 words, embedded with ALL_MINILM_L12_V2.
Chain Text, Chunks, and Embeddings
The functions of DBMS_VECTOR_CHAIN form a chain: UTL_TO_TEXT returns what UTL_TO_CHUNKS takes, and UTL_TO_CHUNKS returns what UTL_TO_EMBEDDINGS takes.
Example:
-- text, chunks, and embeddings with the functions of DBMS_VECTOR_CHAIN, for one document
select json_value(e.column_value, '$.embed_id') as chunk_id,
regexp_replace(substr(json_value(e.column_value, '$.embed_data'), 1, 40), '\s+', ' ')
|| '...' as chunk_start,
substr(json_value(e.column_value, '$.embed_vector' returning clob), 1, 30) || '...'
as embedding
from atlas_documents d,
dbms_vector_chain.utl_to_embeddings(
dbms_vector_chain.utl_to_chunks(
dbms_vector_chain.utl_to_text(d.content),
json('{"by": "words", "max": 100, "split": "sentence", "normalize": "all"}')),
json('{"provider": "database", "model": "ALL_MINILM_L12_V2"}')) e
where d.file_name = 'atlas-sync-faq.docx';Output:
CHUNK_ID CHUNK_START EMBEDDING ___________ ______________________________________________ ____________________________________ 1 Atlas Sync: Frequently Asked Questions ... [-1.03329077E-001,2.57539749E-... 2 Usually because of one of the files desc... [-5.53176962E-002,3.99661474E-... 3 Folders not selected stay in Atlas and c... [-2.76873466E-002,2.0594487E-0... 4 How do we install it on many computers? ... [-7.77353644E-002,2.7684655E-0...
The chain is the way to embed chunks with a provider's model: give UTL_TO_EMBEDDINGS the Gemini parameters instead, and the chunks go to Gemini in batches, as in how to generate embeddings with Gemini from PL/SQL. Note RETURNING CLOB on JSON_VALUE for the embedding: a JSON value longer than 4,000 characters is otherwise returned as NULL.
Search the Chunks
A search over chunks is an ordinary semantic search over the chunk table.
Example:
with q as (select vector_embedding(all_minilm_l12_v2 using
'How many custom roles can an Enterprise account create?' as data) as v
from dual)
select d.doc_id, c.chunk_id,
round(vector_distance(c.embedding, q.v, cosine), 3) as distance,
regexp_replace(substr(c.chunk_text, 1, 55), '\s+', ' ') || '...' as chunk_start
from doc_chunks c join atlas_documents d on d.doc_id = c.doc_id, q
order by distance
fetch first 3 rows only;Output:
DOC_ID CHUNK_ID DISTANCE CHUNK_START
_________ ___________ ___________ _____________________________________________________________
1 4 0.372 The built-in roles are Viewer, Sales, Billing, and Admi...
1 2 0.439 Every account has at least one; the person who created...
1 5 0.482 A custom role starts as a copy of a built-in role; you ...The nearest chunk, from the CRM administrator guide, is the one about roles, and it continues with the sentence that answers the question: up to 20 custom roles per account. The next two are its neighbors in the same guide. In a RAG application, chunks like these become the language model's sources.
Test the Search with Known Answers
DOC_QUESTIONS holds 14 questions that only the documents answer, each with words the right chunk must contain. The test counts how many questions have their answer in the three nearest chunks.
Example:
-- questions answered only by the documents, each with words the right chunk must contain
create table doc_questions (
question_id number constraint doc_questions_pk primary key,
question varchar2(200) not null,
answer_text varchar2(100) not null
);
insert into doc_questions values
( 1, 'How many custom roles can an Enterprise account have?', 'up to 20 per account'),
( 2, 'How long is an invitation link valid?', 'valid for 7 days'),
( 3, 'What is the largest file the contact importer accepts?', '20 MB'),
( 4, 'How long is the audit log kept on Professional?', '180 days'),
( 5, 'When does Billing retry a failed payment?', '3 days later'),
( 6, 'How long does a refund of a direct debit take?', 'up to 3 business days'),
( 7, 'What does a yearly subscription cost?', 'price of ten months'),
( 8, 'Which Android version does the mobile app need?', 'Android 11'),
( 9, 'How often does the app sync in the background?', 'every 15 minutes'),
(10, 'How many records does one page of the API return?', '100 records per page'),
(11, 'Until when is API version v3 supported?', '30 June 2027'),
(12, 'What is the largest file Atlas Sync can upload?', '5 GB'),
(13, 'When was Atlas CRM 8.4.1 released?', '14 September 2026'),
(14, 'How much smaller are offline downloads in 3.9.1?', '40 percent');
commit;
-- is the answer in one of the 3 chunks nearest to the question?
select count(*) as questions,
count(case when exists (
select 1
from (select c.chunk_text
from doc_chunks c
order by vector_distance(c.embedding,
vector_embedding(all_minilm_l12_v2
using q.question as data), cosine)
fetch first 3 rows only) top3
where instr(top3.chunk_text, q.answer_text) > 0) then 1 end)
as answer_in_top_3
from doc_questions q;Output:
Table DOC_QUESTIONS created.
14 rows inserted.
Commit complete.
QUESTIONS ANSWER_IN_TOP_3
____________ __________________
14 13Thirteen of 14 answers are in the top three chunks.
Choose the Chunk Size
Chunk size is the main decision of a document search:
- Small chunks are precise, but can separate an answer from the words that make it findable.
- Large chunks keep context, but dilute it. A chunk about five things is near no question in particular, and a model that reads 256 tokens never sees the end of a long chunk.
- Every chunk passed to a language model costs tokens.
This example chunks the documents five ways, at most 30, 50, 100, 200, and 400 words, and runs the test on each. It also counts how often the nearest chunk holds the answer and how much text the top three chunks contain.
Example:
create table chunk_trials (
max_words number,
chunk_text varchar2(4000),
embedding vector(384, float32)
);
-- the documents chunked five ways: at most 30, 50, 100, 200, and 400 words per chunk
insert into chunk_trials
select s.max_words, json_value(c.column_value, '$.chunk_data'),
vector_embedding(all_minilm_l12_v2
using json_value(c.column_value, '$.chunk_data') as data)
from atlas_documents d,
(values (30), (50), (100), (200), (400)) s (max_words),
dbms_vector_chain.utl_to_chunks(
dbms_vector_chain.utl_to_text(d.content),
json_object('by' value 'words', 'max' value s.max_words,
'split' value 'sentence', 'normalize' value 'all' returning json)) c;
commit;
with q as (
select question_id, answer_text,
vector_embedding(all_minilm_l12_v2 using question as data) as v
from doc_questions),
ranked as (
select t.max_words, q.question_id, q.answer_text, t.chunk_text,
row_number() over (partition by t.max_words, q.question_id
order by vector_distance(t.embedding, q.v, cosine)) as rn
from chunk_trials t cross join q)
select max_words,
count(distinct case when rn = 1 and instr(chunk_text, answer_text) > 0
then question_id end) as answer_first,
count(distinct case when rn <= 3 and instr(chunk_text, answer_text) > 0
then question_id end) as answer_in_top_3,
round(avg(case when rn <= 3 then length(chunk_text) end) * 3) as characters_in_top_3
from ranked
group by max_words
order by max_words;Output:
Table CHUNK_TRIALS created.
347 rows inserted.
Commit complete.
MAX_WORDS ANSWER_FIRST ANSWER_IN_TOP_3 CHARACTERS_IN_TOP_3
____________ _______________ __________________ ______________________
30 10 12 343
50 10 12 560
100 13 13 1298
200 11 13 2383
400 6 10 3678Chunks of up to 100 words win: the answer is in the nearest chunk for 13 of 14 questions. Smaller chunks lose questions whose answer is split from its context. At 400 words, the model reads only the first 200 or so of each chunk, and the answer comes first for just 6 questions, while the three chunks sent to a language model hold almost 3,700 characters, ten times the 30-word chunks. Run the same test with your own documents and questions before choosing.
Add Documents with One Function
New documents should be searchable at once. ADD_DOCUMENT stores a document and its chunks in one call and returns the new document's ID.
Example:
create or replace function add_document (
p_file_name in varchar2,
p_doc_type in varchar2,
p_title in varchar2,
p_product_id in number,
p_content in blob
) return number
is
l_doc_id number;
begin
insert into atlas_documents (file_name, doc_type, title, product_id, content)
values (p_file_name, p_doc_type, p_title, p_product_id, p_content)
returning doc_id into l_doc_id;
insert into doc_chunks (doc_id, chunk_id, chunk_offset, chunk_length, chunk_text,
embedding)
select l_doc_id,
row_number() over (order by c.chunk_offset),
c.chunk_offset, c.chunk_length, c.chunk_text,
vector_embedding(all_minilm_l12_v2 using c.chunk_text as data)
from vector_chunks(dbms_vector_chain.utl_to_text(p_content)
by words max 100 split by sentence normalize all) c;
return l_doc_id;
end;
/
declare
l_doc_id number;
begin
l_doc_id := add_document(
p_file_name => 'atlas-crm-8.4.1-release-notes.html',
p_doc_type => 'Release notes',
p_title => 'Atlas CRM 8.4.1',
p_product_id => 1,
p_content => to_blob(bfilename('ATLAS_FILES',
'atlas-crm-8.4.1-release-notes.html')));
commit;
dbms_output.put_line('Document ' || l_doc_id || ' added');
end;
/
select d.doc_id, d.title, count(c.chunk_id) as chunks
from atlas_documents d left join doc_chunks c on c.doc_id = d.doc_id
where d.file_name = 'atlas-crm-8.4.1-release-notes.html'
group by d.doc_id, d.title;Output:
Function ADD_DOCUMENT compiled
Document 9 added
PL/SQL procedure successfully completed.
DOC_ID TITLE CHUNKS
_________ __________________ _________
9 Atlas CRM 8.4.1 2ADD_DOCUMENT takes the content as a BLOB from anywhere: a server file, as here, or a file uploaded on an APEX page, read from APEX_APPLICATION_TEMP_FILES. To change a document, delete it, which deletes its chunks, and add it again.
Let a Hybrid Vector Index Do It All
A hybrid vector index on a BLOB column does everything above by itself: it extracts the text with the document filters, chunks it, embeds the chunks, and indexes both the words and the vectors. This example creates one on the documents table and searches it.
Example:
set timing on
create hybrid vector index atlas_documents_hybrid on atlas_documents (content)
parameters ('model ALL_MINILM_L12_V2 vector_idxtype ivf');
set timing off
select d.title,
regexp_replace(substr(r.chunk_text, 1, 60), '\s+', ' ') || '...' as chunk_start
from json_table(
dbms_hybrid_vector.search(json('{
"hybrid_index_name": "ATLAS_DOCUMENTS_HYBRID",
"search_text": "Until when is API version v3 supported?",
"return": {"topN": 3, "values": ["rowid", "chunk_text"]}}')),
'$[*]' columns (row_id varchar2(18) path '$.rowid',
chunk_text varchar2(4000) path '$.chunk_text')) r
join atlas_documents d on d.rowid = chartorowid(r.row_id);Output:
Hybrid VECTOR created. Elapsed: 00:00:02.459 TITLE CHUNK_START _______________________________ __________________________________________________________________ Atlas Connect API FAQ Each API version is supported for 18 months after the next v... Atlas Mobile 3.9.1 Atlas Mobile 3.9.1 Release Notes Atlas Mobile 3.9.1 Release... Atlas Billing 5.2 User Guide already issued, support issues a credit note for the tax. P...
The index built in under three seconds, and its first result is the passage of the API FAQ that answers the question. Oracle Text keeps the index settings in CTX_USER_INDEX_VALUES and its maintenance in CTX_USER_INDEXES.
Example:
select ixv_attribute as setting, ixv_value as value
from ctx_user_index_values
where ixv_index_name = 'ATLAS_DOCUMENTS_HYBRID'
and (ixv_attribute like 'CHUNK%' or ixv_attribute like 'VECTOR%'
or ixv_attribute = 'MODEL_NAME')
order by ixv_attribute;
select idx_sync_type, idx_sync_interval
from ctx_user_indexes
where idx_name = 'ATLAS_DOCUMENTS_HYBRID';Output:
SETTING VALUE __________________ ______________________________ CHUNK_BY WORDS CHUNK_MAX 100 CHUNK_NORMALIZE ALL CHUNK_OVERLAP 0 CHUNK_SPLIT RECURSIVELY MODEL_NAME "ATLAS"."ALL_MINILM_L12_V2" VECTOR_ACCURACY 95 VECTOR_DATATYPE * VECTOR_DISTANCE COSINE VECTOR_IDXTYPE IVF 10 rows selected. IDX_SYNC_TYPE IDX_SYNC_INTERVAL ________________ ____________________________ AUTOMATIC FREQ=SECONDLY; INTERVAL=5
The defaults are close to the tested choice: chunks of up to 100 words, split recursively at paragraphs, then lines, then sentences, with no overlap, and an IVF index for cosine distance at 95 percent target accuracy. The index synchronizes automatically every 5 seconds, so a document added, changed, or deleted is searchable a few seconds after commit. All of this can be set in the PARAMETERS clause.
| Approach | Choose it when |
|---|---|
| Hybrid vector index | Its defaults serve you and you want no code: extraction, chunking, embedding, and keyword search in one statement. |
| Your own chunk table | You need to list the chunks, store metadata with them, control their size, or embed them with a provider's model. |
Hybrid search over articles is covered in how to build hybrid search in Oracle Database.
Conclusion
Searching documents by meaning in Oracle is a similarity search over embedded chunks. Test it with questions whose answers you know: on these documents, chunks of up to 100 words split at sentences found the most answers, and 400-word chunks the fewest. Add documents with one function that stores the file and its chunks together, or let a hybrid vector index on the BLOB column extract, chunk, embed, and keep everything in sync on its own.
