Oracle Database 23ai: Indexing Improvements

Oracle Database 23ai is a major milestone in Oracle’s database evolution, bringing AI centric features, richer search capabilities, and smarter performance optimizations directly into the core engine. One of the main areas where 23ai has improved is indexing, enabling faster, more flexible, and AI aware data access for both traditional and modern workloads.

Key indexing improvements

Native AI Vector Indexing for Semantic Search

A major advancement in 23ai is the integration of AI vector search and specialized vector indexes directly within the database engine. Traditional B-tree indexes used for exact lookups, but they aren’t designed for similarity search, a backbone of many AI applications such as recommendation engines, semantic search, and retrieval-augmented generation (RAG).

Oracle offers:

• Hierarchical Navigable Small World (HNSW) indexes
  Efficient for approximate nearest neighbor search at scale.

• Inverted File Flat (IVF-Flat) indexes
  Optimized for large dimensional vector datasets.

• Hybrid Vector Indexes (HVI)
  Combine full-text search with semantic similarity search into a single index structure.
  This unified index simplifies indexing pipelines and enhances result relevance for mixed keyword + semantic queries.

Eg:

CREATE TABLE docs (

  doc_id     NUMBER PRIMARY KEY,

  content    VARCHAR2(4000),

  embedding  VECTOR(384)

);

CREATE VECTOR INDEX doc_vector_idx

ON documents (embedding)

ORGANIZATION HNSW;

Partition-Local Vector Indexes

Oracle 23ai introduces partition-local neighbor vector indexes, which build a dedicated vector index for each partition. 

• Queries with partition filters only scan the relevant partition index.

• Massive datasets with large number of vectors are now more manageable and perform better.

This is very important for analytics and AI workloads that leverage partitioning to balance performance and manageability.

Eg:

CREATE TABLE product_docs (

  doc_id     NUMBER,

  category   VARCHAR2(30),

  description VARCHAR2(4000),

  embedding  VECTOR(384)

)

PARTITION BY LIST (category) (

  PARTITION p_electronics VALUES ('ELECTRONICS'),

  PARTITION p_books       VALUES ('BOOKS')

);

CREATE VECTOR INDEX product_vec_idx

ON product_docs (embedding)

LOCAL

ORGANIZATION HNSW;

Index-Organized Tables (IOTs) with Smart Scan Performance

With 23ai on Exadata platforms, Index-Organized Tables (IOTs),  where the table data itself incorporated in a B-tree index, can now benefit from Smart Scan acceleration. This improves analytic query performance on IOTs with heavy data access patterns, even reaching high speedups for large scans.

For transactional systems that rely on fast primary key lookups with rich query patterns, this is a practical improvement with real impact.

Unified Search Index

New "create serach index" help developers define search indexes over text, XML, and JSON , so building full-text or semantic search indexes is easier. 

This enhances developer productivity and helps standardize indexing strategies across diverse data models.

CREATE TABLE art (

  art_id NUMBER PRIMARY KEY,

  title      VARCHAR2(200),

  body       CLOB,

  metadata   JSON

);

CREATE SEARCH INDEX art_search_idx

ON art (title, body, metadata);

Combined Search (Text + JSON)

SELECT art_id, title

FROM art

WHERE CONTAINS(body, 'Database') > 0

  AND metadata.tags[0] = 'database';

Hope above article will enhance your knowdege with Oracle 23ai Indexing!