Ryan Giggs

Posted on Feb 26

Oracle AI Vector Search: DML and DDL Operations on Vector Columns

#oracle #oracledatabase23ai #vectordatabase #vectoroperations

Oracle Database 23ai introduces the VECTOR data type, enabling you to store AI embeddings alongside traditional business data. Understanding how to perform Data Manipulation Language (DML) and Data Definition Language (DDL) operations on vector columns is essential for building effective AI-powered applications. This guide covers everything you need to know about working with vector columns, including supported operations and important restrictions.

Understanding the VECTOR Data Type

The VECTOR data type can store vectors with:

Arbitrary number of dimensions: From 1 to 65,535 dimensions (65,528 for BINARY format)
Multiple formats: INT8, FLOAT32, FLOAT64, or BINARY
Flexible or fixed specifications: Can be declared with or without constraints

Declaration Examples:

-- Flexible: any dimensions and format
CREATE TABLE flexible_vectors (
    id NUMBER,
    embedding VECTOR
);

-- Fixed: specific dimensions and format
CREATE TABLE fixed_vectors (
    id NUMBER,
    embedding VECTOR(384, FLOAT32)
);

DML Operations on Vectors

1. INSERT Operations

You can directly insert vectors into tables using several methods:

Method A: Insert Using Vector Literals

-- Insert vector as array literal
INSERT INTO products (product_id, name, embedding)
VALUES (1, 'Laptop', '[0.1, 0.8, 0.5, 0.3]');

-- Insert with TO_VECTOR function
INSERT INTO products (product_id, name, embedding)
VALUES (2, 'Mouse', TO_VECTOR('[0.2, 0.7, 0.4, 0.6]'));

Method B: Insert with Embedding Generation

-- Generate embeddings during insert
INSERT INTO products (product_id, name, description, embedding)
VALUES (
    3,
    'Keyboard',
    'Mechanical gaming keyboard with RGB',
    VECTOR_EMBEDDING(doc_model USING 'Mechanical gaming keyboard with RGB' AS data)
);

Method C: Insert from Another Table

-- Copy vectors from another table
INSERT INTO products_archive (product_id, name, embedding)
SELECT product_id, name, embedding
FROM products
WHERE created_date < ADD_MONTHS(SYSDATE, -12);

2. UPDATE Operations

You can directly update vector columns:

-- Update with vector literal
UPDATE products
SET embedding = '[0.15, 0.85, 0.55, 0.35]'
WHERE product_id = 1;

-- Update using embedding generation
UPDATE products
SET embedding = VECTOR_EMBEDDING(doc_model USING description AS data)
WHERE embedding IS NULL;

-- Conditional update
UPDATE products
SET embedding = VECTOR_EMBEDDING(doc_model USING description AS data)
WHERE category = 'Electronics'
    AND embedding IS NULL;

3. DELETE Operations

Delete operations work normally with tables containing vector columns:

-- Delete specific rows
DELETE FROM products
WHERE product_id = 100;

-- Delete based on relational criteria
DELETE FROM products
WHERE created_date < ADD_MONTHS(SYSDATE, -24)
    AND status = 'ARCHIVED';

-- Delete all rows
DELETE FROM products;
-- or
TRUNCATE TABLE products;

4. Loading Data Using SQL*Loader

SQL*Loader can load vector data from external files:

Control File Example (products.ctl):

LOAD DATA
INFILE 'products.dat'
INTO TABLE products
FIELDS TERMINATED BY ','
(
    product_id,
    product_name,
    description,
    embedding CHAR(4000)
)

Data File Example (products.dat):

1,Laptop,High-performance laptop,"[0.1,0.8,0.5,0.3,0.6]"
2,Mouse,Wireless gaming mouse,"[0.2,0.7,0.4,0.6,0.5]"
3,Keyboard,Mechanical keyboard,"[0.15,0.85,0.55,0.35,0.65]"

Load Command:

sqlldr userid=username/password@db control=products.ctl log=products.log

5. DML on Tables with HNSW Indexes

Important Update (23ai Release 23.6+):

In Oracle Database 23ai releases 23.4 and 23.5, DML operations were not allowed on tables with HNSW indexes. Starting with Release 23.6, this restriction has been lifted:

Transactional consistency: DML modifications are now supported on tables with HNSW indexes
RAC support: Guarantees transactional consistency even in Oracle RAC environments
Consistent results: Vector search queries using HNSW indexes see transactionally consistent results based on their read snapshot

-- These operations now work with HNSW indexes (23.6+)
INSERT INTO products VALUES (101, 'New Product', '[0.1, 0.2, 0.3]');
UPDATE products SET embedding = '[0.2, 0.3, 0.4]' WHERE product_id = 101;
DELETE FROM products WHERE product_id = 101;

Vector DDL Operations

Tables with Multiple Vector Columns

Tables can have:

More than one column of VECTOR data type
Different formats and dimensions in different columns

CREATE TABLE multimedia_content (
    content_id NUMBER PRIMARY KEY,
    title VARCHAR2(500),
    description CLOB,
    -- Text embedding: 384 dimensions, FLOAT32
    text_embedding VECTOR(384, FLOAT32),
    -- Image embedding: 512 dimensions, FLOAT32
    image_embedding VECTOR(512, FLOAT32),
    -- Audio embedding: 256 dimensions, INT8
    audio_embedding VECTOR(256, INT8),
    -- Flexible dimension column
    metadata_embedding VECTOR
);

Adding Vector Columns

You can add vector columns to existing tables:

-- Add vector column with specific dimensions
ALTER TABLE products
ADD description_vector VECTOR(384, FLOAT32);

-- Add flexible vector column
ALTER TABLE customers
ADD preference_vector VECTOR;

-- Add with default NULL
ALTER TABLE documents
ADD content_vector VECTOR(768, FLOAT32) DEFAULT NULL;

After Adding, Populate the Column:

-- Generate embeddings for existing data
UPDATE products
SET description_vector = VECTOR_EMBEDDING(doc_model USING description AS data);

Dropping Vector Columns

-- Drop a specific vector column
ALTER TABLE products
DROP COLUMN description_vector;

-- Drop multiple columns
ALTER TABLE products
DROP (description_vector, image_vector);

Dropping Tables with Vector Columns

Tables containing vector columns can be dropped normally:

-- Drop table
DROP TABLE products;

-- Drop table with CASCADE CONSTRAINTS
DROP TABLE products CASCADE CONSTRAINTS;

-- Drop and purge from recycle bin
DROP TABLE products PURGE;

Prohibited Operations on Vector Columns

Oracle Database 23ai has specific restrictions on where and how vector columns can be used. Understanding these limitations is crucial for proper database design.

1. Table and Storage Restrictions

Cannot Define Vector Columns In:

External Tables:

-- This will fail
CREATE TABLE ext_vectors (
    id NUMBER,
    embedding VECTOR
)
ORGANIZATION EXTERNAL (...);

Note: As of Oracle Database 26ai, external tables CAN be created with VECTOR columns, allowing vector embeddings in text or binary format stored in external files to be rendered as the VECTOR data type. Check your database version for availability.

Index-Organized Tables (IOTs):

-- Cannot use as primary key
CREATE TABLE iot_vectors (
    embedding VECTOR PRIMARY KEY,  -- Not allowed
    data VARCHAR2(100)
)
ORGANIZATION INDEX;

-- Cannot use as non-key column either
CREATE TABLE iot_vectors (
    id NUMBER PRIMARY KEY,
    embedding VECTOR,  -- Not allowed
    data VARCHAR2(100)
)
ORGANIZATION INDEX;

Clusters and Cluster Tables:

-- Vectors cannot be part of clusters
CREATE CLUSTER vector_cluster (
    id NUMBER,
    embedding VECTOR  -- Not allowed
);

Global Temporary Tables:

-- This will fail
CREATE GLOBAL TEMPORARY TABLE temp_vectors (
    id NUMBER,
    embedding VECTOR  -- Not allowed
)
ON COMMIT DELETE ROWS;

Manual Segment Space Management (MSSM) Tablespaces:

Only the SYS user can create vectors as BasicFiles in MSSM tablespaces. Regular users should use Automatic Segment Space Management (ASSM) tablespaces:

-- Create ASSM tablespace for vectors
CREATE TABLESPACE vector_data
DATAFILE '/u01/app/oracle/oradata/vector_data01.dbf' SIZE 1G
SEGMENT SPACE MANAGEMENT AUTO;  -- Required for non-SYS users

2. Partitioning Restrictions

Sub-partitioning Keys:

-- Vector columns cannot be sub-partition keys
CREATE TABLE sales_data (
    sale_id NUMBER,
    sale_date DATE,
    embedding VECTOR(384, FLOAT32)
)
PARTITION BY RANGE (sale_date)
SUBPARTITION BY HASH (embedding)  -- Not allowed
(...);

Vectors in Partitioned Tables (Allowed):

-- Vectors CAN exist in partitioned tables
CREATE TABLE sales_data (
    sale_id NUMBER,
    sale_date DATE,
    product_name VARCHAR2(200),
    embedding VECTOR(384, FLOAT32)
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2023 VALUES LESS THAN (DATE '2024-01-01'),
    PARTITION p2024 VALUES LESS THAN (DATE '2025-01-01')
);

3. Constraint Restrictions

Primary Keys:

CREATE TABLE vectors (
    embedding VECTOR PRIMARY KEY  -- Not allowed
);

Foreign Keys:

CREATE TABLE vectors (
    id NUMBER PRIMARY KEY,
    embedding VECTOR
);

CREATE TABLE related (
    id NUMBER PRIMARY KEY,
    vector_ref VECTOR REFERENCES vectors(embedding)  -- Not allowed
);

Unique Constraints:

CREATE TABLE vectors (
    id NUMBER PRIMARY KEY,
    embedding VECTOR UNIQUE  -- Not allowed
);

Check Constraints:

CREATE TABLE vectors (
    id NUMBER PRIMARY KEY,
    embedding VECTOR CHECK (embedding IS NOT NULL)  -- Not allowed
);

Default Values:

CREATE TABLE vectors (
    id NUMBER PRIMARY KEY,
    embedding VECTOR DEFAULT '[0,0,0,0]'  -- Not allowed
);

4. Column Modification Restrictions

Cannot Modify Vector Column Definition:

-- Cannot change dimensions or format
ALTER TABLE products
MODIFY (embedding VECTOR(512, FLOAT64));  -- Not allowed

-- Cannot change to/from VECTOR type
ALTER TABLE products
MODIFY (embedding CLOB);  -- Not allowed

Workaround - Add New Column:

-- Instead, add a new column
ALTER TABLE products
ADD embedding_new VECTOR(512, FLOAT64);

-- Migrate data (with conversion if needed)
UPDATE products
SET embedding_new = VECTOR_EMBEDDING(model USING description AS data);

-- Drop old column
ALTER TABLE products DROP COLUMN embedding;

-- Rename new column
ALTER TABLE products RENAME COLUMN embedding_new TO embedding;

5. Index Restrictions

Non-Vector Indexes:

Vector columns cannot be part of traditional indexes:

-- B-tree index
CREATE INDEX idx_embedding ON products(embedding);  -- Not allowed

-- Bitmap index
CREATE BITMAP INDEX idx_embedding ON products(embedding);  -- Not allowed

-- Reverse key index
CREATE INDEX idx_embedding ON products(embedding) REVERSE;  -- Not allowed

-- Function-based index
CREATE INDEX idx_embedding ON products(UPPER(embedding));  -- Not allowed

Vector Indexes Only:

-- HNSW vector index
CREATE VECTOR INDEX idx_hnsw ON products(embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

-- IVF vector index
CREATE VECTOR INDEX idx_ivf ON products(embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 95;

6. Other Restrictions

Continuous Query Notification (CQN):

Vector columns are not supported in CQN queries.

Comparison Operators:

Standard comparison operators cannot be used:

-- These will fail
SELECT * FROM products WHERE embedding = '[0.1, 0.2]';
SELECT * FROM products WHERE embedding > '[0.1, 0.2]';
SELECT * FROM products WHERE embedding < '[0.1, 0.2]';

Use Vector Distance Functions Instead:

-- Correct approach
SELECT product_name
FROM products
ORDER BY VECTOR_DISTANCE(embedding, '[0.1, 0.2, 0.3]', COSINE)
FETCH FIRST 10 ROWS ONLY;

Best Practices for Vector DML and DDL

1. Design Considerations

-- Good: Specify dimensions and format upfront
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    name VARCHAR2(200),
    embedding VECTOR(384, FLOAT32)  -- Clear specification
);

-- Less optimal: Flexible vectors harder to optimize
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    name VARCHAR2(200),
    embedding VECTOR  -- May lead to performance issues
);

2. Batch Operations

-- Efficient: Batch update
UPDATE products
SET embedding = VECTOR_EMBEDDING(doc_model USING description AS data)
WHERE embedding IS NULL;

-- Less efficient: Row-by-row updates in loop

3. Transaction Management

-- Good practice: Commit after large operations
BEGIN
    INSERT INTO products_archive
    SELECT * FROM products WHERE created_date < DATE '2023-01-01';

    COMMIT;
END;
/

4. Use ASSM Tablespaces

-- Create tablespace with ASSM for vector tables
CREATE TABLESPACE vector_ts
DATAFILE '/u01/oradata/vector_ts01.dbf' SIZE 10G
AUTOEXTEND ON NEXT 1G MAXSIZE UNLIMITED
SEGMENT SPACE MANAGEMENT AUTO;

-- Create table in ASSM tablespace
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    embedding VECTOR(384, FLOAT32)
) TABLESPACE vector_ts;

5. Monitor Vector Column Usage

-- Check vector column statistics
SELECT 
    table_name,
    column_name,
    data_type,
    nullable
FROM user_tab_columns
WHERE data_type = 'VECTOR';

-- Check table size with vectors
SELECT 
    segment_name,
    segment_type,
    bytes/1024/1024 AS size_mb
FROM user_segments
WHERE segment_name = 'PRODUCTS';

Summary

DML Operations Supported:

INSERT vectors directly or with embedding generation
UPDATE vector columns
DELETE rows from tables with vectors
Load data using SQL*Loader
DML on tables with HNSW indexes (23.6+)

DDL Operations Supported:

Create tables with multiple vector columns
Different formats and dimensions per column
ADD vector columns to existing tables
DROP vector columns
DROP tables containing vectors

Key Restrictions:

External tables (except 26ai+)
Index-Organized Tables (IOTs)
Clusters and cluster tables
Global temporary tables
Sub-partitioning keys
Primary keys, foreign keys, unique constraints
Check constraints, default values
Column modification (dimensions/format)
MSSM tablespaces (non-SYS users)
Non-vector indexes (B-tree, bitmap, etc.)
Standard comparison operators (=, >, <)

Understanding these operations and restrictions ensures you can effectively design and manage vector-enabled applications in Oracle Database 23ai, combining the power of AI embeddings with traditional relational data operations.

DEV Community