This study guide covers all official exam topics for Oracle 1Z0-184-25, with in-depth explanations, code examples (SQL, PL/SQL, Python), and practice questions. The guide is organized according to the main subject areas of the exam and emphasizes Oracle’s latest 23c/23ai features and documentation. Use the structured headings and lists to navigate each topic, and refer to the cited Oracle documentation for authoritative details.

1. Understanding Vector Fundamentals (20%)

Oracle Database 23c introduces native vector data capabilities to store and query high-dimensional vector embeddings for AI/ML applications​ oneoracledeveloper.com​docs.oracle.com. This section covers the basics of Oracle’s vector data type, how to use it in tables, how to measure similarity with distance functions, and how to manipulate vector data with SQL/PLSQL (DML and DDL operations).

Vector Data Type in Oracle 23c

• What is the VECTOR data type? – It’s a new data type in Oracle 23c designed for storing vector embeddings, which are arrays of numbers representing unstructured data (text, images, etc.) in a semantic vector space​ docs.oracle.com​docs.oracle.com. Each vector’s position encodes meaning so the content yields nearby vectors (i.e. small distances in the vector space).

• Defining vector columns: You can declare a table column as VECTOR, optionally specifying the number of dimensions and the numeric format. For example:

  CREATE TABLE documents (
doc_id NUMBER,
content CLOB,
embedding VECTOR(768, FLOAT32) -- 768-dimensional vector of 32-bit floats
);


  In the above, every vector stored must have 768 dimensions, each a FLOAT32. You may also omit dimensions/format (using VECTOR or VECTOR(*, *)), which makes the column flexible (can store vectors of varying size/formats)​ docs.oracle.com. However, mixing dimensions in one column is not useful for similarity search (different models’ embeddings aren’t comparable)​ docs.oracle.com.

• Dimension and format constraints: Vectors must have at least 1 dimension (up to 65,535 for non-binary formats) ​docs.oracle.com. Supported element formats are INT8 (8-bit integer), FLOAT32 (32-bit float), FLOAT64 (64-bit float), and BINARY (bit-packed binary) ​docs.oracle.com. For binary vectors, the dimension must be a multiple of 8 (since bits are packed into bytes)​docs.oracle.com.

• Dense vs. Sparse storage: By default, vectors are stored in dense form (all dimensions stored). Oracle also supports sparse vectors, optimized for data with many zeros. Declaring VECTOR(dims, type, SPARSE) will only store non-zero values, saving space when appropriate​docs.oracle.com​docs.oracle.com. (Sparse vec​docs.oracle.com​docs.oracle.come in the same column or be used with BINARY format​docs.oracle.com, and currently cannot be declared as PL/SQL variables​docs.oracle.com.)

• Internal storage: Both dense and sparse vectors are internally stored as SecureFile BLOBs​docs.oracle.com. Typical embedding sizes (hundreds of dimensions) result in a few KB per vector. Oracle provides formulas to estimate storage size for dense and sparse vectors​docs.oracle.com​docs.oracle.comr capacity planning.

• Example – Creating and inserting vectors: Below, we create a simple table with a 3-dimensional INT8 vector and insert a vector value:

  DROP TABLE my_vect_tab PURGE;
CREATE TABLE my_vect_tab (v0 VECTOR(3, INT8));
INSERT INTO my_vect_tab VALUES ('[10, 20, 30]');
SELECT v0 FROM my_vect_tab;


  Result:

  lua

  V0
----------
[10,20,30] -- Stored vector displayed in dense textual form


  In SQL, vectors can be represented textually by a docs.oracle.comted list of values in square brackets​docs.oracle.com​docs.oracle.com. Oracle will automatically cast this text to the VECTOR type. In the example above, '[10, 20, 30]' is inserted as a 3-dimensional vector of INT8.

docs.oracle.com​docs.oracle.com

Note: If a vector column is defined with a fixed dimension, inserting a vector of a different length will raise an error. For instance, a VECTOR(128, FLOAT32) column cannot accept a 256-dimension vector. All vectors in that column must have the same dimensionality​docs.oracle.com. If you define a flexible dimension (using *), you can mix dimensions​docs.oracle.comer that you cannot create a single index on mixed-dimension data​docs.oracle.com (because distance computations require consistent dimensions).

Vector Distance Functions and Similarity Metrics

Once data is stored as vectors, the core operation is measuring the distance or similarity between two vectors. Oracle provides the VECTOR_DISTANCE(vector1, vector2, metric) SQL function to compute a similarity score​docs.oracle.com​dineshbandelkar.com. This function supports several distance metrics (also called similarity metrics) to suit different embedding models:

• Supported metrics: EUCLIDEAN (a.k.a. L2 distance), EUCLIDEAN SQUARED (L2 squared), COSINE (for cosine similarity), DOT (dot product), MANHATTAN (L1 distance), and HAMMING (for binary vectors)​docs.oracle.com. If no metric is specified, the default is COSINE​docs.oracle.com.

  • Cosine similarity is common for text embeddings (measures the angle between vectors, ignoring magnit​blog.marvik.ai implementation likely returns 1 – cos(angle) as a distance (so smaller is more similar) or uses internal transformations such that a lower score means closer.

  • Euclidean is the straight-line distance in the vector space.

  • Dot uses the dot product (often used when embeddings are normalized or when higher dot = more similarity; Oracle treats it as a distance metric for convenience, possibly by ranking larger dot products as “closer”).

  • Manhattan is sum of absolute differences, and Hamming counts bit differences (for binary vectors).

• Choosing the right metric: Use the distance measure recommended by your embedding model blog.marvik.ai. For example, OpenAI’s text embeddings are typically compared with cosine similarity, while some computer vision models might prefer Euclidean or dot product. The distance function in queries must match the metric the index was created with (see Vector Index section) or the index won’t be used docs.oracle.com​docs.oracle.com.

• Example – Using VECTOR_DISTANCE: Suppose docs(embedding VECTOR(768,FLOAT32)) and you have a query vector q_vec (perhaps representing a search query in the same 768-dim space). You can find the top 5 most similar documents by cosine similarity:

  SELECT doc_id, title
FROM docs
ORDER BY VECTOR_DISTANCE(embedding, :q_vec, COSINE)
FETCH FIRST 5 ROWS ONLY;


  This will compute the cosine distance between the embedding and the query vector for each row, then return the 5 with smallest distance (most similar). We use FETCH FIRST N ROWS ONLY to limit results (this is essentially an exact K-nearest neighbors search, scanning data without using an approximate index).

• Performance: Without an index, VECTOR_DISTANCE in a query will compute distances row-by-row (full scan or range scan). This is fine for smaller tables or initial testing. However, for millions of vectors, consider using vector indexes (approximate search indexes) to speed up queries (discussed in the next section).

• Distance calculations in PL/SQL: You can call VECTOR_DISTANCE in PL/SQL contexts as well (e.g., selecting into a variable). Oracle also provides a PL/SQL package DBMS_VECTOR with a utility function DBMS_VECTOR.QUERY to perform similarity search, and even a DBMS_VECTOR.RERANK for refining results​ docs.oracle.com​docs.oracle.com. But for fundamentals, understanding the SQL function is suffic​ techstrongitsm.comML Operations on Vectors (INSERT, UPDATE, DELETE)

• Inserting vectors: As shown earlier, you can insert vector values using the textual [x,y,...] representation or by using the TO_VECTOR function to cast from text/JSON. For example:

  INSERT INTO docs(doc_id, embedding)
VALUES (101, to_vector('[0.12, -0.05, ... 0.87]'));


  This will store the given array as a vector. The TO_VECTOR function can parse a JSON array or the brac​techtarget.comon into the VECTOR type. If the column has a defined dimension and the input doesn’t match it, an error occurs​docs.oracle.com (so ensure your data is in the correct length).

• Updating vectors: Vector columns can be updated like any other column, e.g.:

  UPDATE docs
SET embedding = '[0.10, -0.04, ..., :contentReference[oaicite:40]{index=40}RE doc_id = 101;


  This replaces the old vector with a new one. Under the hood, updating a vector will mark associated vector indexes (if any) for maintenance or accuracy checks. Note that HNSW indexes are static once built (no DML allowed; see Vector I​docs.oracle.comon), and IVF indexes can degrade in accuracy with updates​ blog.marvik.ai. Thus, if you update vector data frequently, you may need to rebuild indexes periodically.

• Deleting vectors: Deleting rows that contain vectors is straightforward with DELETE statements. If a vector index exists on the table, Oracle will handle removing index entries. For IVF, large deletions or insertions might reduce blog.marvik.ai over time ​docs.oracle.com. You can monitor index quality and consider rebuilding if necessary (using DBMS_VECTOR.INDEX_ACCURACY_QUERY and DBMS_VECTOR.REBUILD_INDEX​docs.oracle.com).

• Transaction considerations: Vector data participates in transactions like normal data. All regular ACID properties apply. One thing to be mindful of is consistency of dimensions: if you initially allowed a mix of dimensions by declaring VECTOR(*, *), ensure at application level that you don’t insert inconsistent embeddings by mistake. It’s often best to enforce one dimension per table (either by declaring it or by only using one model for that table).

DDL Operations on Vectors (Table and Index Definitions)

• Creating tables with VECTOR columns: We have seen examples of CREATE TABLE with a vector column. You can also ALTER TABLE to add a vector column. For instanc​oneoracledeveloper.com​docs.oracle.com​docs.oracle.com​techstrongitsm.com​docs.oracle.com(512, FLOAT32);

  This adds a new 512-dimension vector column (perhaps to store face image embeddings for each user). After adding, you can populate it via UPDATE or by reloading data.

   

• Defining constraints: Vector columns can be nullable or not nullable. They can’t be primary keys (since they are complex types), but you could use a primary key on an ID and just store vectors as a regular column. There is no direct “unique” constraint meaning on a high-dimensional vector (and it’s not typically needed).

• Index DDL: Creating vector indexes is a specialized DDL operation covered in the next section (because it’s an exam topic on its own). In short, you use CREATE VECTOR INDEX ... syntax. There are currently no function-based indexes on expressions involving vectors ​docs.oracle.com (you index the vector column itself). Also, only one vector index can exist per vector column​ docs.oracle.com.

• Metadata and dictionary views: After defining vector columns or indexes, you can find them in Oracle’s data dictionary:

  • User/All/DBA_TAB_COLUMNS will show the data type as VECTOR.

  • User/All/DBA_INDEXES will list vector indexes with INDEX_TYPE='VECTOR' and INDEX_SUBTYPE indicating INMEMORY_NEIGHBOR_GRAPH_HNSW or NEIGHBOR_PARTITIONS_IVF​docs.oracle.com.

  • There are also special views like VECSYS.VECTOR$INDEX that store index parameters and status info ​docs.oracle.com.

• Compatibility: To use the vector data type and indexes, the database COMPATIBLE parameter must be 23.4.0 or higher​ docs.oracle.com (for Oracle 23c). Ensure your system is properly upgraded, or if using Autonomous Database, use the “23c” preview which has these features.

Practice Questions (Vector Fundamentals):

1. You create a table with a VECTOR(128, FLOAT32) column. What will happen if you attempt to insert a 256-dimensional vector into this column?

   • Answer: The INSERT will fail with an error (ORA-51801), because the vector’s dimension doesn’t match the column’s defined 128 dimensions docs.oracle.com​docs.oracle.com. Oracle requires all vectors in a fixed-length vector column to have the same number of dimensions.

2. Which of the following distance metrics is the default for VECTOR_DISTANCE if none is specified: (A) EUCLIDEAN, (B) COSINE, (C) DOT, (D) MANHATTAN?

   • Answer: (B) COSINE is the default distance metric if not specified​ docs.oracle.com. (Oracle supports Euclidean, Cosine, Dot, Manhattan, Hamming, etc., but Cosine similarity is used by default for vector searches.)

3. True or False: Oracle’s VECTOR data type can store vectors of different numeric types (e.g. INT8 and FLOAT32) in the same column.

   • Answer: True. If you declare a column as VECTOR(*, *) (no fixed format), Oracle will allow any format and dimension​docs.oracle.com​docs.oracle.com. However, mixing formats is usually discouraged unless necessary, and mixing dimensions will prevent indexing on that column​ docs.oracle.com. In practice, you’d pick one format for consistency.

2. Using Vector Indexes (15%)

For large datasets, vector indexes dramatically speed up similarity searches by avoiding scanning every vector. Oracle 23c supports Approximate Nearest Neighbor (ANN) indexing methods that trade a negligible amount of accuracy for huge gains in query performance ​blog.marvik.ai. There are two vector index types in Oracle: HNSW (Hierarchical Navigable Small World) and IVF (Inverted File). This section explains how to create and use these indexes.

Overview of Oracle Vector Indexes

• Why vector indexes? – A brute-force search computes distance to every vector, which is O(N) per query. Vector indexes use clever data structures to prune the search space, finding the nearest neighbors much faster (sub-linear time). Oracle’s implementations are based on popular ANN algorithms:

  • HNSW builds an in-memory graph of proximity (a navigable “small world” network of vectors).

  • IVF partitions vectors into clusters and searches only the most likely clusters for each query.

• Index types: Oracle categorizes vector indexes into two families​ docs.oracle.com:

  1. In-Memory Neighbor Graph index – this refers to HNSW. It primarily uses memory for the index graph structure, yielding extremely fast lookups at query time (with CPU-bound processing).

  2. Neighbor Partition index – this refers to IVF (specifically IVF Flat). It’s a disk-based index that partitions vectors into “neighbor” groups on storage.

  Oracle’s user guide confirms HNSW is the only supported in-memory graph index, and IVF (flat) is the only supported partition index ​docs.oracle.com. In summary, HNSW and IVF are the two index mechanisms provided.

• General syntax: To create a vector index, use CREATE VECTOR INDEX with the target table and vector column, and specify the type via the ORGANIZATION clause:

  CREATE VECTOR INDEX index_name
ON table_name(vector_column)
ORGANIZATION INMEMORY NEIGHBOR GRAPH -- for HNSW index
[DISTANCE <metric>]
[WITH TARGET ACCURACY <value> PARAMETERS (...)] ;


  or

  ... ORGANIZATION NEIGHBOR PARTITIONS -- for IVF index
...


  The minimal requirement is to specify the column and one of the two organizations (which implies the index type) ​docs.oracle.com. Additional parameters can tune the index (discussed below).

• Distance metric in indexes: You can (and should) specify the distance metric the index will optimize for (Cosine, Euclidean, etc.). If not specified, it defaults to COSINE​docs.oracle.com. The metric must match what you’ll use in queries; otherwise, the index won’t be utilized​docs.oracle.com​docs.oracle.com. For example, if your embeddings are best compared with dot product, create the index with DISTANCE DOT and use VECTOR_DISTANCE(..., DOT) in queries.

• Target accuracy: When building an ANN index, you can set a target accuracy percentage. This is a trade-off knob: 100% means try to find the true nearest neighbors (at cost of more processing), lower values allow more approximation but faster queries​ blog.marvik.ai. By default, Oracle uses 95% if not specified. You can set it in the WITH TARGET ACCURACY <percent> clause during index creation​ oralytics.com. The actual achieved accuracy can be measured after creation (with INDEX_ACCURACY_QUERY) and you can override target accuracy per query if needed (see Similarity Search section).

• Parallel and partitioning: You can create indexes in parallel by adding a PARALLEL n clause like other indexes​ connor-mcdonald.com (useful for large data indexing). Global (table) partitioning of the index is also supported; an index can span partitions of a table globally docs.oracle.com, though you cannot yet create a local partitioned vector index tied to each table partition – it’s essentially global only.

• Restrictions: Vector indexes cannot be created on certain special table types (external tables, clustered tables, GTTs, etc.)​docs.oracle.com. Also, only one vector index per column is allowed​docs.oracle.com (you choose either HNSW or IVF). If you attempt to create a second index on the same vector column, it will error out.

Next, we delve into each index type:

HNSW Index (Hierarchical Navigable Small World Graph)

The HNSW index is an in-memory graph structure for approximate nearest neighbor search:

• Characteristics: HNSW indexes are kept in the vector memory pool (in SGA) and primarily use RAM for query-time speed​ oralytics.com. They organize vectors in layers of a graph where each vector (node) links to its nearest neighbors, forming a “small world” network that can be navigated quickly ​oralytics.com. This algorithm is known for high recall (accuracy) even at low query times – great for real-time search.

• Memory allocation: You must allocate adequate memory for HNSW indexes via the VECTOR_MEMORY_SIZE parameter (this is similar to Oracle’s In-Memory column store concept, but separate)​ oralytics.com. For example, ALTER SYSTEM SET vector_memory_size = 2G SCOPE=SPFILE; then restart, to reserve 2GB for vector indexes. Oracle provides a view V$VECTOR_MEMORY_POOL to check memory usage ​dineshbandelkar.com and a function DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR to estimate needed memory for an index​ docs.oracle.com. Insufficient memory will limit index size (or cause usage of spill-to-disk perhaps).

• HNSW parameters: When creating an HNSW index, you can optionally provide parameters:

  • neighbors <M> – the maximum number of neighbors each node links to (often called M in HNSW literature). Higher M means more connections (higher memory and build time, potentially better accuracy)​ sessionize.com.

  • efConstruction <EF> – the number of neighbors to consider during index construction (controls quality vs. build speed). Higher EF gives better graph quality (and thus search accuracy) but slower index build.

  • If not provided, Oracle will use some defaults (often HNSW defaults might be M=16, EF=100 or similar – exact values are in docs). You must specify type HNSW in the PARAMETERS clause if you include these, e.g. PARAMETERS (type HNSW, neighbors 32, efconstruction 300)​connor-mcdonald.com.

• Creating an HNSW index – example:

  CREATE VECTOR INDEX docs_vec_idx
ON documents(embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95
PARAMETERS (type HNSW, neighbors 40, efconstruction 500);


  oralytics.com​docs.oracle.com

  In this example, we build an HNSW index on the documents.embedding vector column, using Cosine distance, aiming for 95% accuracy. We set HNSW-specific parameters: each vector connects to up to 40 neighbors in the graph, and the index construction will use an efConstruction of 500 for quality. (These are relatively high values, favoring accuracy; defaults might be lower.)

• Usage: After creation, Oracle keeps the HNSW graph in memory. Querying with ... ORDER BY VECTOR_DISTANCE(column, :query_vec, COSINE) FETCH APPROX ... will traverse the graph to find nearest neighbors (see next section for query syntax). Because it’s approximate, some nearest neighbors might be missed, but typically at 95% target accuracy, almost all top results are correct. You can increase accuracy per query (at cost of checking more nodes) by specifying a higher efSearch via a query hint (not shown here, but Oracle allows overriding accuracy in the fetch clause).

• Limitations: HNSW indexes are static once built. No DML (insert/update/delete) is allowed on the base table if an HNSW index exists​ blog.marvik.ai. In fact, Oracle’s documentation indicates you should not perform DML after creating an HNSW index – you’d have to drop and rebuild the index if data changes. This is because maintaining the graph incrementally is complex and not supported in this release. Therefore, HNSW is best for largely read-only datasets or scenarios where you can batch updates and rebuild indexes.

• RAC considerations: HNSW indexes are not available on RAC (Real Application Clusters) in this release​ blog.marvik.ai. They are local to one instance’s memory. If you need multi-instance, you might have to stick to IVF or use a sharded approach with separate indexes on each shard.

IVF Index (Inverted File Flat Index)

The IVF index takes a clustering approach:

• Characteristics: IVF stands for Inverted File (Flat) index. It partitions the vector space into a number of clusters (often via k-means or similar). Each cluster has a centroid, and each vector belongs to one cluster. The index essentially is a list of cluster centroids and an assignment of each vector to a cluster. At query time, the search examines the closest cluster(s) to the query vector and then scans vectors only in those clusters​ oralytics.com​blog.marvik.ai.

• On-disk structure: IVF indexes are stored on disk (and use temporary space heavily during creation)​ docs.oracle.com​docs.oracle.com. They can handle larger datasets that don’t fit entirely in memory, at the cost that querying might involve disk I/O (though Oracle can cache frequently accessed parts). They are well-suited for very large vector collections.

• IVF parameters: Key tuning parameters for IVF:

  • neighbor_partitions <P> – This specifies the number of clusters (partitions) to create or to search. For example, neighbor partitions 100 might create 100 clusters of vectors oralytics.com. A higher number of partitions means each cluster is smaller (faster search within cluster) but you might need to search more clusters to find neighbors if the nearest neighbor isn’t in the top one.

  • Oracle’s interface is a bit confusing here: in some examples, they create the index with ORGANIZATION NEIGHBOR PARTITIONS ... PARAMETERS (type IVF, neighbor partitions 10). The neighbor partitions in PARAMETERS might indicate the number of clusters to explore for a query (like how many partitions to check). It’s possible that by default the index chooses an internal number of clusters and that parameter might override search behavior. However, Oracle docs suggest it’s the number of clusters in the index when building (likely equal to the number of partitions/clusters)​ oralytics.com.

  • There might also be a concept of “centroids to search” at query time, which could be adjusted via the WITH TARGET ACCURACY or by query override. (In some ANN libraries, you specify how many clusters to visit per query, e.g. search top-5 clusters out of 100.)

• Creating an IVF index – example:

  CREATE VECTOR INDEX docs_vec_ivf_idx
ON documents(embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 90
PARAMETERS (type IVF, neighbor partitions 10);


  oralytics.com

  This builds an IVF index on documents.embedding, using Cosine distance. It sets target accuracy 90% (faster but slightly less accurate than default). The parameters here might indicate using 10 partitions (clusters). With only 10 clusters, each cluster will have many points, but the query will only search a subset of those clusters to find neighbors – likely it will look at a few of the nearest clusters to the query.

• DML on IVF: Unlike HNSW, IVF indexes do allow DML on the base table, but with caveats. If you insert new vectors, they will be indexed (likely Oracle assigns them to clusters on the fly). If you delete or update, those changes reflect in the index. However, as DML occurs, the quality of the index can degrade ​docs.oracle.com. For example, new vectors might all accumulate in one cluster until a rebalance is done. Oracle provides DBMS_VECTOR.INDEX_ACCURACY_QUERY to check if the index is still performing well​docs.oracle.com. If accuracy drops, you can rebuild the index (ALTER INDEX ... REBUILD or via DBMS_VECTOR) to re-cluster the data properly. Also note: truncating the table invalidates an IVF index (marks it unusable) – you’d need to rebuild after a truncate​ docs.oracle.com.

• Persistence: Because IVF is on-disk, it persists across database restarts (unlike HNSW which would need to be rebuilt or reloaded into memory each time unless some checkpointing is used). This makes IVF suitable for scenarios where you can’t afford to rebuild indexes on every startup or where the data is truly large.

• Index size and storage: Oracle recommends having a large TEMP tablespace for building IVF on big datasets​docs.oracle.com, as the process likely uses sorting/clustering algorithms that use temp space. The final index is stored in the database (likely as index segments and some BLOB metadata). You can inspect USER_INDEXES or VECSYS.VECTOR$INDEX to see index subtype = NEIGHBOR_PARTITIONS_IVF and possibly parameters like number of partitions etc.​ docs.oracle.com​dineshbandelkar.com.

• Query performance: IVF can scale to millions of vectors. Querying typically involves: compute the query’s cluster (or nearest few clusters), fetch those cluster’s members (which may involve reading from disk if not cached), and then computing exact distances for those. The more partitions (clusters) you have, the fewer vectors per cluster (fast scan), but if you choose too few clusters to search, you might miss some results. Oracle’s target accuracy parameter likely controls how many clusters to consider. E.g., 90% accuracy might search fewer clusters than 99%. You can adjust per query if needed (see next section).

Using Vector Indexes in Queries

Creating the index is half the battle – you must also query in a way that uses the index:

• Approximate search syntax: Oracle uses an extension of the SQL FETCH clause to trigger index usage. You append FETCH APPROXIMATE FIRST <N> ROWS ONLY (or the shorthand FETCH APPROX) to your query​ docs.oracle.com​docs.oracle.com. This tells Oracle optimizer to consider the ANN index for retrieving the top N similar items. For example:

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


  docs.oracle.com

  Here, FETCH APPROXIMATE FIRST 3 ROWS ONLY signals an ANN search for the 3 nearest neighbors by cosine distance, using any available vector index on embedding. If an index with matching metric exists (as created in the example above), the index will be used to satisfy the query quickly​docs.oracle.com. If no index, Oracle would ignore the APPROXIMATE and just do a full sort (so it’s backward compatible – but obviously slower).

  Important: The APPROXIMATE (or APPROX) keyword is required to get the performance benefit​docs.oracle.com. Without it, Oracle will do an exact sort of all distances. The presence of APPROXIMATE tells the optimizer it can do a “top-N approximate” using the index.

• Ensuring index usage: In summary, to use a vector index:

  1. Include APPROX/APPROXIMATE in the fetch clause​docs.oracle.com.

  2. Use the same distance function as index metric​docs.oracle.com (mismatch = no index usage).

  3. Do not wrap the VECTOR_DISTANCE in another function or arithmetic – it must be a simple ORDER BY on the distance​docs.oracle.com.

  4. Don’t use certain SQL features that inhibit index usage (e.g., partitioned row filters, or mixing exact and approximate logic in a complex way)​docs.oracle.com.

  If these conditions are met, Oracle’s plan should show a VECTOR INDEX RANGE SCAN or similar in the explain plan​dineshbandelkar.com​dineshbandelkar.com.

• Overriding accuracy: If you want to override the index’s default accuracy or search parameters at query time, Oracle lets you specify WITH TARGET ACCURACY <p> or even WITH TARGET ACCURACY PARAMETERS (...) in the fetch clause​docs.oracle.com​docs.oracle.com. For example:

  ... FETCH APPROXIMATE FIRST 3 ROWS ONLY WITH TARGET ACCURACY 90;


  This will use a 90% target accuracy for this query (maybe searching fewer neighbors or clusters). Or:

  ... FETCH APPROXIMATE FIRST 3 ROWS ONLY
WITH TARGET ACCURACY PARAMETERS (efsearch 500);


  In HNSW, efSearch is a query-time parameter (how many candidates to evaluate). Oracle allows specifying it like this​docs.oracle.com. Increasing efsearch can improve recall at cost of more work per query.

• Multi-threaded search: It’s not explicitly documented, but since you can create the index with parallel degree, Oracle might also search in parallel threads if you hint parallelism or if it internally does for large top-K. This might not be needed often, as the index search is very fast single-threaded, but keep in mind for huge data.

• Maintaining index quality: Over time, if you’ve done DML (especially on IVF indexes), periodically verify the index accuracy. You can run:

  DECLARE
rep VARCHAR2(4000);
BEGIN
rep := DBMS_VECTOR.INDEX_ACCURACY_QUERY(
owner_name => 'MYSCHEMA',
index_name => 'DOCS_VEC_IVF_IDX',
qv => :some_query_vector,
top_K => 10,
target_accuracy => 90 );
DBMS_OUTPUT.put_line(rep);
END;


  This will output a JSON or string report of accuracy (it compares an exact top-10 vs indexed top-10)​oralytics.com. If it shows significantly less than target accuracy, consider rebuilding the index:

  ALTER INDEX docs_vec_ivf_idx REBUILD;


  or using DBMS_VECTOR.REBUILD_INDEX. Rebuilding will refresh clustering without needing to drop/recreate.

• Resource usage: Keep an eye on memory usage for HNSW. Use V$VECTOR_MEMORY_POOL to see how much of the vector pool is used by each index​dineshbandelkar.com. If you have multiple HNSW indexes and limited memory, you might need to size the pool accordingly (or consider IVF if memory is a constraint).

• One index per query: Oracle currently does not combine multiple vector indexes in a single query. If you had two different vector columns, you’d query them separately. There is no “index merge” or such (it wouldn’t make sense to do multi-index merge for a single similarity metric). So each query uses at most one vector index. Plan accordingly (usually you have one main embedding column per table to index).

Practice Questions (Vector Indexes):

1. You created an HNSW vector index on a table. What must you include in your SELECT query to ensure the index is used for faster search?

   • Answer: You must include the APPROXIMATE (or APPROX) keyword in the FETCH FIRST ... ROWS ONLY clause of the query​docs.oracle.com. For example: ... ORDER BY vector_distance(... ) FETCH APPROXIMATE FIRST N ROWS ONLY. This signals the optimizer to use the HNSW index for an approximate search instead of doing a full sort.

2. Which statement is TRUE about HNSW vs IVF indexes in Oracle?
   A. HNSW indexes are stored on disk and allow efficient inserts/updates.
   B. IVF indexes partition vectors into clusters and can degrade in accuracy after many updates.
   C. HNSW indexes can be used on RAC instances for distributed in-memory search.
   D. IVF indexes cannot be rebuilt without dropping and recreating the index.

   • Answer: B is true. IVF (Inverted File) indexes cluster the data​oralytics.com, and their accuracy can degrade with lots of DML (thus requiring periodic rebuilds)​docs.oracle.com. Option A is false (HNSW is in-memory, not on disk, and does not support inserts/updates without rebuild​blog.marvik.ai). C is false (HNSW is not supported on RAC in the current release​blog.marvik.ai). D is false (IVF indexes can be rebuilt in-place with ALTER INDEX ... REBUILD to refresh clustering).

3. After creating an IVF index with TARGET ACCURACY 95, you want a particular query to run faster at maybe 80% accuracy. How can you achieve that?

   • Answer: Specify a lower accuracy in the query’s FETCH clause. For example: FETCH APPROXIMATE FIRST 5 ROWS ONLY WITH TARGET ACCURACY 80​docs.oracle.com. This will override the index’s default accuracy for that query, likely reducing the number of clusters or vectors searched, thereby increasing speed (at the risk of slightly less accuracy).

3. Performing Similarity Search (15%)

This section covers how to execute similarity searches in Oracle – both exact searches (for full accuracy) and approximate searches using the indexes – as well as performing multi-vector similarity search (searching with multiple query vectors or ensuring results cover multiple categories/documents).

Exact vs. Approximate Similarity Search

• Exact similarity search: An exact search finds the true nearest neighbors by computing all distances. In Oracle, you do this by not using the APPROXIMATE keyword. For example:

  SELECT id, title
FROM docs
ORDER BY VECTOR_DISTANCE(embedding, :qvec, COSINE)
FETCH FIRST 10 ROWS ONLY;


  This will sort the entire table by distance to :qvec and return the top 10. It yields exact results (the true 10 closest vectors)​blog.marvik.ai. Under the hood, Oracle will likely do a full-table scan and sort (or use a partial sort / top-N sort algorithm). Exact search is acceptable for smaller tables or when 100% precision is needed and performance is manageable. However, it doesn’t scale well to very large datasets (millions of embeddings), which is why approximate indexes are introduced.

• Approximate similarity search: As discussed, to do an ANN search, you use the FETCH APPROXIMATE clause with an index in place. For example:

  SELECT id, title
FROM docs
ORDER BY VECTOR_DISTANCE(embedding, :qvec, COSINE)
FETCH APPROXIMATE FIRST 10 ROWS ONLY;


  This will use the vector index (HNSW or IVF) to quickly retrieve 10 neighbors​docs.oracle.com. The results should be the 10 nearest or very close to it, but there is a small chance some neighbors are not the absolute closest if the index is not at 100% accuracy. In practice, at high target accuracy (90-95%), the returned set is usually identical to the exact set. The benefit is speed – these queries are much faster for large data (sub-second even if the table has millions of rows, depending on hardware and index parameters).

• When to use which: If your data volume is low (say thousands of vectors) or if absolute precision is required (and time is not critical), you can run exact searches. If you have large data (hundreds of thousands or more) or need very fast response (real-time applications), use approximate search with indexes. Oracle allows mixing approaches too – for instance, you could use an approximate search to get say top 100, then re-rank them exactly (though Oracle provides a DBMS_VECTOR.RERANK to do something like that if needed​docs.oracle.com​docs.oracle.com).

• Quality of ANN results: You can measure how good the approximate results are by checking recall (the percentage of true neighbors found). Oracle’s INDEX_ACCURACY_QUERY essentially does this by comparing to a brute-force result​docs.oracle.com. For mission-critical use (like if slightly wrong nearest neighbor could cause an issue), test with some queries to ensure the approximation is acceptable. Often a slight drop (e.g., missing 1 out of top 10) is tolerable in AI applications.

• Combining with filters: You can combine similarity search with other predicates. For example:

  SELECT product_id, name
FROM products
WHERE category = 'Electronics'
ORDER BY VECTOR_DISTANCE(embedding, :vec, DOT)
FETCH APPROXIMATE FIRST 5 ROWS ONLY;


  This will first filter to Electronics, then do an approximate top-5 by dot product. The vector index can still be used if it supports a filter (likely the index is applied after the filter in execution). Oracle’s optimizer might apply the category filter first (using normal index if available on category), then the vector index for nearest neighbors among that subset. If the subset is still large, the vector index helps; if the subset is tiny, it might just do exact.

• Hybrid searches: A hybrid query uses both vector similarity and standard conditions, possibly balancing them in a scoring. Oracle 23c also introduced “hybrid search” where you can combine vector distance with text keyword search. For example, you might want items that are semantically similar but also contain a certain keyword. Oracle’s documentation mentions Hybrid Search as a concept (combining vector and relational filters or text)​docs.oracle.com. While not explicitly in exam topics, just know that you can incorporate vector search into SQL queries like any other function – any normal WHERE clause can coexist with the ORDER BY vector_distance.

Multi-Vector Similarity Search

Multi-vector search refers to querying with multiple query vectors or ensuring the results are diversified across different query criteria. In Oracle’s context, one prominent use-case is multi-document search – for example, in a document retrieval scenario, you might want the top relevant piece from each document rather than all top pieces from the same document.

• Use-case: Imagine you have a table of document chunks with a column doc_id (identifying which document the chunk came from) and chunk_embedding. A normal similarity search might return multiple chunks from the single most similar document. But if the goal is to find the most relevant documents (not just chunks), you’d want only the top chunk per document, and then to take the best among those. This is where multi-vector (or multi-group) search comes in.

• Oracle’s approach: Oracle allows a FETCH ... *PARTITIONS BY* ... syntax to facilitate this. This is essentially using the SQL 2012 standard “top-N per group” feature with an extension for approximate. Specifically:

  SELECT doc_id, chunk_id, chunk_data
FROM doc_chunks
ORDER BY VECTOR_DISTANCE(chunk_embedding, :qvec, COSINE)
FETCH FIRST 2 PARTITIONS BY doc_id 1 ROWS ONLY;


  dineshbandelkar.com

  In this query, PARTITIONS BY doc_id 1 ROWS ONLY means: take the closest chunk per doc_id (per document), i.e., partition the data by doc_id and within each partition find 1 closest row. Then, out of those partition winners, return the first 2 partitions (so 2 documents). This effectively gives the top chunk from the top 2 documents relevant to the query​dineshbandelkar.com.

  Oracle’s documentation calls this multi-vector similarity search​docs.oracle.com, but it’s basically a SQL way to enforce diversity (one result per group). The term “multi-vector” might also be interpreted as using multiple query vectors at once (e.g., if you had multiple criteria vectors and wanted results that are close to all), but the exam description specifically says “for multi-document search” which aligns with the partitioned fetch approach.

• How it works: The FETCH FIRST N PARTITIONS BY col, M ROWS ONLY clause is a powerful extension. It finds the top M rows for each distinct value of col (here doc_id), then returns the top N of those groups according to the overall ORDER BY. In the above example, M=1 (one chunk per doc) and we take N=2 partitions (so 2 docs). This ensures no document is represented more than once in the results, and we get the two most relevant documents.

• Example scenario: Suppose Document A has chunks that rank 1st, 2nd, 5th closest to the query, and Document B has its closest chunk ranking 3rd, Document C’s best is 4th. A normal top-5 would give: A1, A2, B1, C1, A3. But using the partition approach for top 3 documents (N=3, M=1) would give: A1, B1, C1 – the best chunk of A, best of B, best of C. This way, you cover more documents in the top results. This is extremely useful for RAG (Retrieval Augmented Generation), because you often want to feed the LLM information from different sources, not five nearly-duplicate chunks from one source.

• Multi-query vectors: Another interpretation: If you literally have multiple query vectors (say you want items similar to either of two different vectors), you could query them separately and merge results. Oracle doesn’t directly support multiple vector inputs in one VECTOR_DISTANCE call (it compares pairwise). But you could do something like:

  SELECT item_id,
LEAST( VECTOR_DISTANCE(emb, :qvec1, COSINE),
VECTOR_DISTANCE(emb, :qvec2, COSINE) ) as score
FROM items
ORDER BY score
FETCH FIRST 10 ROWS ONLY;


  This finds items that are close to either query1 or query2 by taking the min distance as a score. This is a form of multi-vector query too, though not as commonly needed. It might not use indexes effectively (and is not an official feature), but conceptually it’s possible if needed.

• Summary: Multi-vector similarity search in the context of the exam is about retrieving results across multiple categories (like documents) by using the SQL partition-by trick​dineshbandelkar.com. It’s a unique feature that not all databases have natively, and it’s very handy for diversifying results.

• Performance considerations: The PARTITION BY ... in the FETCH clause likely means Oracle will use the vector index to find nearest chunks, then within each doc it keeps track of the best. It might internally iterate through nearest neighbors and pick one per doc until it has N docs collected. This could be slightly more overhead than a plain top-N search, but still far faster than doing separate searches per doc. It’s a single query handled by the engine.

Practice Questions (Similarity Search):

1. You have 10 million vectors and want to find the top 5 most similar to a query vector. What is the most efficient way in Oracle to do this?

   • Answer: Create a vector index (HNSW or IVF) on the data and perform an approximate similarity search using ORDER BY VECTOR_DISTANCE(...) FETCH APPROXIMATE FIRST 5 ROWS ONLY​docs.oracle.com. This will use the index to quickly find the 5 nearest neighbors, which is far more efficient than scanning all 10 million vectors.

2. In a document retrieval scenario, how can you ensure that your query returns at most one result per document, even if one document has many top relevant chunks?

   • Answer: Use a multi-vector (partitioned) similarity search with the FETCH FIRST ... PARTITIONS BY document_id ... clause​dineshbandelkar.com. For example, FETCH FIRST 3 PARTITIONS BY doc_id 1 ROWS ONLY will give the top 1 chunk from each of the top 3 documents, ensuring diversity of documents in the results.

3. True or False: Adding FETCH APPROXIMATE to a similarity query will always return the exact same top results as an exact search.

   • Answer: False. FETCH APPROXIMATE uses ANN indexes and might return slightly different results if it sacrifices some accuracy for speed. Usually at high target accuracy it’s the same, but it’s not guaranteed to match exactly unless target_accuracy is 100% (and even then, some edge cases). The purpose is to allow a slight difference for much faster performance​blog.marvik.ai.

4. Using Vector Embeddings (15%)

This section addresses how to generate and manage the vector embeddings themselves – both outside the database (using external ML models or services) and inside the database (using Oracle’s built-in capabilities). We also cover best practices for storing these embeddings in Oracle and tools for loading them.

Generating Vector Embeddings Outside Oracle

Often, embeddings are produced by machine learning models outside of the database. For example, you might use Python libraries or AI services to convert text or images into vectors:

• Common libraries/services: You can use frameworks like PyTorch or TensorFlow (with pretrained models), libraries like Hugging Face Transformers, or cloud APIs (OpenAI, Azure Cognitive Services, etc.) to generate embeddings. For text, OpenAI’s GPT or Ada models can produce 1536-dim embeddings; for images, models like CLIP or ResNet can produce vectors; for recommendations, you might have custom models.

• Process: Typically:

  1. Extract or prepare the data you want to embed (e.g. sentences, product descriptions, images).

  2. Feed them into the ML model to get embedding vectors (numpy arrays or Python lists).

  3. Load those vectors into the Oracle database (via inserts or bulk loading).

• Example (Python with HuggingFace):

  from transformers import AutoTokenizer, AutoModel
import torch
# Load a pre-trained model for embeddings (e.g., sentence transformers)
tokenizer = AutoTokenizer.from_pretrained('sentence-transformers/all-MiniLM-L6-v2')
model = AutoModel.from_pretrained('sentence-transformers/all-MiniLM-L6-v2')

text = “Oracle Database 23c introduces vector search.”
inputs = tokenizer(text, return_tensors=‘pt’)
with torch.no_grad():
embeddings = model(**inputs).last_hidden_state.mean(dim=1).numpy() # get sentence embedding
vec = embeddings[0] # numpy array

# Connect to Oracle and insert
import oracledb # (cx_Oracle’s new name)
conn = oracledb.connect(user=“usr”, password=“pwd”, dsn=“db_host/db_service”)
cur = conn.cursor()
# Convert numpy array to list of floats for insertion:
vector_list = vec.tolist()
# Insert into a table with a VECTOR column
cur.execute(“INSERT INTO my_vectors(id, embedding) VALUES (:1, to_vector(:2))”,
[101, str(vector_list)]) # converting list to string like “[0.123, 0.456, …]”
conn.commit()