How to Create an IVF Vector Index in Oracle

A similarity search without an index compares the question with every row and sorts all the distances. That exact search is instant on a few thousand rows, takes a noticeable fraction of a second on fifty thousand, and seconds on ten million, for every question of every user.

A vector index makes the search approximate: it looks only where the nearest rows are likely to be, and finds most of them, usually all, in a fraction of the time. This guide covers the IVF index in Oracle AI Database 26ai: FETCH APPROX, creating the index, reading its execution plan, measuring speed and accuracy on 50,000 rows, tuning probes, and the cases where the index is silently not used.

Code for This Guide

The examples are in the examples/ch07 folder of the Oracle AI code repository on GitHub, files 01 to 12, 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.

Embed the Table

Example:

alter table ticket_archive add (embedding vector(384, float32));

set timing on
update ticket_archive
set    embedding = vector_embedding(all_minilm_l12_v2
                     using subject || '. ' || description as data);
set timing off
commit;

Output:

Table TICKET_ARCHIVE altered.

50,000 rows updated.

Elapsed: 00:18:21.886

Commit complete.

About 18 minutes for 50,000 rows, 22 milliseconds each, on the two processors Oracle AI Database Free uses. That is the price of embedding inside the database without a GPU, paid once and then per new row.

Exact Search

FETCH FIRST without another keyword, or FETCH EXACT FIRST, is an exact search: it computes all 50,000 distances and sorts them.

Example:

select archive_id, subject,
       round(vector_distance(embedding,
             vector_embedding(all_minilm_l12_v2
               using 'The invoice was paid two times by mistake' as data), cosine), 3)
         as distance
from   ticket_archive
order  by distance
fetch  first 5 rows only;

Output:

   ARCHIVE_ID SUBJECT                      DISTANCE
_____________ _________________________ ___________
        25638 Invoice paid two times          0.199
        45791 Invoice paid two times          0.199
        25803 Invoice paid two times          0.202
        25394 Invoice paid two times          0.203
        37117 Invoice paid two times          0.209

FETCH APPROX

FETCH APPROX tells the database that an approximate result is good enough. If the column has a vector index for the same distance metric, the database uses it; otherwise it runs an exact search, so the keyword is always safe to write.

Syntax:

select ...
from   table
order  by vector_distance(vector_column, query_vector [, metric])
fetch  { exact | approx } first n rows only
  [ with target accuracy percentage
  | with target accuracy parameters ( { efsearch n | neighbor partition probes n } ) ]

WITH TARGET ACCURACY asks for a percentage of the true nearest rows and lets the index decide how much work that takes. WITH TARGET ACCURACY PARAMETERS sets the work directly: NEIGHBOR PARTITION PROBES for IVF, EFSEARCH for HNSW.

How an IVF Index Works

An IVF (inverted file) index, a neighbor partition index in Oracle's terms, picks a set of centroids spread over the space of the vectors and assigns every vector to its nearest centroid. A search compares the question with the centroids first, then only with the vectors of the few nearest partitions.

The HNSW alternative is covered in how to create an HNSW vector index in Oracle.

Create the IVF Index

Syntax:

create vector index index_name on table_name (vector_column)
  organization { neighbor partitions | inmemory neighbor graph }
  [ distance { cosine | dot | euclidean | euclidean_squared | manhattan | hamming } ]
  [ with target accuracy percentage ]
  [ parameters ( type ivf, neighbor partitions n
               | type hnsw, neighbors n, efconstruction n ) ]
  [ parallel n ]

Example:

set timing on
create vector index ticket_archive_ivf on ticket_archive (embedding)
  organization neighbor partitions
  distance cosine
  with target accuracy 95;
set timing off

Output:

Vector INDEX created.

Elapsed: 00:01:53.209

About two minutes for 50,000 vectors: the database samples the vectors, computes the centroids, and assigns every vector to one.

The Index in the Data Dictionary

A vector index appears in USER_INDEXES with the type VECTOR. An IVF index keeps its data in two tables whose names start with VECTOR$ and the index name.

Example:

select index_name, index_type, index_subtype, status
from   user_indexes
where  table_name = 'TICKET_ARCHIVE';

select table_name, num_rows
from   user_tables
where  table_name like 'VECTOR$TICKET_ARCHIVE_IVF%';

Output:

INDEX_NAME                  INDEX_TYPE    INDEX_SUBTYPE              STATUS
___________________________ _____________ __________________________ _________
TICKET_ARCHIVE_PK           NORMAL                                   VALID
SYS_IL0000090875C00007$$    LOB                                      VALID
TICKET_ARCHIVE_IVF          VECTOR        NEIGHBOR_PARTITIONS_IVF    VALID
SYS_IL0000090875C00008$$    LOB                                      VALID

TABLE_NAME                                                                 NUM_ROWS
_______________________________________________________________________ ___________
VECTOR$TICKET_ARCHIVE_IVF$90875_94519_0$IVF_FLAT_CENTROIDS                      874
VECTOR$TICKET_ARCHIVE_IVF$90875_94519_0$IVF_FLAT_CENTROID_PARTITIONS          50000

The index chose 874 partitions, about 57 vectors each. The centroids table holds one vector per centroid, and the partitions table holds every embedding again, partitioned by centroid. Leave both tables alone; the index maintains them.

Search with the Index

Example:

select archive_id, subject,
       round(vector_distance(embedding,
             vector_embedding(all_minilm_l12_v2
               using 'The invoice was paid two times by mistake' as data), cosine), 3)
         as distance
from   ticket_archive
order  by distance
fetch  approx first 5 rows only;

Output:

   ARCHIVE_ID SUBJECT                      DISTANCE
_____________ _________________________ ___________
        25638 Invoice paid two times          0.199
        45791 Invoice paid two times          0.199
        25803 Invoice paid two times          0.202
        25394 Invoice paid two times          0.203
        37117 Invoice paid two times          0.209

The same five tickets in the same order. The execution plan shows that the index did the work.

Example:

-- the plan of an approximate search, with the long names of the index tables shortened
explain plan for
select archive_id
from   ticket_archive
order  by vector_distance(embedding, :query_vector, cosine)
fetch  approx first 5 rows only;

select lpad(' ', 2 * depth) || operation || ' ' || options as operation,
       regexp_replace(object_name, '^VECTOR\$TICKET_ARCHIVE_IVF\$.*\$', 'VECTOR$...$')
         as object_name
from   plan_table
order  by id;

Output:

Explained.

OPERATION                                        OBJECT_NAME
________________________________________________ __________________________________________
SELECT STATEMENT
  VIEW
    NESTED LOOPS
      VIEW                                       VW_IVPSR_11E7D7DE
        COUNT STOPKEY
          VIEW                                   VW_IVPSJ_578B79F1
            SORT ORDER BY STOPKEY
              HASH JOIN
                PART JOIN FILTER CREATE          :BF0000
                  VIEW                           VW_IVCR_B5B87E67
                    COUNT STOPKEY
                      VIEW                       VW_IVCN_9A1D2119
                        SORT ORDER BY STOPKEY
                          TABLE ACCESS FULL      VECTOR$...$IVF_FLAT_CENTROIDS
                PARTITION LIST JOIN-FILTER
                  TABLE ACCESS FULL              VECTOR$...$IVF_FLAT_CENTROID_PARTITIONS
      TABLE ACCESS BY USER ROWID                 TICKET_ARCHIVE

17 rows selected.

Read it from the inside out. The database scans the 874 centroids, sorts them by distance, and keeps the nearest (COUNT STOPKEY). It then reads only those centroids' partitions (PARTITION LIST JOIN-FILTER), sorts their vectors, and fetches the five winners from TICKET_ARCHIVE by row ID. The 50,000 vectors are never all read.

Measure Speed and Accuracy

An approximate search is judged on two numbers: how much faster it is, and how many of the true nearest rows it finds. MEASURE_SEARCH runs an exact and an approximate search for the 10 nearest tickets for each of 28 questions, times both, and counts how many approximate results are as near as the exact search's tenth.

Example:

create or replace procedure measure_search (p_clause in varchar2 default null)
is
  l_distances  sys.odcinumberlist;
  l_tenth      number;
  l_hits       pls_integer := 0;
  l_results    pls_integer := 0;
  l_start      timestamp;
  l_exact_ms   number := 0;
  l_approx_ms  number := 0;
  l_queries    pls_integer := 0;
  l_sql        varchar2(400) :=
    'select vector_distance(embedding, :q1, cosine) from ticket_archive
     order by vector_distance(embedding, :q2, cosine)
     fetch approx first 10 rows only ' || p_clause;

  function ms (p_since in timestamp) return number is
  begin
    return extract(second from (systimestamp - p_since)) * 1000;
  end;
begin
  -- the 28 questions: for each, the 10 nearest archived tickets
  for q in (select minilm as v from eval_questions where question_id <= 28) loop
    l_start := systimestamp;
    select vector_distance(embedding, q.v, cosine)
    bulk   collect into l_distances
    from   ticket_archive
    order  by vector_distance(embedding, q.v, cosine)
    fetch  exact first 10 rows only;
    l_exact_ms := l_exact_ms + ms(l_start);
    l_tenth := l_distances(10);

    l_start := systimestamp;
    execute immediate l_sql bulk collect into l_distances using q.v, q.v;
    l_approx_ms := l_approx_ms + ms(l_start);

    -- a result is right if it is as near as the 10th result of the exact search
    for i in 1 .. l_distances.count loop
      if l_distances(i) <= l_tenth + 1e-6 then l_hits := l_hits + 1; end if;
    end loop;
    l_results := l_results + 10;
    l_queries := l_queries + 1;
  end loop;

  dbms_output.put_line(nvl(p_clause, '(index default)'));
  dbms_output.put_line('  exact:       ' || to_char(l_exact_ms / l_queries, '990.0')
                       || ' ms per query');
  dbms_output.put_line('  approximate: ' || to_char(l_approx_ms / l_queries, '990.0')
                       || ' ms per query, '
                       || round(100 * l_hits / l_results) || '% of the right results');
end;
/

exec measure_search

Output:

Procedure MEASURE_SEARCH compiled

(index default)
  exact:         57.1 ms per query
  approximate:    3.3 ms per query, 100% of the right results

PL/SQL procedure successfully completed.

Every right result, more than fifteen times faster: about 57 milliseconds per exact search against about 3 per approximate one. Counting "as near as the tenth" rather than comparing IDs matters, because the archive holds tickets with identical texts, and two at the same distance are equally right.

Tune the Probes

Accuracy comes from how many partitions a search reads, its probes. This example sets them to 1, 2, and 5, then asks for a target accuracy of only 50 percent.

Example:

exec measure_search('with target accuracy parameters (neighbor partition probes 1)')
exec measure_search('with target accuracy parameters (neighbor partition probes 2)')
exec measure_search('with target accuracy parameters (neighbor partition probes 5)')
exec measure_search('with target accuracy 50')

Output:

with target accuracy parameters (neighbor partition probes 1)
  exact:         55.9 ms per query
  approximate:    0.9 ms per query, 68% of the right results

PL/SQL procedure successfully completed.

with target accuracy parameters (neighbor partition probes 2)
  exact:         56.1 ms per query
  approximate:    1.0 ms per query, 88% of the right results

PL/SQL procedure successfully completed.

with target accuracy parameters (neighbor partition probes 5)
  exact:         55.2 ms per query
  approximate:    1.2 ms per query, 99% of the right results

PL/SQL procedure successfully completed.

with target accuracy 50
  exact:         55.4 ms per query
  approximate:    1.9 ms per query, 100% of the right results

PL/SQL procedure successfully completed.

One partition takes under a millisecond and finds 68 percent of the right tickets; the rest sit in neighboring partitions. Two find 88 percent, five find 99. A target accuracy is a minimum, so 50 percent still read enough partitions to find them all. Set probes directly only when you have measured what they cost you, and measure on your own data: these near-identical archived tickets make the index's job easier than varied real data would.

Check One Query with INDEX_ACCURACY_QUERY

DBMS_VECTOR.INDEX_ACCURACY_QUERY runs one search with and without the index and reports the accuracy achieved.

Syntax:

dbms_vector.index_accuracy_query(
  owner_name       varchar2,
  index_name       varchar2,
  qv               vector,
  top_k            number,
  target_accuracy  number) return varchar2

Example:

declare
  l_query_vector vector;
begin
  select vector_embedding(all_minilm_l12_v2
           using 'The invoice was paid two times by mistake' as data)
  into   l_query_vector
  from   dual;

  dbms_output.put_line(dbms_vector.index_accuracy_query(
    owner_name      => user,
    index_name      => 'TICKET_ARCHIVE_IVF',
    qv              => l_query_vector,
    top_k           => 10,
    target_accuracy => 90));
end;
/

Output:

Accuracy achieved (100%) is 10% higher than the Target Accuracy requested (90%)

PL/SQL procedure successfully completed.

When the Index Is Not Used

A vector index serves one distance metric. This search asks for EUCLIDEAN on a column indexed for COSINE.

Example:

-- the index was built for COSINE; this search asks for EUCLIDEAN
explain plan for
select archive_id
from   ticket_archive
order  by vector_distance(embedding, :query_vector, euclidean)
fetch  approx first 5 rows only;

select lpad(' ', 2 * depth) || operation || ' ' || options as operation, object_name
from   plan_table
order  by id;

Output:

Explained.

OPERATION                      OBJECT_NAME
______________________________ _________________
SELECT STATEMENT
  COUNT STOPKEY
    VIEW
      SORT ORDER BY STOPKEY
        TABLE ACCESS FULL      TICKET_ARCHIVE

A full scan of TICKET_ARCHIVE: an exact search, silently, even though the query says APPROX. For normalized vectors COSINE, DOT, and EUCLIDEAN rank rows the same, but the index does not know that. The index is also skipped without FETCH APPROX, and by a query with no FETCH FIRST at all. Write every search with the index's metric, or with no metric when the index uses the default, COSINE.

Filters

Most real searches combine meaning with ordinary conditions. Write the WHERE clause as usual; the optimizer decides how to combine it with the index.

Example:

-- the nearest archived tickets about Atlas Mobile (product 3) only
select archive_id, product_id, subject,
       round(vector_distance(embedding,
             vector_embedding(all_minilm_l12_v2
               using 'The app quits as soon as it starts' as data), cosine), 3) as distance
from   ticket_archive
where  product_id = 3
order  by distance
fetch  approx first 5 rows only;

Output:

   ARCHIVE_ID    PRODUCT_ID SUBJECT                             DISTANCE
_____________ _____________ ________________________________ ___________
         5893             3 Mobile app crashes on startup          0.263
        44576             3 Mobile app crashes on startup          0.263
        43788             3 Mobile app crashes on startup          0.265
        43830             3 Mobile app crashes on startup          0.265
        49450             3 Mobile app crashes on startup          0.267

New Rows

A vector index is maintained like any other: an inserted row is in the index when the transaction commits.

Example:

insert into ticket_archive (archive_id, product_id, category, subject, description,
                            created_on, embedding)
with t (subject, description) as (
  select 'Dashboard tiles show an hourglass forever',
         'Every tile on our sales dashboard shows an hourglass and never draws the chart.'
  from   dual)
select 50001, 4, 'Bug', subject, description, sysdate,
       vector_embedding(all_minilm_l12_v2 using subject || '. ' || description as data)
from   t;
commit;

select archive_id, subject,
       round(vector_distance(embedding,
             vector_embedding(all_minilm_l12_v2
               using 'dashboard tiles stuck on an hourglass' as data), cosine), 3)
         as distance
from   ticket_archive
order  by distance
fetch  approx first 3 rows only;

Output:

1 row inserted.

Commit complete.

   ARCHIVE_ID SUBJECT                                         DISTANCE
_____________ ____________________________________________ ___________
        50001 Dashboard tiles show an hourglass forever          0.242
         9508 Download dashboard as spreadsheet                  0.616
        28088 Download dashboard as spreadsheet                  0.627

In an IVF index, a new vector joins the partition of its nearest centroid, and the centroids themselves do not move. After many changes, the partitions grow uneven and accuracy can drop. Measure it from time to time, and rebuild the index, by dropping and creating it or with DBMS_VECTOR.REBUILD_INDEX, when it falls.

Conclusion

An IVF vector index, CREATE VECTOR INDEX ... ORGANIZATION NEIGHBOR PARTITIONS, groups vectors around centroids in ordinary tables and needs no extra memory. Searches use it only with FETCH APPROX and the index's metric. On 50,000 tickets it found every right result more than fifteen times faster than an exact search. Tune accuracy with WITH TARGET ACCURACY or NEIGHBOR PARTITION PROBES from measurements on your own questions, and rebuild the index when the data drifts.