Enhance Skills Daily

March 10, 2026  ·   ·   ·   ·   ·   ·   ·   ·   ·   · 

Oracle AI Vector Search

Oracle AI Vector Search - Deep Dive

Oracle AI Vector Search
Store, Index & Query Embeddings in SQL

This post covers Oracle AI Vector Search from the ground up — the VECTOR data type, distance functions, HNSW and IVF indexes, and loading ONNX embedding models directly into the database. All SQL examples are tested on 23ai. Where a feature requires a specific patch level, that is noted inline.

01 Why Vector Search — and Why Now?

Traditional SQL databases have excelled at exact-match queries for decades. WHERE ticket_text LIKE '%network error%' works — but only when the user types those exact words. In reality, users say "can't connect to the internet", "VPN keeps dropping", or "no signal on ethernet". A keyword search misses all three. Full-text search improves matters somewhat with stemming and stop words, but it still operates on the lexical layer — the characters on the page — not on meaning.

Large language models changed this equation. They can map any piece of text (or image, or audio) into a point in a high-dimensional numerical space in a way that preserves semantic relationships. The challenge for Oracle DBAs has been: where do those vectors live? Historically the answer was a separate vector database — Pinecone, Weaviate, Chroma, pgvector — introducing a second system to operate, synchronize, monitor, and back up alongside the relational database where the actual business data resides.

🔑 Key Insight

Oracle AI Vector Search eliminates the two-system problem. Vectors are stored natively in the same tablespace as your ORDER, EMPLOYEE, and TICKET tables. You get transactional consistency, unified backup, a single connection pool, and the full SQL surface — all in one engine.

🎫
Support Ticket Routing
Find the most similar past tickets to auto-assign priority and team without exact keyword match.
📦
Product Recommendations
Embed product descriptions and query by customer-phrased intent rather than category codes.
📄
RAG (Retrieval-Augmented Generation)
Retrieve relevant document chunks from Oracle, inject into an LLM prompt for grounded answers.
🔍
Semantic Document Search
Search contracts, policies, and reports by meaning — "what are the termination clauses?" — not just text.

02 What Are Vector Embeddings?

An embedding is a dense numerical vector produced by passing input data through a trained neural network. For text, a model reads a sentence, processes it through transformer layers with attention mechanisms, and outputs a list of floating-point numbers — the embedding vector. The number of values in that list is the dimensionality. Common sizes range from 384 dimensions (smaller, faster models) through 768, 1024, and up to 1536 or more for high-capacity models.

The Geometric Intuition

Think of each embedding as a coordinate in a very large room. Words or concepts that are semantically related end up physically close together in that room. "Dog" and "Wolf" share traits — carnivore, fur, pack behaviour — so their embeddings land near each other. "Dog" and "Carburetor" have almost no shared context, so they end up far apart.

This also works across languages in multilingual models. "Chien" (French for dog) ends up near "Dog" in the vector space even though the two words share no characters.

🐕 Dog
384-dim · FLOAT32
🐺 Wolf
384-dim · FLOAT32 · distance ≈ 0.08
🚗 Car
384-dim · FLOAT32 · distance ≈ 0.72
📘 Going Deeper

The math behind embeddings is called contrastive learning. During training, the model sees pairs of sentences that should be similar and pairs that should not be. The loss function — usually cosine similarity loss or triplet loss — nudges the weights so similar pairs produce closer vectors. After enough training data, the model generalises surprisingly well to text it has never seen.

Dimensionality and Quality

Bigger is not always better. Past a certain point all distances start looking similar and nearest-neighbour search gets less useful — this is the curse of dimensionality. A well-tuned 768-dim model will often beat a larger one used carelessly. Test a few models on your own data; the MTEB benchmark is a good starting point for comparison.

// Text → Embedding Pipeline
📝 Raw Text "network error"
→
✂️ Tokenizer WordPiece / BPE
→
🧠 Transformer Attention layers
→
📊 Pooling Mean / CLS token
→
🔢 VECTOR [0.21, -0.07, ...]

03 The VECTOR Data Type

Oracle 23ai adds a native VECTOR column type. When you declare one, you specify the number of dimensions, the numeric format, and optionally whether the storage is DENSE or SPARSE. Most sentence-transformer models output dense vectors, so that is what the examples below use.

SQL · VECTOR Declaration Syntax
-- Full syntax
VECTOR(dimensions, format [, storage])

-- Examples
embedding  VECTOR(384,  FLOAT32, DENSE)   -- 384-dim, 4 bytes/dim = 1536 bytes
embedding  VECTOR(1536, FLOAT32, DENSE)   -- 1536-dim dense text embedding
embedding  VECTOR(768,  FLOAT64)   -- higher precision, 6144 bytes
embedding  VECTOR(512,  INT8)      -- quantised, 512 bytes
embedding  VECTOR(256,  BINARY, DENSE)    -- bit-packed, 32 bytes (23.5+)
features   VECTOR(100000, FLOAT32, SPARSE) -- sparse model output; store only non-zero entries
embedding  VECTOR               -- unconstrained: any dim & format per row

Format Reference

Format Bytes / dim Value Range Introduced Best For
FLOAT32 4 IEEE 754 single 23.4 Most models — good balance of size and precision
FLOAT64 8 IEEE 754 double 23.4 High precision — scientific workloads
INT8 1 −128 to 127 23.4 Quantised — 4× smaller, slight accuracy loss
BINARY 1/8 (bit) 0 or 1 23.5 Ultra-compact — dims must be multiples of 8
⚠️ Storage Constraint

A VECTOR(1536, FLOAT32) column occupies 6,144 bytes per row. For 10 million rows that is roughly 58 GB for the vector data alone — before any row overhead. Plan your tablespace sizing accordingly and consider whether INT8 quantisation is acceptable for your accuracy requirements.

Practical note

For standard sentence-transformer models like MiniLM, DENSE FLOAT32 is the safe default. Only use SPARSE if your model actually outputs sparse vectors — most don't, and switching the storage type on a dense model does nothing useful.

Declaring VECTOR with no parameters creates an unconstrained vector column. The database imposes no restriction on dimension count or numeric format — each row can hold a vector of different dimensionality and a different element type. This is very useful during prototyping when you are trying multiple embedding models (e.g. comparing all-MiniLM-L6 at 384 dims with BGE-large at 1024 dims) and want to store both in the same table temporarily.

However, for production tables you should always specify the dimension and format. Unconstrained columns cannot use vector indexes (HNSW or IVF), since those require all vectors to share the same dimensionality. Any attempt to CREATE VECTOR INDEX on an unconstrained column will return an error.

Specifying dimensions at DDL time also prevents runtime errors. If you call a distance function on two unconstrained vectors with different dimensionalities, Oracle throws an error at execution time. Locking down the column type catches that problem at insert time instead.

Creating a Table with VECTOR Columns

SQL · DDL
-- Support tickets with semantic embeddings
CREATE TABLE support_tickets (
  ticket_id      NUMBER         GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  created_at     TIMESTAMP      DEFAULT SYSTIMESTAMP,
  customer_id    NUMBER         NOT NULL,
  priority       VARCHAR2(10)   CHECK (priority IN ('LOW','MEDIUM','HIGH','CRITICAL')),
  subject        VARCHAR2(500)  NOT NULL,
  body           CLOB,
  resolution     CLOB,
  subject_vec    VECTOR(384, FLOAT32),   -- embedding of subject
  body_vec       VECTOR(384, FLOAT32)    -- embedding of full body text
);

-- Partition for very large datasets
ALTER TABLE support_tickets
  MODIFY PARTITION BY RANGE (created_at)
  INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  (PARTITION p_before VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')));

04 Distance Metrics Deep Dive

Getting the metric wrong is a common mistake. You can pick the wrong one and still get valid SQL results — just much worse search quality. Oracle 23ai supports six metrics. The first five come up in almost every project. The sixth, Jaccard, is mainly relevant when you start working with binary vector encodings.

COSINE
1 − (A·B / |A||B|)
Measures the angle between two vectors, ignoring magnitude. Scale-invariant — a sentence repeated three times has the same cosine distance as the original.
✓ Best for text embeddings
Operator: <=>
EUCLIDEAN
√Σ(Aᵢ − Bᵢ)²
Straight-line distance in the embedding space. Sensitive to magnitude — useful when vector norms carry meaning (e.g. confidence levels).
✓ Good for dense embeddings
Alias: L2_DISTANCE · Operator: <->
DOT
−(A · B)
Negated inner product. For normalised vectors it equals cosine. Unnormalised vectors incorporate magnitude — can represent relevance scores.
✓ For models that output normalised vecs
Alias: INNER_PRODUCT · Operator: <#>
MANHATTAN
Σ|Aᵢ − Bᵢ|
Sum of absolute per-dimension differences. More robust to outliers than Euclidean but less common for text.
✓ Robust to extreme dimensions
Alias: L1_DISTANCE
HAMMING
count(Aᵢ ≠ Bᵢ)
Counts the number of differing bits. Only meaningful for BINARY vectors. Extremely fast — a single XOR + POPCNT instruction.
✓ Default for BINARY format
BINARY vectors only
JACCARD
1 - (|A ∩ B| / |A ∪ B|)
Measures set overlap — how many bits are shared versus total active bits. Mainly useful with BINARY vectors where you care about which dimensions are set, not by how much.
Best for binary/set overlap
No shorthand operator
Metric Matching Rule

Make sure the metric you use in VECTOR_DISTANCE matches what you specified when creating the index, and what the embedding model's documentation recommends. Mixing them gives you valid SQL but noticeably worse search results.

SQL · VECTOR_DISTANCE Usage
-- Explicit function form (recommended for clarity)
SELECT ticket_id, subject,
       VECTOR_DISTANCE(subject_vec, :query_vec, COSINE) AS dist
FROM   support_tickets
ORDER BY dist
FETCH FIRST 10 ROWS ONLY;

-- Shorthand operator equivalents
ORDER BY (subject_vec <=> :query_vec)    -- COSINE
ORDER BY (subject_vec <-> :query_vec)    -- EUCLIDEAN
ORDER BY (subject_vec <#> :query_vec)    -- DOT (negated)

-- Scalar function aliases
COSINE_DISTANCE(v1, v2)
L2_DISTANCE(v1, v2)         -- same as EUCLIDEAN
L1_DISTANCE(v1, v2)         -- MANHATTAN
INNER_PRODUCT(v1, v2)       -- NOT negated — higher = more similar
⚠️ DOT vs INNER_PRODUCT

VECTOR_DISTANCE(..., DOT) returns the negated dot product, so that ordering ASC still yields most-similar first. The scalar function INNER_PRODUCT(v1, v2) returns the un-negated value — higher means more similar. Do not mix the two in the same ORDER BY clause without being explicit.

05 Similarity Search SQL Patterns

Exact Nearest-Neighbour (kNN)

A full-table scan that guarantees the true top-K closest vectors. Use during development or for small tables (under a few hundred thousand rows).

SQL · Exact kNN
SELECT
  t.ticket_id,
  t.subject,
  t.priority,
  VECTOR_DISTANCE(t.subject_vec, :query_vec, COSINE) AS similarity_dist
FROM   support_tickets t
WHERE  t.priority = 'HIGH'            -- combine with any predicate
ORDER BY similarity_dist
FETCH FIRST 5 ROWS ONLY;

Approximate Nearest-Neighbour (ANN) — Using a Vector Index

Adding APPROXIMATE tells the optimizer to use a vector index if one exists. You give up a small amount of recall in exchange for much faster queries on large tables — and you can control exactly how much via TARGET ACCURACY.

SQL · Approximate kNN (ANN)
SELECT
  t.ticket_id,
  t.subject,
  t.priority,
  VECTOR_DISTANCE(t.subject_vec, :query_vec, COSINE) AS dist
FROM   support_tickets t
ORDER BY dist
FETCH APPROXIMATE FIRST 10 ROWS ONLY
WITH TARGET ACCURACY 95;        -- aim for 95% recall; range 50–100
💡 TARGET ACCURACY

WITH TARGET ACCURACY controls how hard the index works on each query. Set it to 95 and Oracle probes more of the graph, returning results very close to what an exact scan would give. Drop it to 80 and queries are faster but you may miss a few results. The default when you omit the clause is 80.

Hybrid Search — Vectors + Relational Predicates

Because the vectors sit in the same database as your relational data, you can mix semantic search with regular WHERE clauses in a single query. Oracle's optimizer handles both together, so filtering by customer or date range happens before — not after — the vector scan.

SQL · Hybrid Semantic + Relational
-- Semantic search scoped to a customer and date range
SELECT
  t.ticket_id,
  t.subject,
  t.resolution,
  VECTOR_DISTANCE(t.body_vec, :q_vec, COSINE) AS dist
FROM   support_tickets t
WHERE  t.customer_id = :cust_id
  AND  t.created_at  >= ADD_MONTHS(SYSDATE, -6)
  AND  t.resolution  IS NOT NULL          -- already resolved tickets only
ORDER BY dist
FETCH APPROXIMATE FIRST 5 ROWS ONLY
WITH TARGET ACCURACY 90;

Returning a Formatted Vector

SQL · Inspecting a VECTOR Value
-- Convert to a human-readable string (first 5 dims shown)
SELECT
  ticket_id,
  VECTOR_SERIALIZE(subject_vec RETURNING CLOB) AS vec_json
FROM   support_tickets
WHERE  ticket_id = 1001;

-- Result example:
-- [0.214782,-0.073412,0.481920,...] (384 values)

-- Check dimensionality
SELECT VECTOR_DIMS(subject_vec)    AS dims,
       VECTOR_NORM(subject_vec)    AS l2_norm
FROM   support_tickets
WHERE  ticket_id = 1001;

06 Vector Index Types: HNSW vs IVF

Oracle gives you two index types for approximate search. They work very differently and the right choice depends mainly on how much SGA memory you have available.

HNSW
Hierarchical Navigable Small World
Storage In-memory (SGA — requires VECTOR_MEMORY_SIZE)
Speed Fastest query latency
Recall Very high (95–99% @ ACCURACY 95)
Build time Slower initial build; incremental inserts supported
Best for High-throughput search where the dataset fits in SGA memory
IVF
Inverted File Flat
Storage Disk-based (no SGA requirement)
Speed Slower than HNSW, but scalable
Recall Good; depends on nlist/nprobe settings
Build time Faster build (k-means clustering phase)
Best for Very large datasets that exceed available SGA memory
Which one to pick

Start with HNSW if you can afford the SGA memory — it's faster at query time and easier to reason about. Switch to IVF when your corpus is too large to fit in memory, or when build time is a constraint. Either way, test both against an exact kNN query first so you have a recall baseline.

How HNSW Works — A Graph Traversal

HNSW builds a multi-layered graph. The top layer is sparse with a small number of long-range connections — think of them as motorway links between cities. Lower layers get progressively denser. At query time, Oracle starts at the top, finds the closest node it can, drops down a layer, and repeats. By the time it reaches the bottom layer it has already narrowed the search to a small neighbourhood, so only a fraction of vectors ever get compared.

Creating Vector Indexes

SQL · Create Vector Index
-- HNSW index (default when no type specified)
CREATE VECTOR INDEX idx_tickets_subject_hnsw
  ON support_tickets (subject_vec)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH HNSW
  WITH DISTANCE COSINE
  PARAMETERS (type HNSW, neighbors 32, efconstruction 200);

-- IVF index
CREATE VECTOR INDEX idx_tickets_subject_ivf
  ON support_tickets (subject_vec)
  ORGANIZATION NEIGHBOR PARTITIONS IVF
  WITH DISTANCE COSINE
  PARAMETERS (type IVF, neighbor_partitions 512);

-- SGA memory allocation for HNSW (init.ora / ALTER SYSTEM)
ALTER SYSTEM SET vector_memory_size = 2G SCOPE=SPFILE;

-- Monitor index population
SELECT index_name, status, num_rows, last_analyzed
FROM   user_indexes
WHERE  index_name LIKE 'IDX_TICKETS%';
ParameterIndex TypeEffectTune When
neighbors (M) HNSW Edges per node per layer. Higher = better recall, more memory Recall too low → increase to 48 or 64
efconstruction HNSW Beam width during index build. Higher = better index quality, slower build Index quality poor → increase to 400
neighbor_partitions (nlist) IVF Number of k-means clusters. Higher = finer partitioning Rule of thumb: √(row_count)
TARGET ACCURACY Both (query) Recall target at query time. Drives efSearch (HNSW) or nprobe (IVF) Adjust per query to balance latency vs recall
📘
eBook
Exadata DBA Guide
A comprehensive PDF guide for Oracle DBAs covering Exadata architecture, Smart Scan, Flash Cache, Storage HA and performance tuning.
Get the PDF →

07 Importing ONNX Embedding Models

Oracle 23ai can execute ONNX (Open Neural Network Exchange) format embedding models natively inside the database process — no Python runtime, no REST API call, no network hop. The model runs as part of a SQL evaluation.

Deployment Tip

When you export a model to ONNX, make sure you export the tokenizer too. Oracle handles the inference inside the database but the tokenizer determines how text gets split into tokens before embedding — if that differs between your test environment and production, your vectors won't be comparable.

1

Download the ONNX Model

Use the optimum library to export any Hugging Face sentence-transformer model to ONNX format.

Python · Export Model to ONNX
# pip install optimum sentence-transformers onnx onnxruntime
from optimum.exporters.onnx import main_export

main_export(
    model_name_or_path="sentence-transformers/all-MiniLM-L12-v2",
    output="./onnx_model/",
    task="feature-extraction",
)
# Output: model.onnx + tokenizer.json in ./onnx_model/
2

Create Oracle Directory Object

Place the .onnx file on the database server filesystem and create a directory object.

SQL · Directory + Privilege Setup
CREATE OR REPLACE DIRECTORY onnx_models_dir
  AS '/opt/oracle/models/onnx';

GRANT READ, WRITE ON DIRECTORY onnx_models_dir TO support_app;
GRANT CREATE MINING MODEL TO support_app;
3

Load the Model with DBMS_VECTOR

The DBMS_VECTOR.LOAD_ONNX_MODEL procedure reads the ONNX file and stores it as a Mining Model object.

SQL · DBMS_VECTOR.LOAD_ONNX_MODEL
BEGIN
  DBMS_VECTOR.LOAD_ONNX_MODEL(
    directory   => 'ONNX_MODELS_DIR',
    file_name   => 'all-MiniLM-L12-v2.onnx',
    model_name  => 'MINILM_L12',
    metadata    => JSON(
      '{"function":"embedding","embeddingOutput":"embedding",
       "input":{"input":["DATA"]}}'
    )
  );
END;
/

SELECT model_name, algorithm, mining_function, model_size_mb
FROM   user_mining_models
WHERE  model_name = 'MINILM_L12';
4

Verify & Check Limits

The cumulative size of all loaded ONNX models is capped at 2 GB per database, stored in SYSAUX tablespace.

🔒 Security Note

Loading a model requires the CREATE MINING MODEL privilege, so it's a DBA operation. Oracle validates the ONNX graph against a whitelist of supported operators before accepting it — you can't load an arbitrary ONNX file and have it execute code inside the database.

08 The VECTOR_EMBEDDING Function

Once a model is loaded, VECTOR_EMBEDDING works anywhere SQL runs — SELECT, INSERT, UPDATE, even inside a WHERE clause. The three patterns below cover the most common situations.

SQL · VECTOR_EMBEDDING Syntax & Patterns
-- Basic syntax
VECTOR_EMBEDDING( model_name USING text_expr AS DATA )

─────────────────────────────────────────────────────
Pattern 1: Embed a literal for ad-hoc search
SELECT ticket_id, subject,
       VECTOR_DISTANCE(
         subject_vec,
         VECTOR_EMBEDDING(MINILM_L12 USING 'server cannot connect to VPN' AS DATA),
         COSINE
       ) AS dist
FROM   support_tickets
ORDER BY dist
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

─────────────────────────────────────────────────────
Pattern 2: Batch-embed new rows on INSERT
INSERT INTO support_tickets
  (customer_id, priority, subject, body, subject_vec, body_vec)
VALUES
  (4201, 'HIGH', 'Email not syncing on mobile', '...',
   VECTOR_EMBEDDING(MINILM_L12 USING 'Email not syncing on mobile' AS DATA),
   VECTOR_EMBEDDING(MINILM_L12 USING '[full body text goes here]'  AS DATA));

─────────────────────────────────────────────────────
Pattern 3: Backfill existing rows (batch update)
BEGIN
  FOR r IN (SELECT ticket_id, subject FROM support_tickets WHERE subject_vec IS NULL) LOOP
    UPDATE support_tickets
       SET    subject_vec = VECTOR_EMBEDDING(MINILM_L12 USING r.subject AS DATA)
     WHERE  ticket_id = r.ticket_id;
    IF MOD(sql%rowcount, 1000) = 0 THEN COMMIT; END IF;
  END LOOP;
  COMMIT;
END;
/
⚠️ Text Truncation

Most embedding models have a maximum token limit (typically 512 tokens for MiniLM models, ~8192 for larger models like BGE-M3). If your text exceeds this limit, the model silently truncates the input. For long documents, split into paragraphs or sentences and embed each chunk separately.

09 End-to-End: Semantic Ticket Search in 5 Steps

A support agent types a free-text description of a problem and the system returns the five most similar resolved tickets — retrieving their resolution text as a suggested answer.

Set up VECTOR_MEMORY_SIZE for HNSW Allocate SGA memory before starting the instance. Restart required for initial sizing.
Load ONNX Model DBMS_VECTOR.LOAD_ONNX_MODEL → MINILM_L12 available for SQL calls.
Create Table & Populate Embeddings INSERT rows, or batch-UPDATE existing rows using VECTOR_EMBEDDING in a PL/SQL loop.
Build HNSW Index CREATE VECTOR INDEX on subject_vec or body_vec with COSINE distance.
Query at Runtime Embed the user's query inline, ORDER BY distance, FETCH APPROXIMATE FIRST 5 ROWS.
SQL · Full End-to-End Query
-- Step 1 (one-time setup, requires bounce)
ALTER SYSTEM SET vector_memory_size = 1500M SCOPE=SPFILE;

-- Step 2 (one-time, DBA)
BEGIN
  DBMS_VECTOR.LOAD_ONNX_MODEL(
    'ONNX_MODELS_DIR', 'all-MiniLM-L12-v2.onnx', 'MINILM_L12',
    JSON('{"function":"embedding"}'));
END;
/

-- Step 3 (DDL + initial data load)
CREATE TABLE support_tickets ( ... );
UPDATE support_tickets
   SET subject_vec = VECTOR_EMBEDDING(MINILM_L12 USING subject AS DATA);
COMMIT;

-- Step 4
CREATE VECTOR INDEX idx_ticket_subj
  ON support_tickets (subject_vec)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH HNSW
  WITH DISTANCE COSINE
  PARAMETERS (type HNSW, neighbors 32, efconstruction 200);

-- Step 5: runtime query
SELECT
  t.ticket_id,
  t.subject,
  t.resolution,
  ROUND(VECTOR_DISTANCE(t.subject_vec,
    VECTOR_EMBEDDING(MINILM_L12 USING :user_query AS DATA),
    COSINE), 4) AS semantic_dist
FROM   support_tickets t
WHERE  t.resolution IS NOT NULL
ORDER BY semantic_dist
FETCH APPROXIMATE FIRST 5 ROWS ONLY
WITH TARGET ACCURACY 90;
🏗️ Architecture Note

This is the basic setup for RAG with Oracle. Your application takes the top 5 tickets, puts them into an LLM prompt, and asks the model to draft a reply. Because you're grounding the LLM on real resolved tickets from your own system, the answers are far more reliable than anything the model could generate from its training alone.

Before you go live

Run your top queries against both exact kNN and approximate search and compare the results. Note the latency difference and check how often the approximate results differ from the exact ones. Then adjust TARGET ACCURACY and index parameters until you're happy with the trade-off.

10 Knowledge Check

Oracle AI Vector Search — Quiz
// Test your understanding · 7 questions
Q1. Which VECTOR format was introduced specifically in Oracle 23.5 and requires dimensions to be a multiple of 8?
INT8
BINARY
FLOAT16
FLOAT32
✅ BINARY was introduced in Oracle 23.5. It stores each dimension as a single bit, so the dimension count must be a multiple of 8. HAMMING distance is the appropriate metric.
Q2. You write ORDER BY (v1 <#> v2). Which distance metric does this operator map to?
COSINE
EUCLIDEAN
Negated DOT PRODUCT
MANHATTAN
✅ The <#> operator maps to negated dot product. The negation means that more similar vectors have a lower value, so ORDER BY ASC still returns the most similar first.
Q3. An unconstrained VECTOR column (no parameters specified) has which key limitation in production?
Cannot have a vector index (HNSW or IVF) created on it
Cannot be queried with VECTOR_DISTANCE
Only supports COSINE distance
It is read-only
✅ A vector index requires uniform dimensionality. An unconstrained column can hold vectors of any size, so no index can be built on it.
Q4. HNSW differs from IVF primarily because HNSW:
Partitions vectors into k-means clusters on disk
Is only available for BINARY vectors
Requires no memory allocation and is fully disk-based
Builds an in-memory multi-layered graph structure in SGA
✅ HNSW builds its index as an in-memory graph stored in Oracle's SGA (controlled by VECTOR_MEMORY_SIZE). IVF is disk-based and uses k-means clustering to partition vectors.
Q5. What does FETCH APPROXIMATE FIRST 10 ROWS ONLY WITH TARGET ACCURACY 90 mean?
Return exactly 9 rows that are 90% similar to the query vector
Use a vector index; aim to return results that overlap 90% with true exact kNN
Scan only 90% of the table rows
Return top 10 rows where distance < 0.90
✅ FETCH APPROXIMATE triggers ANN index search. WITH TARGET ACCURACY 90 means Oracle tunes how many graph nodes or IVF partitions it probes so ~90% of results would also appear in an exact kNN result.
Q6. In VECTOR_EMBEDDING(MINILM_L12 USING subject AS DATA), what is MINILM_L12?
A PL/SQL function you wrote
An Oracle built-in embedding algorithm
The model_name given when loading the ONNX model via DBMS_VECTOR
A column alias
✅ MINILM_L12 is the model_name parameter supplied to DBMS_VECTOR.LOAD_ONNX_MODEL. It becomes an Oracle Mining Model object stored in the data dictionary.
Q7. What is the cumulative maximum size of all ONNX models that can be loaded into one Oracle 23ai database?
2 GB
512 MB
Unlimited
10 GB
✅ Oracle 23ai caps cumulative ONNX model size at 2 GB per database, stored in the SYSAUX tablespace.

February 28, 2026  ·   ·   ·   ·   · 

Oracle Database 23ai - New Features for DBAs

By Gowthami | apps-dba.com | Oracle Database Series

Oracle Database 23ai (formerly known as Oracle Database 23c) is Oracle's latest long-term support release, bringing a significant leap forward with native AI integration directly into the database engine. This post covers the key new features that every Oracle DBA should know about.

Key Insight: Oracle 23ai introduces AI-native capabilities including Vector Search, JSON Relational Duality Views, and True Cache—fundamentally changing how DBAs design and query databases.

AI Vector Search

One of the most significant additions in Oracle 23ai is AI Vector Search. This feature allows the database to store, index, and query vector embeddings natively, enabling semantic similarity searches alongside traditional SQL queries. The new VECTOR data type stores high-dimensional embeddings.

-- Create a table with a vector column
CREATE TABLE documents (
  id      NUMBER PRIMARY KEY,
  content CLOB,
  embedding VECTOR(1536, FLOAT32)
);

-- Similarity search using cosine distance
SELECT content
FROM documents
ORDER BY VECTOR_DISTANCE(embedding, :query_vec, COSINE)
FETCH FIRST 5 ROWS ONLY;

Schema Privileges with SQL Domains

Oracle 23ai introduces SQL Domains, a new schema-level object for centralizing column constraints and properties. DBAs can define a domain once and reuse it across many tables, improving consistency and maintainability.

-- Define a reusable domain
CREATE DOMAIN email_address AS VARCHAR2(255)
  CONSTRAINT email_check CHECK (REGEXP_LIKE(VALUE, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$'));

-- Apply domain to a column
CREATE TABLE employees (
  emp_id NUMBER PRIMARY KEY,
  email  email_address
);

JSON Relational Duality Views

A game-changing feature: JSON Relational Duality Views let you expose relational tables as JSON documents and vice versa with full read/write support. This bridges the gap between relational and document-oriented data models.

-- Create a duality view over relational tables
CREATE JSON RELATIONAL DUALITY VIEW emp_dv AS
  SELECT JSON {
    'emp_id'   : e.emp_id,
    'name'     : e.last_name,
    'dept'     : d.dept_name
  }
  FROM employees e
  JOIN departments d ON e.dept_id = d.dept_id
  WITH INSERT UPDATE DELETE;

True Cache for Read Offloading

True Cache is a read-only cache of the primary database that offloads read workloads without requiring application changes. Unlike Oracle GoldenGate replicas, True Cache stays synchronized using redo logs, providing transparent performance improvements.

Developer-Friendly Features

  • IF [NOT] EXISTS for DDL: CREATE TABLE IF NOT EXISTS simplifies deployment scripts
  • Table Value Constructors: Insert multiple rows cleanly and efficiently
  • Annotations: Attach metadata to schema objects for better documentation
  • Property Graph: Graph queries using SQL/PGQ standard

Summary

Oracle 23ai brings AI-native capabilities, developer ergonomics, and architectural innovations that every DBA should understand. From Vector Search to Duality Views, these features change how you design and query databases, making it essential to stay current with this latest release.

Oracle Exadata - The Complete Guide

Want to master every aspect of Exadata? Smart Scan, Storage Indexes, Hybrid Columnar Compression, and Exadata-specific SQL tuning techniques. Get Gowthami's complete guide.

Get the Book

February 28, 2026  ·   · 

Oracle Exadata Smart Scan Explained – How It Accelerates Query Performance

By Gowthami | apps-dba.com | Oracle Exadata Series

If you work with Oracle Exadata, one of the most transformative features you will encounter is Smart Scan. It is the engine behind Exadata's legendary query acceleration, and understanding it deeply is essential for any Oracle DBA working in Exadata environments. In this post, we will break down exactly how Smart Scan works, when it activates, how to verify it, and how to tune your environment to get the most out of it.

What is Smart Scan?

Smart Scan is Exadata's ability to offload SQL processing — specifically full table scans and index range scans — directly to the Exadata Storage Cells. Instead of the database server reading all data blocks and filtering them in the database layer, Exadata pushes the filter predicates, column projections, and even joins down to the storage cells. Only the relevant rows and columns are returned to the database tier.

This is a radical departure from traditional Oracle storage architecture, where storage is "dumb" — it only reads and returns blocks. Exadata storage cells are intelligent processing units running Cellsrv software that understands SQL predicates.

How Smart Scan Works – Step by Step

  1. Predicate Offloading: Oracle sends the WHERE clause predicates down to each storage cell.
  2. Column Projection: Only the columns referenced in the SELECT list are returned — not all columns in the table.
  3. Row Filtering at Storage: Each cell evaluates predicates row by row and discards non-matching rows before sending data back.
  4. Parallel Processing: Multiple storage cells process different data segments simultaneously in parallel.
  5. IB Network Transfer: Only the filtered, projected result set travels over the InfiniBand network to the database server.
💡 Key Insight: In a traditional setup, a 1 TB table scan might transfer 1 TB over the storage network. With Smart Scan, only the matching rows and required columns are transferred — potentially reducing data movement by 99%.

Prerequisites for Smart Scan to Activate

ConditionDetail
Full Table Scan or Fast Full Index ScanSmart Scan only works with these access paths — not regular index range scans
Direct Path ReadThe segment must be read via direct path (bypassing buffer cache). Happens automatically for large objects on Exadata.
Object on Exadata StorageThe table must reside on ASM diskgroups backed by Exadata storage cells
Cell Offload Compatible SQLNot all SQL constructs can be offloaded. Complex PL/SQL functions may prevent offloading.

Verifying Smart Scan is Active

1. Check Cell Physical IO Statistics

SELECT name, value
FROM v$mystat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name IN (
  'cell physical IO interconnect bytes',
  'cell physical IO interconnect bytes returned by smart scan',
  'physical read total bytes'
)
ORDER BY name;

2. Execution Plan – Storage Predicate

EXPLAIN PLAN FOR
SELECT /*+ FULL(e) */ department_id, SUM(salary)
FROM employees e
WHERE hire_date > DATE '2020-01-01'
GROUP BY department_id;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Look for "storage" in the Predicate Information section — this indicates predicate pushdown to storage cells.

What Prevents Smart Scan?

  • Small tables – Oracle may cache them in buffer cache rather than use direct path reads
  • Index access paths – Regular B-tree index range scans do not trigger Smart Scan
  • Non-offloadable SQL functions – User-defined PL/SQL functions in WHERE clauses cannot be pushed to cells
  • Cell offload disabled – The parameter cell_offload_processing is set to FALSE (default is TRUE)

Forcing or Disabling Smart Scan

-- Disable Smart Scan for current session
ALTER SESSION SET cell_offload_processing = FALSE;

-- Re-enable
ALTER SESSION SET cell_offload_processing = TRUE;

-- Check current setting
SELECT name, value FROM v$parameter WHERE name = 'cell_offload_processing';

Smart Scan and Storage Indexes

Smart Scan works hand-in-hand with Storage Indexes — an Exadata-only feature that maintains in-memory min/max value ranges for each 1MB storage region. Before performing a Smart Scan, Exadata consults storage indexes to skip entire 1MB storage regions that cannot possibly contain matching rows. This further reduces I/O dramatically.

Summary

Smart Scan is one of the most powerful features that distinguishes Exadata from conventional Oracle deployments. By pushing filter and projection processing down to the storage cells, it enables massive reductions in data movement and dramatically accelerates analytical queries and large table scans.

Tags: Oracle Exadata, Smart Scan, Exadata Performance, Oracle DBA, Cell Offload

May 30, 2025  ·   ·   ·   ·   ·   ·   ·   ·   · 

Data Safe - Introduction

Oracle Data Safe - Practical Guide
Oracle Data Safe learner guide

Oracle Data SafeAssess risk, discover sensitive data, audit activity, and mask safely

It focuses on what Data Safe helps you do operationally: review security posture, find risky identities, centralize auditing, locate sensitive data, and produce safer non-production copies.

Why Data Safe matters

Database security work is often fragmented. One process checks configuration drift, another stores audit logs, another team scans for PII, and another team writes masking logic for test refreshes. Data Safe is useful because it turns those separate jobs into one security workflow.

Key idea

The best way to think about Data Safe is as a control plane for database security posture: assess the target, identify risky accounts, discover sensitive data, monitor activity, and mask data before it leaves production.

For DBAs

Prioritize hardening tasks, review powerful accounts, and prove controls are in place.

For security teams

Centralize audit evidence and review alerts in a more consistent way.

For compliance

Map controls to real database evidence instead of relying on manual screenshots.

For developers and QA

Receive masked copies of production data without exposing real customer secrets.

What Oracle Data Safe is and where it fits

Oracle Data Safe is a managed service for securing Oracle databases. In practice, teams register targets, collect metadata and audit information, run security and user assessments, discover sensitive columns, and create masking models for safe downstream use. It fits above the database features you already rely on, such as privileges, Unified Auditing, and encryption.

Mental model

Data Safe does not replace database security features. It helps you evaluate, organize, and operationalize them.

NeedHow Data Safe helpsOutcome
Configuration hardeningSecurity Assessment and baselinesRanked remediation instead of scattered manual checks
Privilege reviewUser Assessment and account analysisFaster identification of over-privileged or stale accounts
Audit evidenceActivity Auditing, reports, and alertsSearchable activity trails and stronger investigation context
PII inventorySensitive Data Discovery modelsBetter visibility into where regulated data actually lives
Safe non-prod refreshesData Masking formats and policiesRealistic but de-identified data for dev and test

Capability map

Security Assessment

Scores security posture and helps spot configuration drift over time.

User Assessment

Highlights risky users, roles, privileges, and password posture.

Activity Auditing

Centralizes audit activity so suspicious events are easier to analyze.

Sensitive Data Discovery

Scans schemas for likely sensitive columns and groups them into usable models.

Data Masking

Transforms sensitive data for lower environments while preserving usefulness.

Alerts and adjacent controls

Teams often combine audit alerts with newer controls such as SQL Firewall where supported.
Important distinction

Discovery tells you where sensitive data is. Masking changes values so the data can be used more safely outside production. Discovery usually comes first.

Assessments: configuration risk and powerful users

Security Assessment and User Assessment answer different questions. Security Assessment asks whether the database is configured safely. User Assessment asks which accounts or privileges create unnecessary risk. Strong teams run both and combine the findings into one remediation list.

Security Assessment mindset

  • Use it to detect drift after upgrades, emergency changes, or team turnover.
  • Compare snapshots over time instead of reading one report in isolation.
  • Separate high-impact issues from noisy low-priority findings.

User Assessment mindset

  • Look for stale accounts, broad roles, and accounts that should not be interactive.
  • Review both granted roles and direct system privileges.
  • Pay special attention to shared accounts and accounts that can delegate privileges.
SQL - Risky roles overview
-- Start with role-based exposure, not just one account at a time
SELECT r.grantee,
       LISTAGG(r.granted_role, ', ') WITHIN GROUP (ORDER BY r.granted_role) AS roles,
       MAX(CASE WHEN r.admin_option = 'YES' THEN 'YES' ELSE 'NO' END) AS can_delegate
FROM   dba_role_privs r
WHERE  r.granted_role IN ('DBA', 'RESOURCE', 'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
GROUP BY r.grantee
ORDER BY r.grantee;
SQL - Direct system privileges
-- Direct grants are easy to miss during reviews
SELECT p.grantee,
       p.privilege,
       p.admin_option
FROM   dba_sys_privs p
WHERE  p.privilege IN (
         'ALTER SYSTEM',
         'CREATE USER',
         'DROP USER',
         'GRANT ANY PRIVILEGE',
         'GRANT ANY ROLE')
ORDER BY p.grantee, p.privilege;
Reading tip

For every risky account finding, ask two questions: Is the account still needed? If yes, is the breadth of privilege still justified?

Activity Auditing: evidence, alerts, and useful scope

Auditing is most useful when it is targeted. If you audit everything, you create noise. If you audit only logons, you miss privilege abuse and schema-change evidence. A strong starting point is administrator actions, identity changes, privilege changes, and access to a shortlist of sensitive objects.

Audit focusWhy it mattersTypical signals
Account lifecyclePrivileged account creation or changes often indicate elevated riskCREATE USER, ALTER USER, DROP USER
Privilege managementTracks who can widen someone else's accessGRANT, REVOKE, role changes
Schema changesUseful for change control and investigationsCREATE TABLE, ALTER TABLE, DROP object
Sensitive data accessShows which identities touched regulated dataSELECT or DML against key application tables
SQL - Unified audit policy example
-- Focus on high-value administrative actions first
CREATE AUDIT POLICY admin_change_pol
  ACTIONS
    CREATE USER,
    ALTER USER,
    DROP USER,
    GRANT,
    REVOKE;

AUDIT POLICY admin_change_pol;
SQL - Read recent audit events
-- Validate that your policy is producing useful evidence
SELECT event_timestamp,
       dbusername,
       action_name,
       object_schema,
       object_name,
       return_code
FROM   unified_audit_trail
WHERE  action_name IN ('CREATE USER', 'ALTER USER', 'GRANT', 'REVOKE')
ORDER BY event_timestamp DESC
FETCH FIRST 20 ROWS ONLY;
Noise control

Do not start by auditing every action in every schema. Begin with a high-signal set of events and expand only after the results are useful.

Sensitive Data Discovery and Data Masking

Discovery and masking are where many teams feel immediate value. Discovery shows where email, payment, health, national-identifier, or compensation data may exist. Masking then turns that map into safe copies for development, QA, analytics, and training environments.

Discovery: think in models, not one-off scans

A useful discovery run does more than list columns. It creates a durable model of sensitive data in the target, so you can review, refine, and reuse classifications.

SQL - Candidate sensitive columns
-- A simple learner-friendly query for columns worth reviewing
SELECT owner,
       table_name,
       column_name,
       data_type
FROM   dba_tab_columns
WHERE  REGEXP_LIKE(column_name,
         'SSN|SOCIAL|NATIONAL|TAX|EMAIL|PHONE|CARD|CREDIT|DOB|BIRTH|SALARY|PAY',
         'i')
ORDER BY owner, table_name, column_id;

Good masking is not just hiding values. It should preserve test usefulness by keeping format, joins, and application expectations intact.

Best fit for masking

Non-production refreshes, training databases, vendor troubleshooting copies, analytics sandboxes.

Common mistakes

Masking only one table, breaking join keys, or generating values that fail application validation.

Useful formats

Deterministic substitution, shuffling, partial masking, random date shifts, and conditional rules.

Validation after masking

Check row counts, referential integrity, uniqueness assumptions, and application-level patterns.
SQL - Post-mask validation checks
-- Example 1: validate email-like shape after masking
SELECT COUNT(*) AS bad_email_rows
FROM   customers_masked
WHERE  email NOT LIKE '%@%';

-- Example 2: validate parent-child integrity still holds
SELECT COUNT(*) AS broken_order_links
FROM   orders_masked o
       LEFT JOIN customers_masked c
         ON c.customer_id = o.customer_id
WHERE  c.customer_id IS NULL;

Operating model: how teams actually use it

Mature teams do not run Data Safe once and forget it. They schedule assessments, review deltas, tune audit scope, refresh sensitive-data models, and keep masking policies aligned with application change.

Weekly

Review new high-risk findings, audit alerts, and accounts with newly expanded privileges.

Monthly

Re-run assessments, compare baselines, and verify that remediation reduced exposure.

Before each refresh

Validate masking coverage for newly added tables, columns, or integrations.

After major releases

Review discovery models and audit policies because new releases often add new sensitive fields.
Good habit

Tie every important report to an owner and a next action. Reports without ownership become shelfware.

A practical first-30-days rollout plan

Week 1: onboard and inventory

Register the most important targets, confirm connectivity, and document business owners.

Week 2: run both assessments

Triage high-severity findings and identify the best privileged-account cleanup opportunities.

Week 3: enable focused auditing

Start with administrator actions and a shortlist of high-value objects.

Week 4: discover and mask

Create a sensitive-data model, build a first masking policy, and validate application behavior.
Avoid the common trap

Do not wait for a perfect enterprise-wide design before starting. A narrow, well-run rollout on a few critical databases teaches more than a giant plan that never reaches production.

Knowledge check

These questions test understanding, not memorization. Read the explanations after you submit - that is where the real learning happens.

Q1. Which statement best captures the relationship between Sensitive Data Discovery and Data Masking?
They are the same feature with two different names.
Discovery finds and classifies sensitive data; masking changes data values for safe downstream use.
Discovery runs only after masking is complete.
Masking is only for production, discovery is only for test databases.
Correct answer: Discovery finds and classifies, masking transforms. Discovery gives visibility into where sensitive data lives. Masking uses that understanding to produce safer non-production copies without exposing real values.
Q2. Why is User Assessment not the same as Security Assessment?
User Assessment measures storage growth, while Security Assessment measures CPU use.
User Assessment is for developers only, while Security Assessment is for DBAs only.
User Assessment focuses on account and privilege risk; Security Assessment focuses on database security posture and configuration.
Security Assessment replaces auditing, while User Assessment replaces masking.
Correct answer: User Assessment looks at identity risk, Security Assessment looks at configuration risk. Strong operations use both because accounts and configuration can each create serious exposure.
Q3. What is the best starting point for activity auditing in a new rollout?
Audit a focused set of high-value actions such as account changes, grants, revokes, and sensitive object access.
Audit every statement in every schema immediately.
Audit only failed logons and nothing else.
Skip auditing until all masking work is complete.
Correct answer: Start focused. Good auditing begins with high-signal events. If you start too broad, teams drown in noise and stop trusting the results.
Q4. Which masking outcome is the most useful in practice?
All values are replaced with NULL so nothing can leak.
Every text field is replaced with exactly the same dummy word.
Only one table is masked, because joins are not important in test environments.
Values are de-identified while still preserving application usefulness and key relationships.
Correct answer: Useful masked data still behaves like data. If the application breaks, referential integrity fails, or validation rules collapse, the masked copy is much less valuable.
Q5. A team runs a discovery scan and finds columns likely to contain email, salary, and national ID data. What is the strongest next step?
Ignore the findings until the next annual audit.
Review and refine the sensitive-data model, then use it to drive masking and monitoring priorities.
Drop the columns immediately from production.
Disable auditing because discovery already covers compliance needs.
Correct answer: Turn findings into action. Discovery is most valuable when it feeds masking design, audit scope, and ownership decisions for sensitive data.
Q6. What is the biggest operational mistake after onboarding Data Safe?
Running reports too often.
Using code blocks with copy buttons.
Treating reports as passive output instead of assigning owners and remediation actions.
Reviewing privileges before looking at storage space.
Correct answer: Unowned findings rarely reduce risk. Security posture improves when each high-value finding has an owner, a decision, and a due date.

Vocabulary Revision 6 — Lessons 21 to 24

Lessons 21–24 · 16 words Watch on YouTube Contents https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily Open ...