Vector Data Type, Embeddings, Distance Metrics, Similarity Search, and Relational Filtering

Learning outcomes

ServiceHub has thousands of troubleshooting notes. A technician searches “compressor overheating after restart,” but exact keywords miss a note that says “temperature rises after power cycle.” A vector embedding maps text into a numeric coordinate space where semantically related inputs can be close even when words differ. Oracle's VECTOR data type stores those coordinates beside relational security/status metadata so a similarity query can stay inside the same transaction and authorization model.

1. Embedding values are model-dependent data

An embedding model turns an input such as text into a fixed-length vector. Its dimensions do not have independent business meanings; the whole vector represents position in the model's learned space. Vectors generated by different models, different model revisions, different preprocessing/chunking, or different normalization policies generally must not be compared as though they share one semantic coordinate system.

2. VECTOR type compatibility and formats

SELECT name,valueFROM v$parameterWHERE name IN ('compatible','vector_memory_size')ORDER BY name;-- VECTOR and related features require COMPATIBLE >= 23.4.0.

Dense vectors can use INT8, FLOAT32, FLOAT64, or packed BINARY elements. A declaration such as VECTOR(4,FLOAT32) is dense by default. Sparse storage is declared with VECTOR(...,...,SPARSE) and stores only nonzero dimensions; use it only when the embedding representation is genuinely sparse.

3. Create a governed ServiceHub embedding table

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh25_notes PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh25_notes (  note_id             NUMBER PRIMARY KEY,  tenant_code         VARCHAR2(20) NOT NULL,  status_code         VARCHAR2(12) NOT NULL,  note_text           VARCHAR2(500) NOT NULL,  embedding_model     VARCHAR2(80) NOT NULL,  embedding_version   VARCHAR2(40) NOT NULL,  normalization       VARCHAR2(20) NOT NULL,  embedded_at         TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL,  source_hash         VARCHAR2(64) NOT NULL,  embedding           VECTOR(4,FLOAT32,DENSE) NOT NULL,  CONSTRAINT sh25_notes_status_ck    CHECK (status_code IN ('ACTIVE','RETIRED')));

The four-dimensional vectors are deliberately hand-authored for a transparent lab. They model four conceptual axes only for teaching SQL mechanics; they are not production semantic embeddings.

INSERT INTO sh25_notes VALUES(  1,'TENANT_A','ACTIVE',  'Compressor temperature rises after power cycle',  'SERVICEHUB_LAB_AXES','1.0','UNIT_LIKE',  SYSTIMESTAMP,'HASH-001',  VECTOR('[0.90,0.80,0.10,0.05]',4,FLOAT32));INSERT INTO sh25_notes VALUES(  2,'TENANT_A','ACTIVE',  'Refrigerant pressure low after restart',  'SERVICEHUB_LAB_AXES','1.0','UNIT_LIKE',  SYSTIMESTAMP,'HASH-002',  VECTOR('[0.75,0.70,0.20,0.05]',4,FLOAT32));INSERT INTO sh25_notes VALUES(  3,'TENANT_A','RETIRED',  'Legacy compressor restart procedure',  'SERVICEHUB_LAB_AXES','1.0','UNIT_LIKE',  SYSTIMESTAMP,'HASH-003',  VECTOR('[0.82,0.62,0.10,0.10]',4,FLOAT32));INSERT INTO sh25_notes VALUES(  4,'TENANT_B','ACTIVE',  'Compressor overheats after electrical restart',  'SERVICEHUB_LAB_AXES','1.0','UNIT_LIKE',  SYSTIMESTAMP,'HASH-004',  VECTOR('[0.95,0.77,0.08,0.02]',4,FLOAT32));INSERT INTO sh25_notes VALUES(  5,'TENANT_A','ACTIVE',  'Replace door latch and align cabinet',  'SERVICEHUB_LAB_AXES','1.0','UNIT_LIKE',  SYSTIMESTAMP,'HASH-005',  VECTOR('[0.05,0.05,0.85,0.30]',4,FLOAT32));COMMIT;

4. Verify vector metadata instead of assuming it

SELECT  note_id,  VECTOR_DIMENSION_COUNT(embedding) AS dims,  VECTOR_DIMENSION_FORMAT(embedding) AS format,  ROUND(VECTOR_NORM(embedding),4) AS euclidean_norm,  embedding_model,  embedding_version,  normalizationFROM sh25_notesORDER BY note_id;

Expected: every row reports four dimensions and FLOAT32. The norm shows that the lab vectors are not exactly unit normalized; that fact matters when choosing/interpreting DOT versus COSINE.

5. Exact cosine search

VARIABLE q VECTORBEGIN  :q := VECTOR('[0.92,0.78,0.10,0.04]',4,FLOAT32);END;/SELECT  note_id,  note_text,  VECTOR_DISTANCE(embedding,:q,COSINE) AS cosine_distanceFROM sh25_notesWHERE tenant_code='TENANT_A'  AND status_code='ACTIVE'ORDER BY VECTOR_DISTANCE(embedding,:q,COSINE)FETCH EXACT FIRST 3 ROWS ONLY;

Smaller cosine distance means closer direction in vector space. The relational predicates are part of the SQL before results are returned: Tenant B and retired rows are not candidates visible to this query result.

6. Compare supported metrics deliberately

SELECT  VECTOR_DISTANCE(    VECTOR('[1,0,0,0]',4,FLOAT32),    VECTOR('[2,0,0,0]',4,FLOAT32),    COSINE  ) AS cosine_distance,  VECTOR_DISTANCE(    VECTOR('[1,0,0,0]',4,FLOAT32),    VECTOR('[2,0,0,0]',4,FLOAT32),    EUCLIDEAN  ) AS euclidean_distance,  VECTOR_DISTANCE(    VECTOR('[1,0,0,0]',4,FLOAT32),    VECTOR('[2,0,0,0]',4,FLOAT32),    DOT  ) AS dot_distance,  VECTOR_DISTANCE(    VECTOR('[1,0,0,0]',4,FLOAT32),    VECTOR('[2,0,0,0]',4,FLOAT32),    MANHATTAN  ) AS manhattan_distanceFROM dual;

Cosine emphasizes angle/direction; Euclidean uses geometric L2 distance; Manhattan uses L1 distance; Oracle's DOT distance is the negated dot product. Hamming/Jaccard are useful for appropriate binary vectors, with Jaccard requiring BINARY vectors. The embedding model's training/evaluation contract should determine the metric.

7. Normalization assumptions matter

For unit-normalized vectors, cosine similarity and dot-product ranking are closely related. If one pipeline normalizes and another does not, magnitude can change dot-product ordering even when semantic direction is similar. Store the normalization policy and verify it statistically rather than naming a column embedding and forgetting how it was produced.

SELECT note_id,ROUND(VECTOR_NORM(embedding),6) AS norm_valueFROM sh25_notesWHERE ABS(VECTOR_NORM(embedding)-1) > 0.05ORDER BY note_id;

8. Deliberate failure: wrong dimensions

INSERT INTO sh25_notes(  note_id,tenant_code,status_code,note_text,  embedding_model,embedding_version,normalization,  embedded_at,source_hash,embedding)VALUES(  99,'TENANT_A','ACTIVE','Bad dimensionality example',  'SERVICEHUB_LAB_AXES','1.0','UNIT_LIKE',  SYSTIMESTAMP,'HASH-099',  VECTOR('[0.1,0.2,0.3]',3,FLOAT32));-- Expected:-- ORA-51803: Vector dimension count must match the dimension count-- specified in the column definition.

The failure protects the physical shape but cannot detect a subtler error: a four-dimensional vector from a completely different model would still fit. Provenance/version controls are therefore part of correctness.

9. Wrong result: mix models with identical dimensions

A model migration from LAB_AXES 1.0 to another four-dimensional model can pass all database type checks while producing meaningless cross-model distances. The repair is to query only one embedding model/version at a time or maintain separate columns/tables/indexes during migration.

SELECT  note_id,  VECTOR_DISTANCE(embedding,:q,COSINE) AS distanceFROM sh25_notesWHERE tenant_code='TENANT_A'  AND status_code='ACTIVE'  AND embedding_model='SERVICEHUB_LAB_AXES'  AND embedding_version='1.0'ORDER BY distanceFETCH EXACT FIRST 3 ROWS ONLY;

10. Runtime plan evidence

SELECT /*+ gather_plan_statistics */       note_idFROM sh25_notesWHERE tenant_code='TENANT_A'  AND status_code='ACTIVE'ORDER BY VECTOR_DISTANCE(embedding,:q,COSINE)FETCH EXACT FIRST 3 ROWS ONLY;SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE'  ));

Without a vector index this is an exact scan/rank over qualifying vectors. On a tiny table that is the right learning path and can be production-correct for small candidate sets.

11. Driver and storage restrictions worth knowing

Current python-oracledb, node-oracledb, JDBC, ODP.NET and OCI releases can bind native vectors. Older/other clients may need VARCHAR2/CLOB representations; 19c/21c clients see VECTOR values as CLOBs. VECTOR columns cannot be PK/FK/unique/check-expression columns and Data Redaction cannot be applied to the VECTOR column itself. ONNX in-database model execution is supported on Linux x86-64/Arm, not Microsoft Windows.

12. Cleanup

DROP TABLE sh25_notes PURGE;

13. Production judgment

Store vectors beside the relational facts they retrieve when that reduces synchronization/security complexity. Make model/version/preprocessing/normalization first-class data, enforce one dimension contract per search space, and use exact search as the correctness reference. Semantic ranking never replaces authorization predicates.

VECTOR requires COMPATIBLE >= 23.4.0; this chapter does not raise COMPATIBLE. The lab is PDB/schema-local, needs normal table/query privileges and no pack/restart. External embedding generation can use current drivers/providers; in-database ONNX has platform restrictions. Lesson 2 now asks when exact scan cost justifies an approximate vector index and how to measure the accuracy loss rather than assuming “ANN is close enough.”

Authoritative references

• Create Tables Using VECTOR — dense/sparse formats/dimensions/restrictions
• Vector Distance Functions and Operators — distance metrics/operators
• Oracle AI Vector Search Overview — COMPATIBLE/model workflow
• AI Vector Search Restrictions — drivers/platform/redaction/Data Pump restrictions
• ORA-51803 — dimension mismatch failure