Syntax for Access Path Hints

The access path hints are modeled on the existing INDEX hints. For example:

VECTOR_INDEX_SCAN ( [ @ queryblock ] tablespec [ indexspec ])

NO_VECTOR_INDEX_SCAN ( [ @ queryblock ] tablespec [ indexspec ])

The VECTOR_INDEX_SCAN hint instructs the optimizer to use a vector index scan for the specified table. The index is optionally specified. The optimizer does not consider a full table scan.

NO_VECTOR_INDEX_SCAN disables the use of the optionally specified vector index scan.

Access Path Hint Examples

SELECT /*+ VECTOR_INDEX_SCAN(galaxies) */ name
FROM galaxies
ORDER BY VECTOR_DISTANCE( embedding, to_vector('[0,1,1,0,0]'), COSINE )
FETCH FIRST 3 ROWS ONLY;

SELECT /*+ NO_VECTOR_INDEX_SCAN(galaxies) */ name
FROM galaxies
ORDER BY VECTOR_DISTANCE( embedding, to_vector('[0,1,1,0,0]'), COSINE )
FETCH FIRST 3 ROWS ONLY;

Note that even though the second example query is expected not to use a vector index, it is just a hint that the optimizer may or may not follow. To be certain that the index will not be used, it is better to use EXACT rather than the hint, as in the following:

SELECT name
FROM galaxies
ORDER BY VECTOR_DISTANCE( embedding, to_vector('[0,1,1,0,0]'), COSINE )
FETCH EXACT FIRST 3 ROWS ONLY;

Syntax for Query Transformation Hints

The syntax for query transformation hints is similar to the INDEX hints model. For example:

VECTOR_INDEX_TRANSFORM ( [ @ queryblock ] tablespec [ indexspec [ filtertype ]] )

NO_VECTOR_INDEX_TRANSFORM ( [ @ queryblock ] tablespec [ indexspec ] )

VECTOR_INDEX_TRANSFORM forces the transformation and uses the optionally specified index, whereas NO_VECTOR_INDEX_TRANSFORM disables the transformation and the use of the optionally specified index. For more information about the potential query transformations, see Optimizer Plans for HNSW Vector Indexes and Optimizer Plans for IVF Vector Indexes.

The optional filtertype specification for the VECTOR_INDEX_TRANSFORM hint can have the following values:

• PRE_FILTER_WITH_JOIN_BACK (only applies to HNSW indexes)
• PRE_FILTER_WITHOUT_JOIN_BACK (applies to both HNSW and IVF indexes)
• IN_FILTER_WITH_JOIN_BACK (only applies to HNSW indexes)
• IN_FILTER_WITHOUT_JOIN_BACK (only applies to HNSW indexes)
• POST_FILTER_WITHOUT_JOIN_BACK (only applies to IVF indexes)

Each filtertype value corresponds to exactly one state in the query transformation. If no filtertype value is specified in the VECTOR_INDEX_TRANSFORM hint, then all of the valid filtering types are considered and the best one is chosen.

Query Transformation Hint Examples

SELECT /*+ vector_index_transform(galaxies galaxies_ivf_idx pre_filter_without_join_back) */ name
FROM galaxies
WHERE id<5
ORDER BY VECTOR_DISTANCE( embedding, to_vector('[0,1,1,0,0]'), COSINE )
FETCH FIRST 3 ROWS ONLY WITH TARGET ACCURACY 90;

SELECT /*+ vector_index_transform(galaxies) */ name
FROM galaxies
WHERE id<5
ORDER BY VECTOR_DISTANCE( embedding, to_vector('[0,1,1,0,0]'), COSINE )
FETCH FIRST 10 ROWS ONLY WITH TARGET ACCURACY 90;

SELECT /*+ no_vector_index_transform(galaxies) */ name
FROM galaxies
WHERE id<5
ORDER BY VECTOR_DISTANCE( embedding, to_vector('[0,1,1,0,0]'), COSINE )
FETCH FIRST 10 ROWS ONLY WITH TARGET ACCURACY 90;

SELECT /*+ vector_index_transform(galaxies galaxies_hnsw_idx in_filter_with_join_back) */ name
FROM galaxies
WHERE id<5
ORDER BY VECTOR_DISTANCE( embedding, to_vector('[0,1,1,0,0]'), COSINE )
FETCH FIRST 10 ROWS ONLY WITH TARGET ACCURACY 90;

The SQL examples included previously are based on the following:

DROP TABLE galaxies PURGE;
CREATE TABLE galaxies (id NUMBER, name VARCHAR2(50), doc VARCHAR2(500), EMBEDDING VECTOR);
-- or CREATE TABLE galaxies (id NUMBER, name VARCHAR2(50), doc VARCHAR2(500), EMBEDDING VECTOR(5,INT8));
 
INSERT INTO galaxies VALUES (1, 'M31', 'Messier 31 is a barred spiral galaxy in the Andromeda constellation which has a lot of barred spiral galaxies.', '[0,2,2,0,0]');
INSERT INTO galaxies VALUES (2, 'M33', 'Messier 33 is a spiral galaxy in the Triangulum constellation.', '[0,0,1,0,0]');
INSERT INTO galaxies VALUES (3, 'M58', 'Messier 58 is an intermediate barred spiral galaxy in the Virgo constellation.', '[1,1,1,0,0]');
INSERT INTO galaxies VALUES (4, 'M63', 'Messier 63 is a spiral galaxy in the Canes Venatici constellation.', '[0,0,1,0,0]');
INSERT INTO galaxies VALUES (5, 'M77', 'Messier 77 is a barred spiral galaxy in the Cetus constellation.', '[0,1,1,0,0]');
INSERT INTO galaxies VALUES (6, 'M91', 'Messier 91 is a barred spiral galaxy in the Coma Berenices constellation.', '[0,1,1,0,0]');
INSERT INTO galaxies VALUES (7, 'M49', 'Messier 49 is a giant elliptical galaxy in the Virgo constellation.', '[0,0,0,1,1]');
INSERT INTO galaxies VALUES (8, 'M60', 'Messier 60 is an elliptical galaxy in the Virgo constellation.', '[0,0,0,0,1]');
INSERT INTO galaxies VALUES (9, 'NGC1073', 'NGC 1073 is a barred spiral galaxy in Cetus constellation.', '[0,1,1,0,0]');

For HNSW indexes:

CREATE VECTOR INDEX galaxies_hnsw_idx ON galaxies (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

For IVF indexes:

CREATE VECTOR INDEX galaxies_ivf_idx ON galaxies(embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 95;