Vector Search on Oracle 26ai: When the Database Understands What You Mean

🇧🇷 Leia este post em Português: Vector Search no Oracle 26ai: quando o banco entende o que você quer dizer

The problem SQL can't solve

Have you ever needed to find all support tickets related to "memory issues in the database" — but without knowing exactly what words the analyst used when opening the ticket?

Maybe they wrote "ORA-04031", maybe "shared pool insufficient", maybe "database crashed due to lack of memory". Three different ways to describe the same problem.

With traditional SQL, you pick your poison: either write ten LIKE conditions and still miss some records, or accept that some will slip through. The database doesn't understand what you're looking for — it only compares text.

That's exactly the gap that Vector Search closes. Instead of comparing characters, the database compares meanings. And the practical result is impressive: a query with "database crashed due to lack of memory" finds, at a distance of 0.6453, a document titled "ORA-04031: insufficient memory in the Shared Pool" — with no words in common between the question and the title.

In this article I'll show how this works in Oracle 26ai, how to set up the environment with a local embedding model via ONNX, and how to build semantic searches on top of data you already have in the database.

📝 This lab was executed on Oracle 26ai on-premises, using the all-MiniLM-L12-v2 model loaded locally via ONNX — no external calls, no API dependency. Oracle 23ai on the cloud (ADB) supports the same feature via OCI GenAI, with a different configuration that won't be covered in this article.

What is a vector and why does it matter

Embeddings: transforming text into coordinates

An embedding is a numerical representation of a text in a high-dimensional space. Instead of storing words, the embedding model transforms the text into an array of numbers — typically hundreds of dimensions — where semantically similar texts are geometrically close in space.

Think of it this way: if you plot words on a graph where position reflects meaning, "dog" and "hound" are very close, while "dog" and "tablespace" are far apart. Vector Search operates on exactly this logic, but in 384 dimensions (in the model we use) instead of 2.

The practical result: when you search for "backup failure", the database finds documents that mention "ORA-19809", "FRA full" and "recovery point" — even if none of those words appear in your query.

The model learned, during training, that all those concepts inhabit the same semantic neighborhood.

Similarity: distance as relevance

The distance between two vectors determines relevance.

Oracle 26ai supports five metrics, but the most commonly used for text is cosine distance — which measures the angle between two vectors, regardless of text length. The smaller the distance, the more similar the texts are:

In our lab results, the distance of 0.6453 between "database crashed due to lack of memory" and the ORA-04031 document indicates relevant semantic similarity. Documents with distances above 0.80 are rarely relevant to the query.

What Oracle 26ai offers

Native vector support in Oracle was introduced in 23ai and evolved in 26ai. The main components are:

The VECTOR type

The column VECTOR(384, FLOAT32) stores an array of 384 floating-point numbers directly in the table — no auxiliary tables, no external extensions. Fixing the dimensions in the column definition is a good practice: it ensures only vectors from the correct model can be inserted.

HNSW: the index that makes search viable

Without an index, a vector search compares the query against every row in the table — impractical in a production environment.

The HNSW (Hierarchical Navigable Small World) index builds an in-memory navigation graph that allows finding the nearest neighbors in sub-linear time. The TARGET ACCURACY 95 parameter balances speed and precision — 95% of correct results are returned, with much lower latency than exact search.

📝 Oracle 26ai also supports the IVF (Inverted File / Neighbor Partitions) index, which uses less memory at the cost of slightly lower precision. For very large datasets that don't fit entirely in memory, IVF may be the right choice.

The embedding model: all-MiniLM-L12-v2 via ONNX

ONNX (Open Neural Network Exchange) is an open format for machine learning models. Oracle 26ai supports loading ONNX models directly into the database via DBMS_VECTOR.LOAD_ONNX_MODEL — the model is stored in the Oracle data dictionary as a mining object and is invoked by the SQL function VECTOR_EMBEDDING with no external network calls.

The model chosen for this lab is all-MiniLM-L12-v2, made available by Oracle in augmented ONNX format — it includes integrated tokenization and post-processing. It generates vectors of 384 dimensions and supports multiple languages, including Portuguese.

It was trained to capture semantic similarity in information retrieval tasks — exactly what we need.

(*) via DBMS_VECTOR.LOAD_ONNX_MODEL_CLOUD (Object Storage)

Oracle makes the model available at this link, as described in this article.

The Lab: environment setup

Prerequisite: Vector Memory Area

Before creating the first vector index, Oracle requires that vector_memory_size be configured. The parameter can be changed dynamically in the CDB, but needs to be saved to the SPFILE to persist across database restarts:

-- As SYS in CDB root
ALTER SYSTEM SET vector_memory_size = 1040M SCOPE=BOTH;

-- Configure in the PDB
ALTER SESSION SET CONTAINER = RRPDB;
ALTER SYSTEM SET vector_memory_size = 512M SCOPE=BOTH;

-- Confirm
SELECT name, value
  FROM v$spparameter
 WHERE name = 'vector_memory_size';

NAME                      VALUE
------------------------- ---------------
vector_memory_size        536870912

⚠️ The ORA-51962 error (vector memory area out of space) occurs when vector_memory_size = 0. It appears in the PDB, but the cause lies in the CDB — which can confuse those setting this up for the first time.

Loading the ONNX model

First, the ONNX model must be placed in a directory on the database server. Then an Oracle directory object is created pointing to that OS path so the model can be loaded into the data dictionary.

SQL> show pdbs

    CON_ID CON_NAME       OPEN MODE  RESTRICTED
---------- -------------- ---------- ----------
         2 PDB$SEED       READ ONLY  NO
         3 RRPDB          READ WRITE NO

SQL> alter session set container=RRPDB;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME       OPEN MODE  RESTRICTED
---------- -------------- ---------- ----------
         3 RRPDB          READ WRITE NO
SQL> show user
USER is "SYS"

SQL> CREATE OR REPLACE DIRECTORY onnx_models_dir
  2    AS '/home/oracle/app/oracle/onnx_models';

Directory created.

SQL> select *
  2    from dba_directories
  3    where directory_name like 'ONNX%';

OWNER  DIRECTORY_NAME     DIRECTORY_PATH                      ORIGIN_CON_ID
------ ------------------ ----------------------------------- -------------
SYS      ONNX_MODELS_DIR /home/oracle/app/oracle/onnx_models              3

1 row selected.

SQL> GRANT READ ON DIRECTORY onnx_models_dir TO rrezende;

Grant succeeded.

SQL> GRANT CREATE MINING MODEL TO rrezende;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_vector TO rrezende;

Grant succeeded.

SQL> GRANT EXECUTE ON dbms_vector_chain TO rrezende;

Grant succeeded.

With the Oracle directory created and grants in place, loading the model is straightforward:

-- As RREZENDE
SQL> conn rrezende@rrpdb
Enter password:
Connected.

SQL> BEGIN
  2    DBMS_VECTOR.LOAD_ONNX_MODEL(
  3      directory  => 'ONNX_MODELS_DIR',
  4      file_name  => 'all_MiniLM_L12_v2.onnx',
  5      model_name => 'ALL_MINILM_L12_V2'
  6    );
  7  END;
  8  /

PL/SQL procedure successfully completed.

-- Verify loading
SQL> COL model_name      FOR A25
SQL> COL algorithm       FOR A10
SQL> COL mining_function FOR A12
SQL> SELECT model_name, algorithm, mining_function
  2    FROM user_mining_models
  3    WHERE model_name = 'ALL_MINILM_L12_V2';

MODEL_NAME         ALGORITHM  MINING_FUNCT
------------------ ---------- ------------
ALL_MINILM_L12_V2  ONNX       EMBEDDING

1 row selected.

Once loaded, the model is a native SQL function. Testing it is simple:

SQL> SELECT VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Teste do modelo ONNX local' AS DATA)
  2    AS embedding_onnx
  3  FROM dual;

EMBEDDING_ONNX
--------------------------------------------------------------------------
[-6.71416521E-002,2.61541009E-002,-4.83012293E-003,3.49481567E-003,

SQL> SELECT VECTOR_DIMENSION_COUNT(VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Teste de dimensões' AS DATA)) AS dimensoes
  2    FROM dual;

 DIMENSOES
----------
       384

SQL> SELECT VECTOR_DIMENSION_COUNT(VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING 'Test in English' AS DATA)) AS dimensoes_en
  2    FROM dual;

DIMENSOES_EN
------------
   384

384 dimensions confirmed — in Portuguese and in English. The model was designed to be multilingual.

Table structure

The lab uses four tables in the RREZENDE schema:

The table creation scripts and sample data are available here:
doc_suporte
doc_tecnico
doc_pdf

The embedding column is defined with a fixed dimension: VECTOR(384, FLOAT32). This prevents accidental insertion of vectors from a different model — a good governance practice as the environment evolves.

Generating the embeddings

With the model loaded, generating embeddings is a simple PL/SQL loop.

For the doc_suporte table:

SQL> BEGIN
  2    FOR rec IN (
  3      SELECT doc_id, titulo || '. ' || conteudo AS texto
  4        FROM rrezende.doc_suporte
  5       WHERE embedding IS NULL
  6    ) LOOP
  7      UPDATE rrezende.doc_suporte
  8         SET embedding = VECTOR_EMBEDDING(
  9                           ALL_MINILM_L12_V2 USING rec.texto AS DATA
 10                         )
 11       WHERE doc_id = rec.doc_id;
 12    END LOOP;
 13    COMMIT;
 14    DBMS_OUTPUT.PUT_LINE('doc_suporte: embeddings gerados com sucesso.');
 15  END;
 16  /
doc_suporte: embeddings gerados com sucesso.

PL/SQL procedure successfully completed.

SQL> SELECT doc_id, SUBSTR(titulo, 1, 50) AS titulo, VECTOR_DIMENSION_COUNT(embedding) AS dims
  2    FROM rrezende.doc_suporte
  3    ORDER BY doc_id;

DOC_ID TITULO                                              DIMS
------ -------------------------------------------------- -----
    13 Lentidão em queries após reorganização de índices    384
    14 Falha de conexão intermitente — TNS-12541            384
    15 Crescimento inesperado de tablespace — alerta 85%    384
    16 ORA-04031: memória insuficiente no Shared Pool       384
    17 Backup RMAN falhou — ORA-19809 limit exceeded        384
    18 Relatório de manutenção preventiva — agosto 2026     384
    19 Deadlock recorrente no módulo de faturamento         384
    20 Migração de tablespace para compressão avançada      384
    21 High CPU usage — unoptimized full table scan detec   384
    22 Redo log switch storm — I/O saturation               384
    23 Data Guard lag exceeding threshold — 45 minutes      384
    24 Password expiration causing application outage       384

12 rows selected.

📝 Why concatenate title + content? The embedding captures the full semantic meaning of the text. Including the title improves representation quality — especially for short documents where the title carries key information.

The same loop is repeated for the doc_tecnico table, with identical results: 10 documents, all with 384 dimensions.

SQL> BEGIN
  2    FOR rec IN (
  3      SELECT doc_id, titulo || '. ' || conteudo AS texto
  4        FROM rrezende.doc_tecnico
  5       WHERE embedding IS NULL
  6    ) LOOP
  7      UPDATE rrezende.doc_tecnico
  8         SET embedding = VECTOR_EMBEDDING(
  9                           ALL_MINILM_L12_V2 USING rec.texto AS DATA
 10                         )
 11       WHERE doc_id = rec.doc_id;
 12    END LOOP;
 13    COMMIT;
 14    DBMS_OUTPUT.PUT_LINE('doc_tecnico: embeddings gerados com sucesso.');
 15  END;
 16  /
doc_tecnico: embeddings gerados com sucesso.

PL/SQL procedure successfully completed.

SQL> SELECT doc_id, SUBSTR(titulo, 1, 50) AS titulo, VECTOR_DIMENSION_COUNT(embedding) AS dims
  2    FROM rrezende.doc_tecnico
  3    ORDER BY doc_id;

DOC_ID TITULO                                                    DIMS
------ ------------------------------------------------------- ------
    11 Bind Variables: por que seu banco odeia SQL litera         384
    12 Estatísticas do otimizador: quando coletar e quand         384
    13 AWR e Statspack: diagnóstico de performance no Ora         384
    14 Data Guard: conceitos e modos de proteção                  384
    15 RMAN: estratégia de backup para ambientes críticos         384
    16 Index internals: B-tree, Bitmap, and when to use e         384
    17 Partitioning strategies for large tables                   384
    18 Oracle Unified Auditing: tracking who did what and         384
    19 Vector data type and AI Vector Search in Oracle 23         384
    20 JSON Relational Duality Views — Oracle 23ai                384

10 rows selected.

Creating the vector index

With embeddings generated, the next step is to create the index that makes search efficient. Without an index, a similarity query would do a full table scan — comparing the query against every row, one by one. That works for small tables, but is impractical in production.

Oracle 26ai offers two types of vector index:

• HNSW (Hierarchical Navigable Small World) — builds an in-memory navigation graph. The fastest for searches, ideal for datasets that fit within the Vector Memory Area.
• IVF (Inverted File / Neighbor Partitions) — partitions the vector space into clusters and searches only the nearest clusters. Uses less memory than HNSW, suitable for very large datasets.

For this lab, I used HNSW with DISTANCE COSINE and TARGET ACCURACY 95:

SQL> CREATE VECTOR INDEX vidx_suporte_onnx
  2    ON rrezende.doc_suporte (embedding)
  3    ORGANIZATION INMEMORY NEIGHBOR GRAPH
  4    DISTANCE COSINE
  5    WITH TARGET ACCURACY 95;

Index created.

SQL> CREATE VECTOR INDEX vidx_tecnico_onnx
  2    ON rrezende.doc_tecnico (embedding)
  3    ORGANIZATION INMEMORY NEIGHBOR GRAPH
  4    DISTANCE COSINE
  5    WITH TARGET ACCURACY 95;

Index created.

SQL> COL index_name  FOR A30
SQL> COL table_name  FOR A20
SQL> COL status      FOR A10
SQL> SELECT index_name, table_name, status
  2    FROM user_indexes
  3    WHERE index_type LIKE '%VECTOR%'
  4    ORDER BY table_name;

INDEX_NAME	          TABLE_NAME       STATUS
--------------------- --------------- -------
VIDX_SUPORTE_ONNX     DOC_SUPORTE       VALID
VIDX_TECNICO_ONNX     DOC_TECNICO       VALID

2 rows selected.

⚠️ The HNSW index uses the Vector Memory Area — the vector_memory_size parameter configured earlier. If the value is 0 or insufficient, Oracle returns ORA-51962 when trying to create the index.

PDFs: the chunking pipeline

Long documents like PDFs need a different approach. A single embedding for a 300-page document would lose all semantic granularity. The solution is chunking: splitting the PDF into smaller pieces, generating one embedding per chunk, and storing each chunk as a row.

Oracle 26ai provides this pipeline natively via DBMS_VECTOR_CHAIN:

PDF (BLOB)
    │
    ▼  UTL_TO_TEXT      — extracts text from the PDF
    │
    ▼  UTL_TO_CHUNKS    — splits into 150-word chunks with 20-word overlap
    │
    ▼  VECTOR_EMBEDDING — generates embedding per chunk via local ONNX
    │
    ▼  INSERT           — stores chunks + embeddings in the table

The PL/SQL block below handles the entire pipeline at once:

SQL> DECLARE
  2    l_chunks_gerados NUMBER := 0;
  3  BEGIN
  4    FOR pdf IN (
  5  SELECT p.doc_id, p.nome_arquivo, p.conteudo_pdf
  6    FROM rrezende.doc_pdf p
  7   WHERE NOT EXISTS (
  8     SELECT 1 FROM rrezende.doc_pdf_chunks c
  9      WHERE c.doc_id = p.doc_id
 10   )
 11    ) LOOP
 12  DBMS_OUTPUT.PUT_LINE('Processando: ' || pdf.nome_arquivo);
 13  INSERT INTO rrezende.doc_pdf_chunks
 14    (doc_id, chunk_seq, chunk_offset, chunk_length, chunk_text, embedding)
 15  SELECT
 16    pdf.doc_id,
 17    JSON_VALUE(c.column_value, '$.chunk_id'     RETURNING NUMBER),
 18    JSON_VALUE(c.column_value, '$.chunk_offset' RETURNING NUMBER),
 19    JSON_VALUE(c.column_value, '$.chunk_length' RETURNING NUMBER),
 20    JSON_VALUE(c.column_value, '$.chunk_data'),
 21    VECTOR_EMBEDDING(
 22      ALL_MINILM_L12_V2
 23      USING JSON_VALUE(c.column_value, '$.chunk_data') AS DATA
 24    )
 25  FROM DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(
 26   DBMS_VECTOR_CHAIN.UTL_TO_TEXT(pdf.conteudo_pdf),
 27   JSON('{"by":"words","max":"150","overlap":"20",
 28          "split":"recursively","normalize":"all"}')
 29       ) c;
 30  -- SQL%ROWCOUNT deve ser lido ANTES do COMMIT
 31  -- O COMMIT zera o valor de SQL%ROWCOUNT
 32  l_chunks_gerados := l_chunks_gerados + SQL%ROWCOUNT;
 33      DBMS_OUTPUT.PUT_LINE('  → ' || SQL%ROWCOUNT || ' chunks gerados.');
 34  COMMIT;
 35    END LOOP;
 36    DBMS_OUTPUT.PUT_LINE('Total de chunks: ' || l_chunks_gerados);
 37* END;
 38  /

Processando: database-concepts.pdf
¿ 2883 chunks gerados.
Processando: SELECT_AI_no_Oracle_23ai_e_26ai-Controlando_o_consumo_de_tokens_por_schema.pdf
¿ 52 chunks gerados.
Total de chunks: 2935

PL/SQL procedure successfully completed.

Results for the two loaded PDFs:

SQL> COL nome_arquivo  FOR A80
SQL> COL total_chunks  FOR 9999
SQL> COL min_chars     FOR 9999
SQL> COL max_chars     FOR 9999
SQL> COL sem_embedding FOR 9999
SQL> SELECT p.doc_id, p.nome_arquivo, COUNT(c.chunk_id) AS total_chunks, MIN(c.chunk_length) AS min_chars, MAX(c.chunk_length) AS max_chars,
  2         SUM(CASE WHEN c.embedding IS NULL THEN 1 ELSE 0 END) AS sem_embedding
  3    FROM rrezende.doc_pdf p
  4    LEFT JOIN rrezende.doc_pdf_chunks c
  5      ON c.doc_id = p.doc_id
  6    GROUP BY p.doc_id, p.nome_arquivo
  7    ORDER BY p.doc_id;

 DOC_ID NOME_ARQUIVO                                                                     TOTAL_CHUNKS MIN_CHARS MAX_CHARS SEM_EMBEDDING
------- -------------------------------------------------------------------------------- ------------ --------- --------- -------------
      1 database-concepts.pdf                                                                    2883       158      1181             0
      2 SELECT_AI_no_Oracle_23ai_e_26ai-Controlando_o_consumo_de_tokens_por_schema.pdf             52       393       799             0
  
2 rows selected.

2883 chunks from Oracle Database Concepts (12.73 MB, EN) and 52 chunks from the previous article in this series on SELECT AI (1.08 MB, PT) — the one you've probably already read. 😄

Creating the vector index for chunks

With chunks generated and embeddings populated, we create the HNSW index for the doc_pdf_chunks table:

SQL> CREATE VECTOR INDEX vidx_pdf_chunks_onnx
  2    ON rrezende.doc_pdf_chunks (embedding)
  3    ORGANIZATION INMEMORY NEIGHBOR GRAPH
  4    DISTANCE COSINE
  5    WITH TARGET ACCURACY 95;

Index created.

One important note: with 2935 total chunks, the HNSW index needs more memory than the indexes on the shorter document tables. If vector_memory_size is at its limit, create the chunks index last — it's the largest of the three.

After creation, all vector indexes in the lab are live:

SQL> COL index_name for a20
SQL> COL table_name for a20
SQL> SELECT index_name, table_name, status
  2    FROM user_indexes
  3    WHERE index_type LIKE '%VECTOR%'
  4    ORDER BY table_name;

INDEX_NAME            TABLE_NAME       STATUS
--------------------- --------------- -------
VIDX_PDF_CHUNKS_ONNX  DOC_PDF_CHUNKS    VALID
VIDX_SUPORTE_ONNX     DOC_SUPORTE.      VALID
VIDX_TECNICO_ONNX     DOC_TECNICO.      VALID

3 rows selected.

With all three indexes valid, the database is ready for semantic searches across all sources simultaneously.

The results: the database understanding what you mean

Basic semantic search

Semantic search uses VECTOR_DISTANCE with FETCH FIRST N ROWS ONLY — no complex JOINs or subqueries. The HNSW index ensures Oracle doesn't scan every row.

To make testing easier, I stored the search topic in a bind variable:

SQL> COL doc_id     FOR 999
SQL> COL titulo     FOR A55
SQL> COL categoria  FOR A15
SQL> COL dist       FOR 0.9999
SQL> VARIABLE query VARCHAR2(200)
SQL> EXEC :query := 'banco travou por falta de memória'

PL/SQL procedure successfully completed.

SQL> SELECT doc_id, SUBSTR(titulo, 1, 55) AS titulo, categoria,
  2         ROUND(VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA), COSINE), 4) AS dist
  3    FROM rrezende.doc_suporte
  4    ORDER BY distancia ASC
  5    FETCH FIRST 3 ROWS ONLY;

DOC_ID TITULO                                         CATEGORIA      DISTANCIA
------ ---------------------------------------------- -------------- ---------
    16 ORA-04031: memória insuficiente no Shared Pool Memória           0.6453
    14 Falha de conexão intermitente — TNS-12541      Conectividade     0.7498
    17 Backup RMAN falhou — ORA-19809 limit exceeded  Backup            0.7544

    3 rows selected.

None of the query words appear in the title "ORA-04031: memória insuficiente no Shared Pool" — the query uses "banco", "travou", "falta", "memória". But the model knows the semantic neighborhood of those concepts and positions the two texts 0.6453 apart in vector space.

Another example demonstrating multilingual capability:

-- Query in English, documents in Portuguese
SQL> EXEC :query := 'database performance degradation after maintenance'

PL/SQL procedure successfully completed.

SQL> SELECT doc_id, SUBSTR(titulo, 1, 55) AS titulo, categoria,
  2         ROUND(VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA), COSINE), 4) AS dist
  3    FROM rrezende.doc_suporte
  4    ORDER BY distancia ASC
  5    FETCH FIRST 3 ROWS ONLY;

DOC_ID TITULO                                                CATEGORIA       DISTANCIA
------ ----------------------------------------------------- --------------- ---------
    13 Lentidão em queries após reorganização de índices     Performance        0.5568
    23 Data Guard lag exceeding threshold — 45 minutes       Alta Disponibil    0.6349
                     idade
    21 High CPU usage — unoptimized full table scan detected Performance        0.6445

3 rows selected.

The English query found a Portuguese document as the top result. The model was trained to understand that "performance degradation after maintenance" and "lentidão após reorganização" are the same concept in different languages.

Search with relational filter

Vector Search and conventional SQL combine naturally — just add a WHERE clause:

-- Semantic search restricted to Portuguese documents
SQL> EXEC :query := 'problema de performance no banco'

PL/SQL procedure successfully completed.

SQL> SELECT doc_id, SUBSTR(titulo, 1, 55) AS titulo, idioma, categoria,
  2         ROUND(VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA), COSINE), 4) AS distancia
  3    FROM rrezende.doc_suporte
  4    WHERE idioma = 'PT'
  5    ORDER BY distancia ASC
  6    FETCH FIRST 3 ROWS ONLY;

DOC_ID TITULO                                             IDIOMA  CATEGORIA      DISTANCIA
------ -------------------------------------------------- ------- -------------- ---------
    13 Lentidão em queries após reorganização de índices       PT Performance       0.5998
    16 ORA-04031: memória insuficiente no Shared Pool          PT Memória           0.6987
    20 Migração de tablespace para compressão avançada         PT Armazenamento     0.7059

3 rows selected.

Hybrid search: semantic + keyword

Specific technical terms like error codes (ORA-04031) may not have strong semantic representation in the model — after all, it's an arbitrary string. Hybrid search combines the semantic score with the presence of the term in the text:

SQL> EXEC :query := 'ORA-04031'

PL/SQL procedure successfully completed.

SQL> WITH sem AS (SELECT doc_id, VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA), COSINE) AS dist_semantica
  2       FROM rrezende.doc_suporte
  3       ORDER BY dist_semantica ASC
  4       FETCH FIRST 10 ROWS ONLY),
  5   kw AS (SELECT doc_id, 1 AS match_keyword
  6      FROM rrezende.doc_suporte
  7      WHERE UPPER(conteudo) LIKE UPPER('%' || :query || '%')
  8         OR UPPER(titulo)   LIKE UPPER('%' || :query || '%'))
  9  SELECT s.doc_id, SUBSTR(d.titulo, 1, 55) AS titulo, d.categoria, ROUND(s.dist_semantica, 4) AS dist_semantica, NVL(k.match_keyword, 0) AS keyword_match,
 10     ROUND(s.dist_semantica - (NVL(k.match_keyword, 0) * 0.05), 4) AS score_hibrido
 11    FROM sem s
 12    JOIN rrezende.doc_suporte d
 13  ON d.doc_id = s.doc_id
 14    LEFT JOIN kw k
 15  ON k.doc_id = s.doc_id
 16*   ORDER BY score_hibrido ASC
SQL> /

DOC_ID TITULO                                             CATEGORIA       DIST_SEMANTICA KEYWORD_MATCH SCORE_HIBRIDO
------ -------------------------------------------------- --------------- -------------- ------------- -------------
    16 ORA-04031: memória insuficiente no Shared Pool     Memória                  .5008             1         .4508
    14 Falha de conexão intermitente — TNS-12541          Conectividade            .5687             0         .5687
    17 Backup RMAN falhou — ORA-19809 limit exceeded      Backup                    .651             0          .651
    15 Crescimento inesperado de tablespace — alerta 85%  Armazenamento            .7328             0         .7328
    24 Password expiration causing application outage     Conectividade            .7904             0         .7904
    22 Redo log switch storm — I/O saturation             Performance               .803             0          .803
    18 Relatório de manutenção preventiva — agosto 2026   Manutenção               .8219             0         .8219
    19 Deadlock recorrente no módulo de faturamento       Concorrência             .8264             0         .8264
    23 Data Guard lag exceeding threshold — 45 minutes    Alta Disponibil          .8383             0         .8383
                     idade
    13 Lentidão em queries após reorganização de índices  Performance              .8492             0         .8492

10 rows selected.

The ORA-04031 document was already the closest semantically (0.5008), but the keyword_match = 1 — for containing the exact term in the title — further reduces the hybrid score to 0.4508, reinforcing its relevance. A combination that captures the best of both worlds.

PDF search: semantic chunks

Searching long documents shows the power of chunking. With "controle de tokens e custo de LLM", the database finds specific passages from the previous article in this series — even though it's a 52-page PDF:

SQL> EXEC :query := 'controle de tokens e custo de LLM'

PL/SQL procedure successfully completed.

SQL> SELECT c.chunk_id, c.doc_id, p.nome_arquivo, c.chunk_seq,
  2     ROUND(VECTOR_DISTANCE(c.embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA), COSINE), 4) AS distancia,
  3     CAST(SUBSTR(c.chunk_text, 1, 60) AS VARCHAR2(60))  AS trecho
  4    FROM rrezende.doc_pdf_chunks c
  5    JOIN rrezende.doc_pdf p
  6  ON p.doc_id = c.doc_id
  7    ORDER BY distancia ASC
  8*   FETCH FIRST 5 ROWS ONLY
SQL> /

CHUNK_ID DOC_ID NOME_ARQUIVO                                                                    CHUNK_SEQ DISTANCIA TRECHO
-------- ------ ------------------------------------------------------------------------------- --------- --------- ------------------------------------------------------------
    5869      2 SELECT_AI_no_Oracle_23ai_e_26ai-Controlando_o_consumo_de_tokens_por_schema.pdf         51    0.5064 AI_OPS + PKG_AI_GATEWAY resolve os três problemas de uma vez
    5868      2 SELECT_AI_no_Oracle_23ai_e_26ai-Controlando_o_consumo_de_tokens_por_schema.pdf         50    0.5515 naturalmente alinhada com o que aparece na fatura. O
    5866      2 SELECT_AI_no_Oracle_23ai_e_26ai-Controlando_o_consumo_de_tokens_por_schema.pdf         48    0.5698 rastreamento de tokens é ainda mais relevante nesse modelo d
    5865      2 SELECT_AI_no_Oracle_23ai_e_26ai-Controlando_o_consumo_de_tokens_por_schema.pdf         47    0.5917 COUNT(*) AS chamadas, SUM(tokens_total * 4) AS total_chars,
    5867      2 SELECT_AI_no_Oracle_23ai_e_26ai-Controlando_o_consumo_de_tokens_por_schema.pdf         49    0.6006 GROUP BY caller_schema, error_code, error_message

5 rows selected.

Chunks 51, 50 and 48 — all from the PT article — were the most relevant, showing the model found the right passages inside a real Portuguese document.

Unified search: multiple sources in one query

The lab's highlight: searching simultaneously across support notes, technical documentation and PDF chunks, with a single query and the embedding generated only once:

-- Generate the query embedding once and reuse across all three searches
SQL> EXEC :query := 'falha de backup e recovery no Oracle'

PL/SQL procedure successfully completed.

SQL> WITH q_embed AS (SELECT VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA) AS vec
  2     FROM dual)
  3  SELECT 'Suporte' AS origem, doc_id, TO_NUMBER(NULL) AS chunk_seq,
  4     CAST(SUBSTR(titulo, 1, 60) AS VARCHAR2(60)) AS titulo_trecho,
  5     ROUND(VECTOR_DISTANCE(embedding, q.vec, COSINE), 4) AS distancia
  6    FROM rrezende.doc_suporte, q_embed q
  7    WHERE embedding IS NOT NULL
  8  UNION ALL
  9  SELECT 'Técnico' AS origem, doc_id, TO_NUMBER(NULL) AS chunk_seq,
 10     CAST(SUBSTR(titulo, 1, 60) AS VARCHAR2(60)) AS titulo_trecho,
 11     ROUND(VECTOR_DISTANCE(embedding, q.vec, COSINE), 4) AS distancia
 12    FROM rrezende.doc_tecnico, q_embed q
 13    WHERE embedding IS NOT NULL
 14  UNION ALL
 15  SELECT 'PDF' AS origem, c.doc_id, c.chunk_seq,
 16     CAST(SUBSTR(c.chunk_text, 1, 60) AS VARCHAR2(60)) AS titulo_trecho,
 17     ROUND(VECTOR_DISTANCE(c.embedding, q.vec, COSINE), 4) AS distancia
 18    FROM rrezende.doc_pdf_chunks c, q_embed q
 19    WHERE c.embedding IS NOT NULL
 20    ORDER BY distancia ASC
 21    FETCH FIRST 10 ROWS ONLY;

ORIGEM     DOC_ID  CHUNK_SEQ TITULO_TRECHO                                                          DISTANCIA
---------- ------ ---------- ---------------------------------------------------------------------- ---------
PDF             1       2244 files, the                                                                0.3792
                             server parameter file
                             , and control file. A sam
Técnico        15            RMAN: estratégia de backup para ambientes críticos                        0.3966
PDF             1       2264 Oracle AI Database Backup and Recovery User's Guide fo                    0.3979
PDF             1       2584 unplanned outages of networks, nodes, storage, and databases              0.4036
PDF             1       2270 block corruption                                                          0.4060
                             D:20260420030906-08'00'
                             Data Corruption
PDF             1       2272 concepts                                                                  0.4194
                             Zero Data Loss Recovery Appliance
                             The cloud-scale
PDF             1       2263 Typically, you have multiple ways to solve the preceding pro              0.4206
PDF             1       1663 Oracle AI Database Backup and Recovery User's Guide to learn              0.4257
PDF             1       2278 Copyright © 1993, 2026, Oracle and/or its affiliates.                     0.4417
                             April
PDF             1       2437 D:20260420030906-08'00'                                                   0.4438
                             undo tablespace
                             D:20260420030906-0

10 rows selected.

The RMAN technical document appears in 2nd place (distance 0.3966), right after Database Concepts chunks covering exactly the same topic. The database scanned three different sources — free text, structured documentation and PDF — and returned the most relevant results, ranked by semantic similarity.

One important implementation detail: the q_embed subquery generates the query embedding only once and reuses it across all three searches. Without it, the database would call VECTOR_EMBEDDING three times for the same string — tripling the model's workload.

COSINE vs EUCLIDEAN: which metric to use?

This lab also compared the two most common metrics. For "falha de backup no banco de dados":

SQL> EXEC :query := 'falha de backup no banco de dados'

PL/SQL procedure successfully completed.

SQL> SELECT doc_id, SUBSTR(titulo, 1, 40)   AS titulo,
  2         ROUND(VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA), COSINE), 4) AS dist_cosine,
  3         ROUND(VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(ALL_MINILM_L12_V2 USING :query AS DATA), EUCLIDEAN), 4) AS dist_euclidean
  4    FROM rrezende.doc_suporte
  5    ORDER BY dist_cosine ASC
  6    FETCH FIRST 5 ROWS ONLY;

DOC_ID TITULO                                    DIST_COSINE DIST_EUCLIDEAN
------ ----------------------------------------- ----------- --------------
    17 Backup RMAN falhou — ORA-19809 limit exc          .57         1.0677
    18 Relatório de manutenção preventiva — ago        .6652         1.1535
    19 Deadlock recorrente no módulo de faturam        .7451         1.2207
    14 Falha de conexão intermitente — TNS-1254        .7544         1.2284
    13 Lentidão em queries após reorganização d        .7562         1.2298

5 rows selected.

The ranking is consistent between both metrics for this dataset — as expected when texts have similar sizes.

The difference shows up in collections with highly variable text lengths: COSINE tends to be more robust because it is magnitude-invariant — two texts of very different sizes can be semantically close even with different norms.

Key takeaways

Vector Search in Oracle 26ai is not a niche feature for AI projects. It's a natural extension of the database's relational capabilities — you keep using SQL, keep your normal tables, keep ACID and indexes. You just add a new search dimension: meaning.

The main points:

• Local ONNX model: no external dependency, no per-call cost, no network latency — the model runs inside the database.
• Native SQL: VECTOR_EMBEDDING and VECTOR_DISTANCE are SQL functions. They combine naturally with WHERE, JOIN and any relational predicate.
• Multilingual for free: all-MiniLM-L12-v2 understands Portuguese and English — a PT query finds EN documents and vice-versa.
• Native PDF chunking: the UTL_TO_TEXT → UTL_TO_CHUNKS → VECTOR_EMBEDDING pipeline runs entirely inside the database, with no external tools.
• Hybrid search: semantic + keyword in the same SQL — the best of both approaches for specific technical terms.

Next step: SELECT AI with RAG

Vector Search solves the retrieval problem — finding the right documents. The next article in this series goes further: using those documents as context for an LLM to generate natural language answers.

That's RAG (Retrieval-Augmented Generation) — and Oracle 26ai has native support via SELECT AI with the vector_index_name option in the profile.

Join the conversation

Are you already using Vector Search in Oracle in production? Which embedding model are you using — local ONNX or an external API? What was the biggest obstacle during setup?

Leave a comment below and find me on LinkedIn — this is exactly the kind of topic that makes for a great conversation among DBAs.

See you in the next article.

— Ricardo Rezende (@ricarezende)

Previous articles in this series:

› SELECT AI on Oracle 23ai and 26ai: Talking to Your Database in Plain Language

› SELECT AI on Oracle 23ai and 26ai: Tracking Token Usage per Schema