Spatial, Graph, Text and Vector Search

Also: Graph Server (PGX), PGQL, graph analytics algorithms.

32.1 Oracle Spatial

CREATE TABLE stores (id NUMBER, name VARCHAR2(50), location SDO_GEOMETRY);
INSERT INTO stores VALUES (1, 'Pune', SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(73.8567, 18.5204, NULL), NULL, NULL));
-- Register metadata in USER_SDO_GEOM_METADATA, then:
CREATE INDEX ix_stores_loc ON stores(location) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2;
SELECT name FROM stores WHERE SDO_WITHIN_DISTANCE(location, :pt, 'distance=10 unit=KM') = 'TRUE';
SELECT SDO_GEOM.SDO_DISTANCE(a.location, b.location, 0.005, 'unit=KM') FROM stores a, stores b WHERE a.id = 1 AND b.id = 2;

32.2 Property Graphs (SQL/PGQ, 23ai)

Also: Graph Server (PGX), PGQL, graph analytics algorithms.

CREATE PROPERTY GRAPH social_graph
  VERTEX TABLES (persons KEY (id) PROPERTIES (id, name))
  EDGE TABLES (friendships KEY (id)
     SOURCE KEY (person_a) REFERENCES persons (id)
     DESTINATION KEY (person_b) REFERENCES persons (id));

SELECT * FROM GRAPH_TABLE (social_graph
  MATCH (a IS persons) -[e IS friendships]-> (b IS persons)
  WHERE a.name = 'Asha'
  COLUMNS (b.name AS friend));

32.3 Oracle Text

CREATE INDEX ix_prod_text ON products(description) INDEXTYPE IS CTXSYS.CONTEXT;
SELECT product_name, SCORE(1) FROM products WHERE CONTAINS(description, 'wireless AND mouse', 1) > 0 ORDER BY SCORE(1) DESC;
-- CTXCAT and CTXRULE index types, fuzzy/stem/soundex operators ($run, ?colour, !smith), NEAR, sync/optimize
EXEC CTX_DDL.SYNC_INDEX('IX_PROD_TEXT');

32.4 AI Vector Search (23ai)

Distance metrics: COSINE, EUCLIDEAN (L2), DOT, MANHATTAN, HAMMING, JACCARD. Shorthand operators: <=>, <->, <#>.

DBMS_VECTOR_CHAIN: UTL_TO_TEXT, UTL_TO_CHUNKS, UTL_TO_EMBEDDINGS, UTL_TO_GENERATE_TEXT for RAG pipelines.

Select AI (DBMS_CLOUD_AI): natural language to SQL and RAG on Autonomous Database.

Hybrid vector indexes (text + vector) in 23.6+.

CREATE TABLE docs (id NUMBER PRIMARY KEY, content CLOB, embedding VECTOR(384, FLOAT32));

-- Load an ONNX embedding model into the database
EXEC DBMS_VECTOR.LOAD_ONNX_MODEL('MODEL_DIR', 'all_MiniLM_L12_v2.onnx', 'doc_model');

UPDATE docs SET embedding = VECTOR_EMBEDDING(doc_model USING content AS data);

-- Exact similarity search
SELECT id, VECTOR_DISTANCE(embedding, VECTOR_EMBEDDING(doc_model USING 'refund policy' AS data), COSINE) dist
FROM docs ORDER BY dist FETCH FIRST 5 ROWS ONLY;

-- Approximate search with vector indexes
CREATE VECTOR INDEX ix_docs_hnsw ON docs (embedding) ORGANIZATION INMEMORY NEIGHBOR GRAPH
  DISTANCE COSINE WITH TARGET ACCURACY 95;                    -- HNSW (needs VECTOR_MEMORY_SIZE)
CREATE VECTOR INDEX ix_docs_ivf ON docs (embedding) ORGANIZATION NEIGHBOR PARTITIONS DISTANCE COSINE;   -- IVF

SELECT id FROM docs ORDER BY VECTOR_DISTANCE(embedding, :q, COSINE)
FETCH APPROX FIRST 5 ROWS ONLY WITH TARGET ACCURACY 90;

32.5 Machine Learning in the Database

Oracle Machine Learning (OML4SQL, OML4Py, OML4R, AutoML UI): in-database algorithms (classification, regression, clustering, anomaly detection), DBMS_DATA_MINING, PREDICTION() SQL functions.